Combine multiple grouped rows into one

[ctopmep]

I have a table containing data similar to this:

GRoupID JobDesc FirstName LastName

1 1 Name1 LName1
1 2 Name2 LName2
2 1 Name3 LName3
2 2 Name4 LName4


I need to use the data for creating one letter per GroupID with Mail Merge, so I need a query that would create rows grouped by GroupID.

GroupID JD1FirstName JD1LastName JD2FirstName JD2LastName

1 Name1 LName1 Name2 LName2
2 Name3 LName3 Name4 LName4

In the real table there are ten JobDesc and I need to select more than just FirstName and LastName, so the query I was trying to write become very long and complicated. Can somebody help me?

 

[dhookom]

The easiest and quickest solution relies on your JobDesc being numbers 1 through a few. Is this truly a good representation of your data or is it over simplified/misleading?

Duane
Hook'D on Access
MS Access MVP

 

[ctopmep]

JobDesc in this table is a number, but it is a foreign key that matches real description of the job in another table. The primary key in table that holds job descriptions which matches JobDesc is autonumber. Number of jobs is ten, their description would not be changed in time and are predefined. Even if table that holds job descriptions should not change in time, I think better solution would be if resulting tables's column names could be dynamically generated containing part of textual description of the job not relaying on fact that JobDesc1 has always ID=1.

 

[dhookom]

I think you can use a multi-valued crosstab as described in faq701-4524.

Duane
Hook'D on Access
MS Access MVP

 

[ctopmep]

I saw the example from FAQ701-4524, but the problem is that in TRANSFORM, I cannot (or I don't know how to) use aggregate function that will choose from 10 FirstName or 10 LastName fileds based on JobDesc.

 

[ctopmep]

I found another post with example that I guess is yours. I think it will help.

Thank you

http://msgroups.net/microsoft.publi...mbine-multiple-records-into-one-record-in-a-Q

 

[dhookom]

That other post uses the same logic for multiple values.

Duane
Hook'D on Access
MS Access MVP