We help IT Professionals succeed at work.

Reorganize data in a table in Access

GIStewart
GIStewart asked
on
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.
Data-reorg.mdb
Comment
Watch Question

Michael VasilevskySolutions Architect

Commented:
Can you describe the logic you used to make Table2?

Author

Commented:
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
Commented:
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...


JeffCoachman
Michael VasilevskySolutions Architect
Commented:
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?
Best,

MV

Author

Commented:
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.

Author

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