Merging multiple records into one long record...

[Joe]

Greetings,

I want to say up front that I'm NOT much more than a neophyte at Access. If
I ask a "stupid" question here, it's because I really don't know that much
about how to do queries.

Anyway, here is the situation. I've been given a database that has the
normal sort of name and address fields, but it also has three fields that
contain information about a purchased product.... the description of the
product in one field, the product number of the product in another field,
and the date the product was purchased in a third field.

What I've been asked to do is to take multiple records that have the same
name and address but different products and associated data, and combine
them into a single record with the three product records for each product
purchased appended to the end of the record in chronological order of when
they were purchased. I believe that no name/address combo has more than 5
products purchased.

How would I go about this? Is there an easy way with a single query, or
would I have to do multiple queries and then merge the output somehow?

I really am a noob, so no explanation can be worded to simply.

Thanks to all for any ideas you might have on this... I really appreciate
it.

Joe

 

[John W. Vinson]

What I've been asked to do is to take multiple records that have the same
name and address but different products and associated data, and combine
them into a single record with the three product records for each product
purchased appended to the end of the record in chronological order of when
they were purchased. I believe that no name/address combo has more than 5
products purchased.

How would I go about this? Is there an easy way with a single query, or
would I have to do multiple queries and then merge the output somehow?

You'll really need some VBA code to do this, given the variable number of
records. But the code is all written and well commented for your use at:

http://www.mvps.org/access/modules/mdl0004.htm

 

[Joe]

John,

Thank you for the response. I'll look through that page and see if I can
make heads or tales of it.

Thanks again,

Joe