Combining multiple records into one record

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.

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 Sub

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.
 
Last edited:
The ValueType being in a numberic form is probably not what you want at the end, I was going to have script generate the Lookup table at the same time but never got to it.

Let's first see if the query is fast enough to be practical then worry about that later.
 
Yeaaaaah! It works, and very fast as well.
Like you've already mentioned, it gave me "the query is too complex", but after I removed "_opt" fields it worked nicely.
Now I will take some time to fully understand how it works and how to implement it the best way possible.
 
Have you done the self join part too? The union is only the first step to get something more suited to the ultimate query.
 
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.

Application.RefreshDatabaseWindow

JR
 
No, I haven't done it yet. That's what I'm trying to understand now.

But my brain processing ability is...aaaahhhh...and I don't have influenza at all. Hope you are feeling better though. :) I guess I'm still sleeping.
 
Application.RefreshDatabaseWindow

JR

Thanks

I was thinking it was something like

Code:
With flu
    Application.Impossible
End With - out a solution
 
When I run the union query alone it has about 90k records, however when I self join it, it has 4.5M records. It repeats the each record 41 times?
Also, how do I go from here, how do I take the latest available value?
 
The self join is to connect the primary result to all all other results with matching ValueType and the the other key fields. Don't panic about the number of records. This will fall dramatically in the next step.

After the join, the next step is to return the Max (R_Date) Having the R_Date before or equal to the main result from the alias for each main Group. Here we also add the condition that we are only interested in those results that are Not Null.

Turn on Totals (right click) in the query designer grid to achieve this.

We just keep working through it peice by peice. Query upon query. It is actually still efficient because Access look at the big picture before it optimises rather than calclating each result we see by running once piece.

As I said earlier the recordset might ultimately work better but every experienced developer knows the query is always worth a try because Access is remarkable good at reductionism when formulating a question to the data.

One foundation that must never be forgotten for queries. Index any field joined or searched and ask question that can refer directly to the index.
 
Actually, I've made a mistake, there was 4.5M records because I forgot to match the ValueType, now there is 115k records (25k more than in original union query).

So, for the next step, do I make another query or?
I've put it within the same self join query, I've added Max(ReportDate) from the UnionQuery and for the criteria I've put <=[UnionQuery_1]![ReportDate]. Additionally I've put the criteria to the [Measurement] "Is Not Null".
When I try to run it I get this:
You tried to executa a query that does not include the specified expression 'Max(UnionQuery.ReportDate)<=[UnionQuery_1]![ReportDate] And Not UnionQuery.Measurement is Null' as part of an aggregate function.

ReportDate is instead of R_Date.
I know that I'm doing it probably fundamentally wrong, but I really can't think clearly at all these days.

One more thing to consider - it's possible that there will be couple of records all having the null. So in this case, Access should go through all of them, figure out that they are all null and do nothing.
 
Regard each of the joined queries as aliases of the same data all connected to the matching records in the other by the join.

In the query designer, include only the key fields from one alias but not the measurement. These will be the master records and Access keeps them defined by the Groups on each field. Really just the representation of your original data at this point.

Now we want to get a Measurement in every group even if the current one is not availalable. So we now need to know the valid Measurement taken on a date that is less than or equal to the matching group master record. So we have the Max date on the second alias with a condition >= the date on the master record and a condition of Is Not Null on the Measueament.

At this point we don't ask for the Measurement because Access would demand the Group again and give use the Max date for each different value. So we don't tick that Show box for Measurement even though it has a condition.

Now we have the the keys as a group plus the date of the latest valid Measruement.

Join this result back yet again to the union query on the keys plus the date field. Return the keys and the measurement from that result and you have your answers.
 
Thanks! This works fine now.
However, now I have the problem that it completely ignored the "nulls".
When none of the records had the value, it just didn't show them.
That's why I have now about 20k records instead of 90k.
Do you have an idea how could I add those records?
 
Do an OUTER JOIN between the original table and the query results you have so far.
Set it to "Show all records from the table plus matching records from the query".

Have you got the results into the format you want?

I am also curious how long the query took to run.
 
Do an OUTER JOIN between the original table and the query results you have so far.
Set it to "Show all records from the table plus matching records from the query".

Have you got the results into the format you want?

I am also curious how long the query took to run.

Great! That should work.
Yes, I got the results in the format I wanted. Thank you.

It takes about 25 seconds to open the query, than it takes about 35 seconds to show all records (I mean - to show the exact number of records in a query - it's about 20k).

However, this was tested locally, I assume that the performance will go down drastically when I run it over the network. But this task won't be performed that often, so even if it's 3-4 minutes over the network it should still be fine I guess.

Cheers!
 

Users who are viewing this thread

Back
Top Bottom