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.