Combine mutliple records into one record

[vierkarv]

Hi,
I have a table structured as follow:

Start
Name N1
Address A1
Item I1a
Item I1b
End
Start
Name N2
Address A2
Item I2a
Item I2b
End

I figured if I could add an additional column in the table, so that it becomes:
Order1 Start
Order1 Name N1
Order1 Address A1
Order1 Item I1a
Order1 Item I1b
Order1 End
Order2 Start
Order2 Name N2
Order2 Address A2
Order2 Item I2a
Order2 Item I2b
Order2 End

Then I combine them together.

Order1 N1 A1 I1a I1b
Order2 N2 A2 I2a I2b

At the end, I'll need them to be at item level (one record per item. So I'm thinking something like this)

Item1a N1 A1
Item1b N1 A1
Item2a N1 A1
Item2b N1 A1


How would I be able to do this in Access?

I can clarify if I'm being unclear..
Any help or input will be much appreciated. Thanks in advance.

 

[dhookom]

Are there always 2 items as per your example?

Are there 2 fields/columns or more?

Do you have any type of primary key? If not, your data is really scary since records are basically stored like marbles in a cloth bag. There is no order.


Duane
Hook'D on Access
MS Access MVP