Re: Hard to explain - multiple update fields query...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hopefully this will make things a bit easier. It basically writes the
repeating union queries for you...

Public Function GenerateUnion(ByVal intStart As Integer, ByVal intStop
As Integer)

'SAMPLE CALL: ?generateunion(1,3)

'sample result:
'SELECT ID, Award1 AS Award, Award1Date AS AwardDate, NumberAwarded1
AS AwardNum FROM YourTableName UNION ALL
'SELECT ID, Award2 AS Award, Award2Date AS AwardDate, NumberAwarded2
AS AwardNum FROM YourTableName UNION ALL
'SELECT ID, Award3 AS Award, Award3Date AS AwardDate, NumberAwarded3
AS AwardNum FROM YourTableName;


Const cSTRSQL = "SELECT ID, Award1 AS Award, Award1Date AS
AwardDate, NumberAwarded1 AS AwardNum FROM YourTableName UNION ALL "
Dim strsql As String

Dim intCounter As Integer

For intCounter = intStart To intStop
strsql = Replace(cSTRSQL, 1, intCounter)
GenerateUnion = GenerateUnion & vbCrLf & strsql
Next intCounter

GenerateUnion = Left$(GenerateUnion, Len(GenerateUnion) - Len("
UNION ALL ")) & ";"
End Function

I can't remember where the limit is on the length of a SQL statement
before Access barfs... so if you have to, you can create intermediate
unions. I did it for several months pretty much every day at my last
job, so been there, done that! This was one of the routines I used
all the time. A word of warning, though. Performance on union
queries is absolutely horrible, because you can't take advantage of
indexing. You could create a temporary table or a table in another
database and dump the result of the unions to that table and then
index that and summarize it and that would work...

Hope it helps a little.

Pieter
.