Grouping Data Fields

[NEWER USER]

Can the following be accomplished in Access 2K3?

Name City
ABC123 Tampa
ABC123 Orlando
ABC123 Miami

I want to group on name and get the following result in City field or add
another field to store the combined city names.

ABC123 Tampa, Orlando, Miami

 

[John W. Vinson]

Can the following be accomplished in Access 2K3?

Name City
ABC123 Tampa
ABC123 Orlando
ABC123 Miami

I want to group on name and get the following result in City field or add
another field to store the combined city names.

ABC123 Tampa, Orlando, Miami

It's easiest to use some VBA code to do this; see
http://www.mvps.org/access/modules/mdl0004.htm

 

[NEWER USER]

I followed the link and believe this is exactly what I need. I copied the
Function into the Nothwinds database module and then copied the statement
below into the SQL view of a new query.

SELECT Orders.*, fConcatChild("Order
Details","OrderID","Quantity","Long",[OrderID]) AS SubFormValuesFROM Orders;

When I tried to save, I got an error message 'The SELECT staement includes a
reserved word or an argument name that is mispelled. Any ideas what might be
incorrect?

 

[John W. Vinson]

I followed the link and believe this is exactly what I need. I copied the
Function into the Nothwinds database module and then copied the statement
below into the SQL view of a new query.

SELECT Orders.*, fConcatChild("Order
Details","OrderID","Quantity","Long",[OrderID]) AS SubFormValuesFROM Orders;

When I tried to save, I got an error message 'The SELECT staement includes a
reserved word or an argument name that is mispelled. Any ideas what might be
incorrect?

The syntax error I see there is that you don't have a blank between
SubFormValues and FROM - does that make a difference?

Do you in fact have tables with the same tablenames and fieldnames as the
example? i.e. a table named [Order Details] with fields OrderID and Quantity?

 

[NEWER USER]

That space made all the difference. I will now try and apply it to my
application. Thanks for the sharp eagle eye.

I followed the link and believe this is exactly what I need. I copied the
Function into the Nothwinds database module and then copied the statement
below into the SQL view of a new query.

SELECT Orders.*, fConcatChild("Order
Details","OrderID","Quantity","Long",[OrderID]) AS SubFormValuesFROM Orders;

When I tried to save, I got an error message 'The SELECT staement includes a
reserved word or an argument name that is mispelled. Any ideas what might be
incorrect?

The syntax error I see there is that you don't have a blank between
SubFormValues and FROM - does that make a difference?

Do you in fact have tables with the same tablenames and fieldnames as the
example? i.e. a table named [Order Details] with fields OrderID and Quantity?