We help IT Professionals succeed at work.
Get Started

Query Access (multiple classifications in one field)

695 Views
Last Modified: 2015-04-24
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
Comment
Watch Question
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE