Query Access (multiple classifications in one field)

I have a data set (query) that includes 2 columns; [Account Number], [Activity_Code]. Each [Account_Number] could have as many as 6 [Activity_Codes]. The data set contains 4,332 records, however, there are only 3,914 unique account numbers. I want to create a query that shows the 3,914 unique records with the multiple activity codes separated by commas in the activity code column. I can then link that query to the names associated with the unique account number and create my report.

I presume a CrossTab query should be used but I can't seem to get the results I need.

Attached is an Excel Spreadsheet that includes two tabs, "CurrentView" and "View I Seek". The "View I Seek" tab contains a sampling of the data on the "CurrentView" tab and illustrates what I am looking to produce.

One solution would be to add a column that assigns a sequence number to each activity code, for each account number. e.g. acct. num=123456 and has three activity codes. The sequence for each activity code would be 1, 2 or 3. Acct. num=789012 and has two activity codes with a sequence of either 1 or 2. Obviously the acct. nums with only one activity code would have a sequence of 1.

In such a case, how would I create that sequence number since it in not a part of the current data that I am querying?

I hope this is sufficient detail to make understandable, what I am trying to do.
ExampleQuery.xlsx
ergenbgrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You can handle this with sorting and grouping in the report.  You don't need to flatten the table.  If you want to display the Activity Codes horizontally rather than vertically, then you need a main report and a sub report.  The main report is based on a query that selects ONLY distinct Account numbers.  The sub report can be defined to show multiple columns and should be based on a query similar to the attached spreadsheet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.