Creating Horizontal Output

[xweyer]

I am working on creating a schedule in Access (2000). I have a query “Sessions” which produces this output.

Begin End Activity Name
9:00 10:00 Swim Jones
9:00 10:00 Swim Smith
9:00 10:00 Swim Baker

I had a report based on this query that produced this output.

Begin End Activity Name
9:00 10:00 Swim Jones
Smith
Baker

My ultimate goal is a report that produces this output.

Begin End Activity Name
9:00 10:00 Swim Jones, Smith, Baker

I was previously referred to thread(703,495410). I edited the script found there to reflect my database as below:

Public Function Quest_String()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vQString As String
Set db = CurrentDb
Set rs = db.openrecordset("Sessions", dbOpenDynaset)
rs.MoveFirst
Do
vQString = vQString & rs("NAME&quot & ", "
rs.MoveNext
Loop Until rs.EOF
Quest_String = Mid$(vQString, 1, Len(vQString) - 2)
rs.Close
db.Close
End Function

When I used this in the report the output came out something like this:

9:00 10:00 Swim Jones, Smith, Baker, White, Brown
9:00 10:00 Swim Jones, Smith, Baker, White, Brown
9:00 10:00 Swim Jones, Smith, Baker, White, Brown

(Every client in the database was listed – not just the ones actually in the activity and one line of output was produced for each client actually in the activity).

As an experiment I tried using the script as an expression in the query but the results were even more peculiar – all the clients in the database were listed as above repeatedly until the maximum field size was met.

I don't know if this is significant but the query SESSION is based on three tables CLIENT (People Stuff) ACTIVITY (Activity related stuff) and CLIENT2ACT which links the other two tables. CLIENT2ACT has two fields, “Name” (which looks up it value from the CLIENT table and AID (which looks up its value from ACTIVITY. Both “Name” and “AID” are unique indexed fields.

Does anyone know how to make this work?

 

[beetee]

A problem like this is generally d/t the queries; I seriously doubt the code has anything to do with it.

I would suggest you get the queries working correctly before trying to debug the report.

Normally, a problem like this is d/t a join that is causing Access to generate the duplicate records.

Are you sure that BOTH 'Name' and 'Aid' are unique indexed fields in the Client2Act table, or are they part of a single, unique index.

The reason I mention that is because if you are joining Client2Act on just the name, you will generate a row for each name/act pair.

You probably want to first generate a query from the Client2Act table that relates a name to a list of Acts, then join that query to whatever else you're using.