We help IT Professionals succeed at work.

Query Access (multiple classifications in one field)

Ray Ergenbright
Ray Ergenbright asked
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.
Watch Question

Distinguished Expert 2017
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.