Using a query to combine multiple lines into one.

[Deech]

I have created a query to specify the different reps assigned to a particular account.

The output is below:

Account NameAccount IDAccount CSNXX Account IdLogin NameUnique ID1stLine MgrAccount MgrA SpecialistB SpecialistC SpecialistAB OverlayCD OverlayABC INC123452158436A-100883User 112345;User1User 1ABC INC123452158436A-100883User 212345;User2User 2ABC INC123452158436A-100883User 312345;User3User 3ABC INC123452158436A-100883User 412345;User4User 4

I would like the output to look like below:

Account NameAccount IDAccount CSNXX Account Id1stLine MgrAccount MgrA SpecialistB SpecialistC SpecialistAB OverlayCD OverlayABC INC123452158436A-100883User 2User 4User 1 User 3

Could anyone help me acheive this through a query? I am new to access and need some help. Your help is greatly appreciated!

For some reason the view that i pasted came out different when i submitted my post. I have editted my post and added an attachment of th excel file to show the output.

 

[pbaldy]

I haven't looked at the attachment, but it sounds like this is the type of thing you're looking for:

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

 

[Deech]

Thanks Paul for your reply. Not sure how to go about applying the code you sent me via the link. Can you elaborate alittle on what it will do?

I haven't looked at the attachment, but it sounds like this is the type of thing you're looking for:

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

 

[pbaldy]

Sorry, I was assuming you had properly normalized data from the initial description. You could try a totals query that groups on account name and has Max for the other fields.

SELECT AccountName, Max(ASpecialist) AS MaxASpecialist, ...
FROM TableName
GROUP BY AccountName

 

[Deech]

Sorry, I was assuming you had properly normalized data from the initial description. You could try a totals query that groups on account name and has Max for the other fields.

SELECT AccountName, Max(ASpecialist) AS MaxASpecialist, ...
FROM TableName
GROUP BY AccountName

Thanks Paul, this function works but there are instances where there are multiple user's under a specialist column. When i do the above it only brings back one of the users. Is there a way to pull in both?