• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

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.
1 Solution
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now