Problem with multple outputs

[Krystoff]

Hello all,

I have a query that looks up Applications by people. For every person I find, I also need to find the number of applications booked. I can do this fine and it comes up similar to this:

Name PersonID Appp Bkd
John Doe 12345 100 200
John Doe 12345 100 201
John Doe 12345 100 203
John Doe 12345 101 200
John Doe 12345 101 201
John Doe 12345 101 203


What I want, is this output:
Name PersonID Appp Bkd
John Doe 12345 100 200, 201, 203
John Doe 12345 101 200, 201, 203


BKD, APP, and Person info is coming from three seperate tables all related on the PersonID.

Any ideas how I can get the output I want?

Chris

 

[PaulBricker]

First you are going to need a query that brings it all together like in your first example. I'll call it Query1. Then you will need a Totals query that has the Name PersonID and Appp fields like your second example. I'll call that Query2.
Then you need to put this function in a module
Function getBkd(myID as String, myAppp as String) as String
Dim rst as DAO.Recordset
Dim strSQL as String, strHolder as String
strSQL = "Select * From Query1 Where Query1.PersonID = '" & myID & "' And Query1.Appp = '" & myAppp & "'"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOPenDynaset)
rst.MoveFirst
Do Until rst.EOF
strHolder = strHolder & rst!Bkd & ", "
rst.MoveNext
Loop
getBkd = Mid(strHolder,1,Len(strHolder)-2)
End Function

Then in Query2 you put this expression
myBkd:getBkd([PersonID], [Appp])

You will have to adjust field/table/query names to suit your situation.

Paul

 

[scriverb]

To create display the booked information in a linear fashion seperated by commas will require the the use of VBA code where you read the records from your initial query and build a new record for each Name,PersonID,and App and append the booked information to a variable until you get to the next person. At that point you save the new record and start building the next record.

If you want to provide the SQL and query name that you use for the example that you posted I can give you the VBA code to build your final records. Bob Scriver