combine 2 records data to one record if one field matches

[DCV0204]

Hi, I need some help combining multiple records when the ProviderNPI matches another record. Here is some information from my table:

LastName	ProviderNPI	GroupName	GroupNPI
Potter	1538340682	Christ Community Health Services, Inc.	1053572206
Potter	1538340682	Christ Community Health Services, Inc.	1841513058
Potter	1538340682	Christ Community Health Services, Inc.	1982686614

as you can see I have 3 records with the same LastName, ProviderNPI and GroupName; but the GroupNPI is different for each record.
I would like to combine these into one record to look like this:

LastName	ProviderNPI	GroupName	GroupNPI	Gr2_Name	Grp2_NPI	Gr3_Name	Grp3_NPI	Gr4_Name	Grp4_NPI
Potter	1538340682	Christ Community Health Services, Inc.	1053572206	Christ Community Health Services, Inc.	1841513058	Christ Community Health Services, Inc.	1982686614

Thanks,
Lisa

[June7]

Why would you want to denormalize data?

[DCV0204]

Because I am wanting to mail merge the information into a letter. Is this possible in a query?

[June7]

What you want won't be easy, denormalizing seldom is, especially because each LastName will not have the same number of records and the field names you want are not generated by data as would be done in a CROSSTAB query. Also, I don't think mail merge works nice with calculated fields. Will probably require VBA code to accomplish, possibly having to write records to a temp table and use that table as the data source for the mail merge.

Review http://forums.aspfree.com/microsoft-...ry-322123.html