Simple Maybe? Report Formatting Question 1

[xweyer]

I have a report based on a query with the relevant fields being Begin, End, Activity, & Name. I’ve got the report set up and grouped so the output looks like this:

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

What I’d like to produce for output instead is:

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

How could I best accomplish this?

 

[CosmoKramer]

See scriverb's response in the following thread for a recent explanation

thread703-495410

 

[xweyer]

I've doctored scriverb's script in the thread you indicated me to reflect my table and field names as follows:

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("tblClient", 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

However when I try to run the report I get a compile error "User defined type not defined" with the line "Dim db As DAO.Database" highlighted.

Any idea where I've gone wrong.

 

[CosmoKramer]

What version on Access are you using?

If you are using A97 take out the DAO. reference.

If it's A2000, go to Tools | References and select "Microsoft DAO 3.6 Object Library" and click OK.

 

[xweyer]

Thanks again for your information. I am using 2000 and once I followed your directions and corrected line 6 above to read "Set rs = db.openrecordset("Client", dbOpenDynaset)" I got the names printing horizontally.

The output now would be perfect except for one thing. Instead of getting the names of the "clients" who are actually in the activity I get the names of all "clients" for every activity.

This was not the case when the output was vertical. The vertical output was correct. (So if only Jones was in swimming his would be the only name to appear.)

Does this make any sense to you?

 

[CosmoKramer]

Set rs = db.openrecordset("Client", dbOpenDynaset)

Is Client the name of your query? It looks like it should work if you put the query name there......

 

[xweyer]

Yes "Client" is the name of the query.

What is really baffling me is that the query "Client" by itself produces the correct information (the right clients are paired with the right activities).

 

[xweyer]

This is more complex than I originally thought. In examining several pages of data what the script actually seems to be doing is pulling every name from the database and repeating them X the number of clients in a given activity. So if the database were tracking a total of 5 people (Smith, Jones, Baker, Cook, Miller) the output for Swim would look like this now. So "Swim" would now look like this.

Swim Smith, Jones, Baker, Cook, Miller

Smith, Jones, Baker, Cook, Miller

Smith, Jones, Baker, Cook, Miller

(One row of output for each of the three clients who are actually in the activity)

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