Build Multivalue Output

[JoeReed]

Greetings...

We maintain a database for a client as a relational (one to many) database and the client would like us to provide a "flat" file of the data for them to have inhouse.

Current data example

ID ShowCode
10001 CH09
10001 LB09
10001 RN09
10002 CP08
10002 CP09

I would like to build output like this;

ID ShowCodes
10001 CH09*LB09*RN09
10002 CP08*CP09

I have written a program to do this, however can this be done with a query?

Thank you in advance for any information.

 

[PHV]

faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Assuming a second table with the ID field as the primary key and a larger text field ShowCodes.
Run these 4 queries:

DELETE tblJoeReeds.*
FROM tblJoeReeds;

INSERT INTO tblJoeReeds ( ID )
SELECT tblJoeReed.ID
FROM tblJoeReed
GROUP BY tblJoeReed.ID;

UPDATE tblJoeReed 
INNER JOIN tblJoeReeds ON tblJoeReed.ID = tblJoeReeds.ID 
SET tblJoeReeds.ShowCodes = [ShowCodes] & "*" & [ShowCode];

UPDATE tblJoeReeds 
SET tblJoeReeds.ShowCodes = Mid([ShowCodes],2);

Duane
Hook'D on Access
MS Access MVP

 

[JoeReed]

Thank you both...I will look at these solutions this weekend. Both look better than the code I had doing this.