We help IT Professionals succeed at work.

Reorganize data in a table in Access

GIStewart asked
I have data that is structured as in Table1 of the attached file.  It is sorted by the Ref field.  I am looking for a query that will combine the data for each Ref number, in a single record, as shown in Table2.  Is this possible?  Thank you.
Watch Question

Michael VasilevskySolutions Architect

Can you describe the logic you used to make Table2?


The data in FldA thru FldD remains intact for the first record for each reference number.  But, where there are additional records for a reference number, the data in FldA thru FldD is moved and "stacked" to the right.  Essentially, I am trying to get all the data for each reference number in 1 record.  Hope this helps.
MIS Liason
Most Valuable Expert 2012
The issues with your request is that:
1. You cannot easily "Create" new fields.
2. There is a hard limit of 255 fields per dataset
(So with your 4 fields, you will be limited to 64 records...)

This all being stated...
There is always a way to do anything...
How much trouble you are willing to go through to do it is another story.

But unless I am missing something, (please reply to mvasilevsky's request for clarification), I don't see an "easy" way to get the output you are requesting...

Michael VasilevskySolutions Architect
GIStewart, as implied by Jeff, your requirements sounds a bit awkward - maybe if you clarify why you want to do what you say you want to do we can provide an appropriate solution. Is this part of a report? Why do these values need to be in separate fields of the same record?



I'm getting the sense from your responses that my request is just not a reasonable thing to do in Access, and that it may be more trouble than it's worth.  So, I am going to see if it can be done via a macro in excel.  Thanks for your responses.


I decided to try another program to get the desired solution.