Listing names in one field

  • Thread starter Thread starter Robin Chapple
  • Start date Start date
R

Robin Chapple

I have a table of club names which are grouped in zones. Each club
record includes the allocated zone.

I need a report which lists all clubs on zone one as a continuous
string of the names. (I hope that explains properly)

So that the report would say:

Zone One

Team 1; Team 2; Team 3: and so on.

Zone Two

Team 21; Team 40; Team 62; and so on.

Zone Three

Team 14; Team 18; Team 33 and so on

How do I do that?

Thanks,

Robin Chapple
 
Thanks that sample does produce the format that I need.

My table has the Zone number and the club name in the same table.

I have changed the ADO references to DAO.

I have edited your SQL to this:

SELECT qryZones.Zone, Concatenate("SELECT ClubName FROM qryZones WHERE
Zone =" & [Zone]) AS Zones
FROM qryZones;

Which produces multiple examples of each zone, one for each club.

What have I missed?

Thanks,

Robin Chapple
 
You can change your query to a totals query and group by both of the
columns. This will return one record per zone.

--
Duane Hookom
MS Access MVP
--

Robin Chapple said:
Thanks that sample does produce the format that I need.

My table has the Zone number and the club name in the same table.

I have changed the ADO references to DAO.

I have edited your SQL to this:

SELECT qryZones.Zone, Concatenate("SELECT ClubName FROM qryZones WHERE
Zone =" & [Zone]) AS Zones
FROM qryZones;

Which produces multiple examples of each zone, one for each club.

What have I missed?

Thanks,

Robin Chapple




There is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane with
sample usage.
Click to expand...
Click to expand...
 
Douane,

I have now attempted to produce a similar report for the clubs in
groups. There is a field [Group] in the same table so I substituted:

Group for zone in the design of the query and this:

Groups: Concatenate("SELECT ClubName FROM qryZones WHERE Group =" &
[Group])

for the other field. all that I have done is to change Zone to Group.

I now get an error message:

"Run time error 3145
Syntax error in WHERE clause"

A Google search did not show any similar reference in the first answer
that I scanned.
 
If Group is a text field, you need to use:
Groups: Concatenate("SELECT ClubName FROM qryZones WHERE Group =""" &
[Group] & """")

--
Duane Hookom
MS Access MVP


Robin Chapple said:
Douane,

I have now attempted to produce a similar report for the clubs in
groups. There is a field [Group] in the same table so I substituted:

Group for zone in the design of the query and this:

Groups: Concatenate("SELECT ClubName FROM qryZones WHERE Group =" &
[Group])

for the other field. all that I have done is to change Zone to Group.

I now get an error message:

"Run time error 3145
Syntax error in WHERE clause"

A Google search did not show any similar reference in the first answer
that I scanned.
Click to expand...
 
Back
Top