Concatenation in a Totals query

[Robin Chapple]

I have a Totals query based on a query with the fields [ClubName],
[Zone] and [Group] in the same table.

Clubs are in several groups and any one of four zones.

This produces a concatenated list of clubs in each zone:

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

All of that thanks to help in this newsgroup.

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:

SELECT qryZones.Group, Concatenate("SELECT ClubName FROM qryZones
WHERE Group =" & [Group]) AS Zones
FROM qryZones
GROUP BY qryZones.Group, 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.

 

[Ken Snell [MVP]]

Group is a reserved word in ACCESS, so wherever you use it as a field name
you must surround it with [ ] characters:

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

 

[Duane Hookom]

The other possible issue depends on the data type of [Group]. If it is text,
you need to use:
SELECT qryZones.[Group], Concatenate("SELECT ClubName FROM qryZones WHERE
[Group] =""" & [Group] & """") AS Zones
FROM qryZones
GROUP BY qryZones.[Group], Concatenate("SELECT ClubName FROM qryZones WHERE
[Group] =""" & [Group] & """");


--
Duane Hookom
MS Access MVP

Group is a reserved word in ACCESS, so wherever you use it as a field name
you must surround it with [ ] characters:

SELECT qryZones.[Group], Concatenate("SELECT ClubName FROM qryZones
WHERE [Group] =" & [Group]) AS Zones
FROM qryZones
GROUP BY qryZones.[Group], Concatenate("SELECT ClubName FROM qryZones
WHERE [Group] =" & [Group]);
--

Ken Snell
<MS ACCESS MVP>

I have a Totals query based on a query with the fields [ClubName],
[Zone] and [Group] in the same table.

Clubs are in several groups and any one of four zones.

This produces a concatenated list of clubs in each zone:

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

All of that thanks to help in this newsgroup.

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:

SELECT qryZones.Group, Concatenate("SELECT ClubName FROM qryZones
WHERE Group =" & [Group]) AS Zones
FROM qryZones
GROUP BY qryZones.Group, 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.

 

[Robin Chapple]

Thanks Ken that did the trick. Where do I find a list of reserved
words please? Help was no help.

 

[Ken Snell [MVP]]

See these Knowledge Base articles for more information about "reserved
words":

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken that did the trick. Where do I find a list of reserved
words please? Help was no help.

Group is a reserved word in ACCESS, so wherever you use it as a field name
you must surround it with [ ] characters:

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