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)

Capricorn1's hunt for the points

When a questioner finds a solution by him self a request for closure can be made and an expert can complain.
That's exactly what capricorn1 did in this case:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24511901.html

The questioner did post his solution and questioners final comment was:
"My appologies. It is that your suggestion is exactly how my code already reads and has nothing to do with the solution."

But capricorn1 did get the "Answer" assigned, although it was solved by the questioner...

Another sample of the "quality answers" you need to pay for to get.

Ab-normalization at Experts Exchange - part 14

Question today: How would you run an update query in Access

A questioner want to update in a field the value -1 to 215 and 0 to Null

The proposed queries by a real expert:
UPDATE tblethnicitycopy SETintFatherNEuropean = Null WHERE intFatherNEuropean= 0
UPDATE tblethnicitycopy SETintFatherNEuropean = 250 WHERE intFatherNEuropean= -1

doesn't work as there's no space after the SET command :-(

But crappy has a working copy/paste solution:
UPDATE tblethnicitycopy
SET intFatherNEuropean = iif([intFatherNEuropean]=-1,250, Null)

He just forgot that all other values (not being -1) will turn into Null too .
That's what I call a beginners mistake, but did I even thought he was an expert?

The questioner added a question:
Now I see why you have > 1,000,000 points. Another great solution to a difficult issue.
Could you tell me why the standard approach I tried ~ to the shambalad solution does not work and
your solution does work?

Like my previous blog article did point out, there's no additional comment from crappy, as he's got his points

Thanks to crappycorn1 I had my daily laugh, so please keep on commenting crappy

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_24413706.html)

Ab-normalization at Experts Exchange - part 13

Question today: Is it possible to implement a triple click in MS Access 2007 Form

The correct answer is "No" and that's what crappycorn does know and it does get him another 2,000 expert points.
He even took the effort to ask "can you explain what you want to do?"

The questioner described the goal:
"We are building a touch screen application using MS Access 2007. For text fields on the form, our users
want to have a "Simple" keyboard(A pop up form).
For example, a button with Caption ABC - Single click is A, double click is B, triple click is C."
and accepted the "No" comment as the solution.....

There's an "easy" solution to code a button with an "rotating" caption.

But there's no more commenting from crappycorn, now the question remains, is this because of the "I have my expert points" attitude from crappy or isn't he able to think of such a solution....

It's my guess that option 2 is the correct answer to this, but feel free to think that it's his attitude...

Thanks to crappycorn1 I had my daily laugh, so please keep on commenting crappy

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_24322006.html)

Ab-normalization at Experts Exchange - part 12

Question today: Simple Query Criteria pulling data from a form.

Obviously "Simple" was the tempting word here for the EE "expert", so the proposed solution was "Simple" too, a "RTFM" link pointing to "How to use the Query by Form (QBF) technique in Access 2000"

The fact that the questioner posted a query using formfields, does make this solution look a bit odd. The "general" solution is to use "LIKE" or to code some VBA code to construct the WHERE clause.

Remains the question why this "solution" was accepted by the questioner....

So I had my laugh today again, or is it something to cry....

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_Reports/Q_24347199.html)

Ab-normalization at Experts Exchange - Part 1

I'm always disappointed when beginners are pointed the wrong way.

The first glance at a table design like:

Table A - ID, LAstname, First Name, Year
Table B - ID, LAst Name, First Name, Year, accepted status

Triggers for me the question "Why two tables with almost identical fields?"

But as the beginner asks for how to delete rows in both tables when the [accepted status] holds a certain value, he's helped by an "expert" without even asking for the reason of this "design". Even the possibility of a Cascade Delete isn't mentioned...

It's obvious that this beginner should start with redesigning his database structure and should be pointed to an article about database normalization. Having this "process driven table design" will cause a lot of additional questions and coding, thus generating more "expert points" for this "would be expert". So it's obvious that it's not in the interest of this "expert" to point this beginner in the right direction and this (paying) experts exchange member is fooled by this "expert" as he thinks his problem is solved, while it just has started.

This bad service to a paying experts exchange "premium" member is even more embarrassing when you know that this "would be expert" even proudly states in his profile that he's an MVP....

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/Q_23762363.html and for those without member access a copy can be found at: http://www.altict.nl/Q_23762363.html )

Ab-normalization at Experts Exchange - Part 2

When I see a question with a statement like:
"if someone chooses Mike Smith from the drop-down field, then Mike Smith will also automatically be entered in the other field?"

Then my first question would be why a firstname and lastname are stored in one field.
The next question would be where this drop-down field is getting it's information from.
And also a question why having this mechanism in place, while the proper sequence would be to have the user first determine or a person is already registered, before starting to enter a new one...

None of these questions are stated in the "answer comment", the questioner gets a simple answer with a Move statement, even without referring to a test and/or warning when there's already data in the target field that could be overwritten.

Another fine sample of the way you can be "helped" when paying for an answer.

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/Q_23885899.html or at http://www.altict.nl/Q_23885899.html when you have no free access)

Ab-normalization at Experts Exchange - part 3

The question today:
How to trap error when form closes with a null value in an index or primary key field?

Obviously this member tries to close a partially filled for, or a so-called "dirty" form.
The "normalized" way to handle this is to issue a confirmation message that the user wants to abandon the record and when confirmed, issue a "Me.Undo" to restore the record contents and to close the form normally.

The Experts-Exchange #1 Access expert however proposes to code an error handling exit when dataerr=3058 occurs in the form_error event.
This will however work sometimes, but as the error code can differ, depending on the Access and Windows version, it's no 100% solution.
Moreover in general coding rules it's considered bad practice to code error handling afterwards when you can intercept / prevent such an error on forehand.

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/Q_23924143.html)

Ab-normalization at Experts Exchange - part 4

The question today:
How to choose multiple records when using a drop-down-list on an Access form?

It's impossible to use a combo box for a multiple selection. So the proposed solution is to use a list box with the Multi Select property set to Simple or Extended.
To create the necessary filtering with the selected items the #1 Access "expert" proposes to use some VBA code to loop through the Items Selected collection.
Impressive, but unnecessary.

There's a code less solution by using a sub form with a Yes/No field for the selection and now you can JOIN the reporting query with the sub form table and filter for the Yes/No field to be True.

It's an amateurs approach to use code to solve problems, but did I expect otherwise from the Experts Exchange #1 expert ?

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/Q_23822041.html)

Ab-normalization at Experts Exchange - part 5

The question today:
Access 2007 form validate field based on result from query

Reading the question you find a questioner wanting to check or an entered field equals the result from a query +1
In case there's a difference the user should be warned and, when possible, given the value to be entered.

This is a real "classic" example of redundant data. Everything that can be extracted from the database with query shouldn't be stored, so you would expect that an "expert" would advise not to store this and use the result of the query to show the actual value of the field as information.

Well, you're having a bad day when you're advised by the #1 MS Access expert at Experts-Exchange. This "expert" will give you as solution the code with a working IF statement and won't mention the data redundancy...

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/Q_23961889.html)

Ab-normalization at Experts Exchange - part 6

The question today:
Filter with Multi Field Combo Box

It looks like a complex combo problem, but all needed is a concatenation of two fields in the combo's query and the positioning of the form on the selected row from the combo box.

The #1 Access "expert" creates an attachment from a part of the NorthWind sample database containing a form with a working combo box with this elaborate explanation:

test this

So no credits to Microsoft for the database and no syllable in the combo's code about the how and why...
All Microsoft code behind the form holds one or more lines of explanation, the #1 Access "expert" explains nothing...

Even the simple solution to place a combo box using the Access wizard and choosing the option "Find a record on my form ..." isn't mentioned.
Again a sample how an "expert" keeps a questioner hooked up to Experts Exchange as he's too lazy to teach the questioner how to cope with this problem. Or is this "expert" incapable of explaining how to use the Access wizards...


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_Forms/Q_23977702.html)

Ab-normalization at Experts Exchange - part 7

The question today:
Access copy from subform to Main form

Another "classic" example of redundant data. Why have the same field on a main and subform?
The first question I would ask before advising such a questioner is why storing the same value twice.

But, you're having a bad day when you're advised by the #1 MS Access expert at Experts-Exchange. This "expert" will give you as solution the code to move the field and won't mention the data redundancy and the trouble this can cause...

Is the questioner helped by this ? Yes, in the short term, but it's sure he'll be back and that makes EE happy as they will continue to collect their $$'s.
For smarter people there are sites like bytes.com where the membership is for free and the experts are cooperating to provide the best answers.

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_Forms/Q_23993476.html)

Ab-normalization at Experts Exchange - part 8

The question today:
Trying to use CASE statement in ADO execute command

It almost gets boring as this is another "classic" example of redundant data trouble.
Why have a table field filled with the same value as another field when empty?

The first question I would ask before advising such a questioner is why a field is occurring twice in the table. The tell tale field name "Salesperson2" indicates normalization hasn't been applied.

But, you're having a bad day when you're advised by the #1 MS Access expert at Experts-Exchange. This "expert" will give you as solution the code to update the field in the table and won't mention the data redundancy and the trouble this can cause. Obviously he's never worked with databases in the real world as when there's a need for a "Salesperson2", it's almost sure that in the near future a "Salesperson3" will be requested by the users and all code can be changed, etc., etc.

Is the questioner helped by this ? Yes, in the short term, but it's sure he'll be back and that makes EE happy as they will continue to collect their $$'s.
For smarter people there are sites like bytes.com where the membership is for free and the experts are cooperating to provide the best answers.

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_Forms/Q_23996147.html)

Ab-normalization at Experts Exchange - part 9

The question today:
Access: Can't Add Records to Query Based on Multiple Tables

No solution is offered by capricorn1 the #1 MS Access expert, but only a Google link to Microsoft showing when it's possible and when not.

For this question a simple explanation of the Main form with linked sub form construction would have solved the problem, but that's not mentioned. Makes me wonder or this "expert" even realizes the basics of the Access architecture.

For smarter people there are sites like bytes.com where the membership is for free and the experts are cooperating to provide the best answers.

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_23735833.html)
PS The staff of Experts Exchange still prove to be incapable of changing my email address in my profile, will keep you posted)