[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

Combine 2 recordset into a multicolumn listbox

I am running two separate queries and obtain 2 recordsets.  One of the queries ia an standard query and the other is an aggregate query.  One will return a measurement for a person while the other will return average, min, max measurements for a collection of people.

I want to display the result in a single listbox.  So I will have columns in the listbox such as personID,personValue,GroupAvg,GroupMin,GroupMax

So first, how can I combine the recordsets?  If this is not possible, then how can I populate a multicolumn listbox with those columns when getting the results from 2 recordsets?  If necessary, the recordsets can be combined in a multidim array and then use that to populate the multicol listbox.
  • 6
  • 5
  • 3
1 Solution
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.
mock5cAuthor Commented:
I want to totals to appear in a row, for example:


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.


Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

<<<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?
mock5cAuthor Commented:
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.
mock5cAuthor Commented:
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?
You requested  info on multidimensional arrays in vba  You can find info in vba help under Dim and ReDim.  Basically, multidimensional arrays in access are like the 2 dimensional array show below.

Dim MyArray(0, 4)

MyArray(0,0) = [PersonID]
MyArray(0,1) = [PersonUnit]
MyArray(0,2) = [PersonMeas]
MyArray(0,3) = [GroupCount]
MyArray(0,4) = [GroupAvg]

in Access , one way you can extract the info into your listbox from the Array is to use the Split Function.
             Split(MyArray, ",")
I believe that the Split function is not available in VBA, so alternatively you woud have to use functions like Left and inStr to extract to the columns in your listbox,        

If you have any trouble declaring and dimensioning an array, the following vba code is for a function that will return an array of any size you want, compliments of this web site:

'Return an array from a function.

Sub TestArrFn()
Dim lSize as Long, l as Long

lSize = UBound(arrList)
MsgBox "The size of the returned array is " & str(lSize)

For l = 1 to lSize
debug.print arrList(l)
Next l

End Sub

Function arrList()
'This function returns an array of anything.
Dim lArrRows as Long, lRow as Long
Dim arrTmp() as Variant

lArrRows = 0
For lRow = 1 to 15
lArrRows = lArrRows + 1
ReDim Preserve arrTmp(1 To lArrRows)
arrTmp(lArrRows) = "MyArrayValue" & trim(str(lRow))
Next lRow

arrList = arrTmp
erase arrTmp
end function

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)
    If Len(strInput) > 0 Then
        iCount = iCount + 1
        ReDim Preserve tempArray(iCount)
        tempArray(iCount) = strInput
    End If
    Split = tempArray
End Function

Open in new window

<<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.
This link has an excellent example of populating a listbox from an array and contrasts it to populating a listbox using a union query.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now