Here is the Function that generates the Union query. Put it in a Standard Module. Then you can run it by putting the cursor inside it and clicking the Run button in the VBE.
This will allow you to adjust which fields you want to get into the query. It will also allow you to regenerate the query if you change fieldnames etc. Just change the range of the n and m loops and the Select Case to suit you needs. You can also incorporate a similar Select Case in the n loop if you want more control over which fields are included.
The query doens't appear in the list until you change the display options of the Navigation Pane. I am sure there is a way to get it refreshed but I can't remember right now.
I am pretty sure you will need A2007 at least to handle the size the query. Also you may also have to write the union results to a temporary table beacuse Access might choke on handing both copies of the union at the same time.
Part of the query you would need to run on the union are not dissimilar to the other one I posted. Basically you need to self join the query on the key fields and ValueType.
You would get better performance if you added a number field to your table and use this as the key (along with ValueType) rather than an extensive multiple join. Of course you would need to adjust the selection criteria in the function to suit.
Remember the field count starts at zero and will follow the order in the table design view. (If you reorder the fields in datasheet view you might find it a bit confusing.)
Then select the records in the alias that are equal to or prior to the date of the record. Then get the Max date from these records.
Unfortunately a self join creates a lot of records in the intermediate step. If you can narrow the criteria to dates equal to or within a reduced range rather than every previous date then this would certainly help.
Be sure to index any fields you are searching or joining on.
I am thinking the recordset is probably a better solution but one never can be sure. Access is pretty good at optimising queries and they can be surprisingly fast. It is just the sheer size of the calculations it has to make on this one. Hope you have a lot of RAM.
Unfortunately I have what appears to be a mild influenza. I have been enduring it as a cold for the past few days but my brain is starting to abandon the best of its processing ability today. Not had enough sleep for several days.
This will allow you to adjust which fields you want to get into the query. It will also allow you to regenerate the query if you change fieldnames etc. Just change the range of the n and m loops and the Select Case to suit you needs. You can also incorporate a similar Select Case in the n loop if you want more control over which fields are included.
Code:
Private Sub MakeUnion()
Dim db As DAO.Database
Dim tbldefConsensus As DAO.TableDef
Dim qrydef As DAO.QueryDef
Dim IdxMax As Integer
Dim n As Integer
Dim m As Integer
Dim strSub As String
Dim strSQL As String
Set db = CurrentDb
Set tbldefConsensus = db.TableDefs("tblConsensus")
Set qrydef = New DAO.QueryDef
IdxMax = tbldefConsensus.Fields.Count - 1
For n = 13 To IdxMax
If Right(tbldefConsensus.Fields(n).Name, 3) <> "Opt" Then
strSub = "SELECT "
' Select fields included as keys
For m = 0 To 8
Select Case m
Case 1, 2, 5, 6, 7, 8
strSub = strSub & tbldefConsensus.Fields(m).Name & ", "
End Select
Next
strSub = strSub & tbldefConsensus.Fields(n).Name & " AS Measurement, " _
& n & " As ValueType FROM tblConsensus" & vbCrLf & "UNION ALL"
strSQL = strSQL & vbCrLf & strSub
End If
Next
strSQL = Left(strSQL, Len(strSQL) - 9)
With qrydef
.Name = "UnionQuery"
.SQL = strSQL
End With
With db.QueryDefs
.Append qrydef
.Refresh
End With
Set tbldefConsensus = Nothing
Set qrydef = Nothing
Set db = Nothing
End SubThe query doens't appear in the list until you change the display options of the Navigation Pane. I am sure there is a way to get it refreshed but I can't remember right now.
I am pretty sure you will need A2007 at least to handle the size the query. Also you may also have to write the union results to a temporary table beacuse Access might choke on handing both copies of the union at the same time.
Part of the query you would need to run on the union are not dissimilar to the other one I posted. Basically you need to self join the query on the key fields and ValueType.
You would get better performance if you added a number field to your table and use this as the key (along with ValueType) rather than an extensive multiple join. Of course you would need to adjust the selection criteria in the function to suit.
Remember the field count starts at zero and will follow the order in the table design view. (If you reorder the fields in datasheet view you might find it a bit confusing.)
Then select the records in the alias that are equal to or prior to the date of the record. Then get the Max date from these records.
Unfortunately a self join creates a lot of records in the intermediate step. If you can narrow the criteria to dates equal to or within a reduced range rather than every previous date then this would certainly help.
Be sure to index any fields you are searching or joining on.
I am thinking the recordset is probably a better solution but one never can be sure. Access is pretty good at optimising queries and they can be surprisingly fast. It is just the sheer size of the calculations it has to make on this one. Hope you have a lot of RAM.
Unfortunately I have what appears to be a mild influenza. I have been enduring it as a cold for the past few days but my brain is starting to abandon the best of its processing ability today. Not had enough sleep for several days.
Last edited:
I guess I'm still sleeping.