Ab-normalization at Experts Exchange - part 21

Question today: sql crosstab with auto-generated column headers?

How to transform:







Into:

Company	A - Firstname	A - SurName	B - Firstname	B - SurName	C - Firstname	C - SurName	D - Firstname	D - SurName
comp1	Harry	Smith	Ellen	Black	Jack	Johnson
comp2	Norman	White					Ann	Wright

Well I must admit that the question was located in the "coding" section, nevertheless capricorn1 once again proved to be a “coding happy” amateur instead of a professional.

His solution was this VBA code creating an additional table with the result:

Sub CreateTable()
Dim rs As DAO.Recordset, sFld As String, rs1 As DAO.Recordset, rsNew As DAO.Recordset
Dim j As Integer, fldArr()
Set rs = CurrentDb.OpenRecordset("select distinct contacttype from table1 order by contacttype")
rs.MoveFirst
Do Until rs.EOF
sFld = sFld & "," & rs(0) & "_FirstName Text(25)" & "," & rs(0) & "_SurName Text(25)"
rs.MoveNext
Loop
rs.Close
'see if the table already exists, if so, delete the table
If DCount("*", "msysobjects", "[name]='newTable'") > 0 Then
CurrentDb.Execute "drop table newTable"
End If
'create the table
CurrentDb.Execute "Create Table newTable(Company Text(25) " & sFld & ")"

Set rsNew = CurrentDb.OpenRecordset("newTable")
For j = 0 To rsNew.Fields.Count - 1
ReDim Preserve fldArr(j)
fldArr(j) = rsNew(j).Name
Next

Set rs = CurrentDb.OpenRecordset("select distinct company from table1 order by company")
rs.MoveFirst
Do Until rs.EOF
Set rs1 = CurrentDb.OpenRecordset("select * from table1 where company='" & rs(0) & "' order by ContactType")
rs1.MoveFirst
rsNew.AddNew
rsNew("Company") = rs1("Company")
Do Until rs1.EOF
For j = 1 To UBound(fldArr)
If Left(fldArr(j), 1) = rs1("ContactType") And Mid(fldArr(j), InStr(fldArr(j), "_") + 1) = "FirstName" Then
rsNew(fldArr(j)) = rs1("FirstName")
End If
If Left(fldArr(j), 1) = rs1("ContactType") And Mid(fldArr(j), InStr(fldArr(j), "_") + 1) = "SurName" Then
rsNew(fldArr(j)) = rs1("SurName")
End If
Next
rs1.MoveNext
Loop
rsNew.Update
rs.MoveNext
Loop
rs.Close
rs1.Close
rsNew.Close
End Sub


While all needed using a query would have been:

TRANSFORM Max(qryBasicData.FirstName) AS MaxOfFirstName
SELECT qryBasicData.Company
FROM (SELECT Table1.Company, [ContactType] & " - Firstname" AS Type, Table1.FirstName
FROM Table1
UNION
SELECT Table1.Company, [ContactType] & " - SurName" AS Type, Table1.SurName
FROM Table1) qryBasicData
GROUP BY qryBasicData.Company
PIVOT qryBasicData.Type;

Needless to say that a query is at least 10 times faster and won’t let the database grow with adding (and deleting) a temporary table.


Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_26108122.html)

Ab-normalization at Experts Exchange - part 20

Question today: Export 4 Queries to the same Worksheet

The problem:
I have code to export 4 queries to the same workbook in 4 different worksheets. I'm using the TransferSpreadsheet method. I would like to change the code to put the results of the query in the same worksheet but I don't know how. I don't know if it's possible but I also would like to put 2 blank lines between each query result. The queries are:
1. qry_RN
2. qry_RN_Casual
3. qry_PT
4. qry_PT_Casual.

The "answer" by capricorn1:
Use my 45 lines of VBA code. Very slow as it's done by recordset processing and I hope the tables aren't too big....
Again proof that some experts are as obsessed by "code solutions" as many amateurs.



A simple and fast solution would have been to create a UNION query and even two blank lines can be added with litle effort like:
select 1, * from qry_RN
UNION
select 2, * from tblTwoDummyLines
UNION
select 3, * from qry_RN_Casual
UNION
select 4, * from tblTwoDummyLines
UNION
select 5, * from qry_PT
UNION
select 6, * from tblTwoDummyLines
UNION
select 7, * from qry_PT_Casual
ORDER BY 1;

This query could be used for the DoCmd.TransferSpreadsheet and get the result at least 6 times faster.

Nic;o)

Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_25079300.html)

Ab-normalization at Experts Exchange - part 19

Question today: How do I create a reference to a control in code

The problem:
I have 28 fields on my form named Date1 thru Date28.
I would like to iterate through these controls something like this"

dim n as integer. MyDate as date
n=1
MyDate-Date()
do while n >29
me!Date(n) = MyDate
n=n+1
(increment Mydate by n)
loop

I know it can be done. Just forgotten the syntax. Thanks in advance.

The "answer" by capricorn1:

dim n as integer, myDate as date
myDate=date()
for n=1 to 28
me("date" & n)=mydate
mydate=mydate + 1
next

The response by the questioner CRB1609:
Thanks. You are a star of galactic proportions.

CRB

The real solution would be to ask the questioner why he's using a not normalized date field, but that's a service beyond the "thinking frame" of our EE MS Access expert.
Just keep the questioners unaware of normalization, thus they'll keep coming back.....

Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24822375.html )

Ab-normalization at Experts Exchange - part 18

Question today: How to flag "records" filtered in a Access form?

The problem:
Base on what the user had filtered, I would like to update "updateStatus" field in the tbl_TrainingSched.

Because "employeGroup" field is not a field from tbl_TrainingSched, the update can't be done.
That can work, if I use "employeId", but I have more than one employeId filtered in the form.


The crappy "solution":

Me.Filter = strFilter
Me.FilterOn = True

'Build the employeeID list
Dim strID
With Me.RecordsetClone
Do Until .EOF
If InStr(strID, !employeId) = 0 Then
strID = strID & "," & !employeId
End If
.MoveNext
Loop
strID = Mid(strID, 2)
End With
'identification of the records selected by the user
strUpdate = "update tbl_TrainingSched set [updateStatus]= True where employeId In (" & strID & ")"
CurrentDb.Execute strUpdate

The flaw is the limited length of the query string. When too long the query will be truncated and the query will fail.

The real solution would be to use the query the form is based on (stripped to return just the employeId) and add the Me.Filter as the WHERE clause. Next use an update query like:
update tbl_TrainingSched set [updateStatus]= True where employeId In (SELECT employeId from qryForm WHERE " & Me.Filter & ")"

This "expert" shows off with 3 Microsoft MVP awards, doesn't Microsoft check for the "quality"?
Guess you know the answer

A fail safe solution and faster, as the crappy VBA loop to build a string will take some time....


Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24762354.html)

Ab-normalization at Experts Exchange - part 17

Question today: If table exist

The problem:
The questioner wants to add new tables, but runs into trouble when a table does exist...


The crappy solution:

if dcount("[name]","msysobjects","[name]='Table1'")>0 then
DoCmd.DeleteObject acTable, "Table1"
end if

Obviously capricorn1 doesn't know that the normally hidden "msys" tables are owned by Microsoft and that they can change the names and/or structure as they please.
So it's bad coding to use these tables and there's no need to use them, as the Access objects will enable you to check for the existance of a table in the tables collection.

Three "proper" samples can be found at:
http://forums.devarticles.com/microsoft-access-development-49/access-2000-delete-a-table-if-it-exists-using-code-3821.html


Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24726902.html)

Ab-normalization at Experts Exchange - part 16

Question today: Change text box name with a variable

The problem:
I am coding a form in Access 2003 using VBA and SQL.
The form has 16 text boxes sequentially named txtBSCPIN1 to txtBSCPIN16.
When updating the table I need to see if there are values in each box like isnull(txtBSCPIN1) = True.
I don't want to have to write 16 ElseIf statements if I don't have to.

Is there a way to use a variable to change the number at the end of txtBSCPIN so I could then use a do loop to check all of the boxes.
Or is there a better way to do this.

The crappy solution:
Copy/paste code how to use a loop to dynamically check the text box value.

Obviously capricorn1 doesn't know that field names with a trailing number point to a not normalized approach.
Obviously capricorn1 doesn't read the Q as it states "Or is there a better way to do this."
Obviously capricorn1 doesn't know how to translate a Not-Normalized approach into a normalized way, that's using little or no VBA code.

The "normalized way" only requires:
1) A (temporary) table with 16 rows, one for each txtBSCPIN field.
2) An UPDATE query to set the BSCPIN fields to Null when opening the form and
3) An APPEND query selecting the "True" rows to be added to the target table.

As expected, this is again a "bridge too far" for this "would be" expert.
Still wonder why Experts-Exchange is so proud on their (self proposed) MVP experts and why Microsoft doesn't track their MVP's better for real quality solutions.
Hmm, not really surprised, after all both EE and MS are only interested in $$'s and not in their customers in the true American spirit.


Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24618166.html)

Ab-normalization at Experts Exchange - part 15

Question today: formula in access to replace values in a column

The problem:
Each column corresponds to a question number and for each question number one of the values (1,2,3,4,5) represents the correct

answer for the whole column.

For example in the column 'Q1', the value '4' is the correct answer for all the records in the column.

For column 'Q1' I would like all the '4' values to be replaced by the value '1' and the rest of the values (1,2,3,5) to be

replaced by the value '0'


The crappy solution:
update Table1 set Q1=iif([Q1]="4","1","0")

Obviously capricorn1 doesn't know how a SELECT query could do this without "deleting" the original value.
The general approach to this is to leave the original table "as is" and to define a query for repacing the value.

As expected, this is a "bridge too far" for this "would be" expert.
He even has to apologize for not reading the question correctly as he overlooked Q2, Q3, etc.

I could also tell the questioner that his table isn't normalized and that using a normalized "question" table and "answer" table

would make a really flexible way to construct a questionair.
But as capricorn1 is only interested in points, this won't be noticed by him....

Nic;o)
Still proud to be suspended from this "quality site" where wannabee experts rule and "quality" is just another word from the

dictionary.
(The question can be found at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-

Macros/Q_24533410.html)