Combine 2 recordset into a multicolumn listbox

First, you need to have an item in each of the recordsets that links the two.  THEN you want to create a third query to combine the two queries you've created and use that for the recordset of your listbox.

If the relating field/key in each query is CustomerID, your third query would look something like this:

SELECT Query1.*, Query2.* FROM Query1 INNER JOIN Query2 ON Query1.CustomerID = Query2.CustomerID

If you want the totals to appear as a row total in your listbox, use a union query to combine the two queries into one.

If you want the totals to appear as s column total in your listbox, construct a crosstab query using the union query as the data source for the crosstab. The crosstab query wizard will help with about 90% of the construction of the crosstab.

I want to totals to appear in a row, for example:

PERSONID,PERSONUNIT,PERSONMEAS,GROUPCOUNT,GROUPAVG,GROUPMIN, GROUPMAX
101,weight,165.7,55,149.2,122.5,205.7
102,height,1.9,55,1.7,1.2,2.1

The data for the columns starting with "PERSON" come from one query and the data for the columns beginning with GROUP come from a group by query.

I'm not not sure how I can accomplish that with a UNION query because doesn't a union query append rows, not columns?

There is no ID that can tie the two queries together because one is just a standard query and the other is a group by query.

I thought that one approach might be to stuff the values into an multidimensional array and then take the values from that array and assign them to a multicolumn list box.  Then again, that might not be the best approach so please advise on a better method or how to create than mulit-dim array and how to get the values from that into a mulitcolumn listbox.

Thanks.

<<<I'm not not sure how I can accomplish that with a UNION query because doesn't a union query append rows, not columns?>>>><<<<I want to totals to appear in a row, for example:>>>>

Union query appends rows, but you can't use here because the indvdual queries have different data types.

Let me think about this and I will get back to you.

How do you know which groupcount goes to each personid?

I was wrong, there are several columns I can join by so that would make a 3rd query possible.

So far, I've cheated.  I've created 2 list boxes and put them together on the form so they appear to be a single list box with continuous rows.  I'm responsible for making sure the rows can join correctly so the data is not mismatched.

I'm still interested in how to create multidim array and transfer that data to a multicol listbox.

It would be very, very simple to create a third query that joins the two queries (inner, left, or right -- your choice based on what you want to see) and set the recordsource of that listbox to the third query to display everything you want in one listbox.

in my example, I can join on the unit such as weight.  Now that I think of it, one query needs specific criteria for a Person ID, while the other does not have that criteria.  So to join those two queries, are you suggesting that I have 2 select clauses and then join them?  Or have one select clause with the criteria?

I'm suggesting that you have one SELECT clause and join the data on related fields.  You will have to have some sort of related field in order to combine the data, I would assume.  Could you paste your two current queries here?

It's not a very practical solution for this question, though.  That almost serves as another question entirely.

I will add that the split function is invaluable when using arrays, however.

Public Function Split(source As String, Splitter As String) As Variant
    Dim strInput As String
    Dim iloc As Integer
    Dim iCount As Integer
    Dim tempArray() As String
    strInput = source
    iloc = InStr(strInput, Splitter)
    Do While iloc > 0
        iCount = iCount + 1
        ReDim Preserve tempArray(iCount)
        tempArray(iCount) = Left$(strInput, iloc - 1)
        strInput = Mid$(strInput, iloc + Len(Splitter))
        iloc = InStr(strInput, Splitter)
    Loop
    If Len(strInput) > 0 Then
        iCount = iCount + 1
        ReDim Preserve tempArray(iCount)
        tempArray(iCount) = strInput
    End If
    Split = tempArray
End Function

Select allOpen in new window

Sean,
<<It's not a very practical solution for this question, though.  That almost serves as another question entirely.>>>

mock5 may be breaking new ground for listboxes.....

listbox1.RowSource = Split(MyArray, ",")

see Doug Steele's comments on populating a listbox from an array.
                http://www.tech-archive.net/Archive/Access/microsoft.public.access.modulesdaovba/2005-04/msg00618.html

This link has an excellent example of populating a listbox from an array and contrasts it to populating a listbox using a union query.
http://bytes.com/groups/ms-access/202828-a2k-populating-listbox-using-array-there-better-way