Data consolidation help...

I have a table I'm having some trouble with and was hoping someone had already solved this issue.

I receive a report, which sadly can't be changed, that has a list of customer IDs and the sales reps assigned to them as well as the reps role. The problem is there are duplicate customer IDs since some accounts have multiple reps.

ID1 Rep1 Role1
ID1 Rep2 Role2
ID2 Rep3 Role3
ID3 Rep3 Role3
etc....

I need to get the list rotated so the customer IDs are the rows, the roles are the columns, and the name of the rep is in the value portion.

No matter how I run it, I still get a separate row for each customer ID and it doesn't place the reps name in one row, but instead on each row in the proper column. For each column in my query I have a simple IIF statement. If the Role=xxx the place the name of the rep in the spot, if not place 'N/A'.

IDs Role1 Role2 Role3
ID1 Rep1 N/A N/A
ID1 N/A Rep2 N/A
ID2 N/A N/A Rep3
ID3 N/A N/A Rep3

What I'm looking for is:

IDs Role1 Role2 Role3
ID1 Rep1 Rep2 N/A
ID2 N/A Rep2 N/A
ID3 N/A N/A N/A

I can't figure out how to get those rows combined.

Any ideas?

Try a cross tab query to see if that gives you what you want.

Failing that, create three queries to select the 3 roles and union them together.

I tried a crosstab query, but I can't get the actual rep's name to be in the role column. Instead it only returns a count/sum/avg of the number of times a rep is in that role. Unless someone knows how to get the value portion of the crosstab query to return the needed text and not a count.

I'm going to look into the Union Query. This looks like it is what I need. The only problem is I need it for 22 different role types. I was hoping to do this in only a few queries. Any ideas?

Thanks for the tips. I didn't even think of the Union Query.

Holy cow! I completely stand humbled and corrected.

I ran a crosstab just to see if I could use it in some way to help with the 22 queries I needed in the Union Query. I noticed other options in groupby other than count. I used 'First' and it gave me exactly what I needed.

I apologize neilg, I was wrong. The crosstab was exactly what I was looking for. I incorrectly assumed it operated similar to a pivot table in Excel.

Thanks again, this saved me a ton of work!

Happy to help!