Using a query to combine multiple lines into one.

Deech

New member
Local time
Today, 07:06
Joined
Jun 26, 2007
Messages
5
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.
 

Attachments

Last edited:
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
 
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?
 

Users who are viewing this thread

Back
Top Bottom