Listing names in one field

[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

 

[Duane Hookom]

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

 

[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

 

[Duane Hookom]

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
--

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.

 

[Robin Chapple]

Thanks Duane,

That is now complete. I do appreciate your help.

Robin Chapple

 

[Robin Chapple]

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.

 

[Duane Hookom]

If Group is a text field, you need to use:
Groups: Concatenate("SELECT ClubName FROM qryZones WHERE Group =""" &
[Group] & """")

--
Duane Hookom
MS Access MVP

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.