Merging Records

[Guest]

Hi,

I have a table (tbl_Table). It has the following fields and example data.
I typed this in a table in MS Word so I trust you can copy it into word to
make it more legible.


Type Purchase_Order Product Description Order_Units Order_Retail Due_Period Due_Date Received_Units Received_Retail Received_Period Received_Date
PO 555555 2345678 Widget 10 $15 Jul_07 15/07/07
PO 555555 87654321 Tester 5 $25 Jul_07 21/07/07
Rec 555555 87654321 Tester 4 $20 Jul_07 21/07/07
Rec 555555 87654321 Tester 1 $5 Jul_07 29/07/07
Rec 888888 11111111 Yo-Yo 99 $150 Aug_07 01/08/07


The result I am after follows. I want to merge the records where the
Purchase_Order and Product fields match so there is only one record. It is
possible to have two receipts for one Purchase Order but not vica versa. In
the required table there is no need to havethe Type filed. Also to make it a
little more challenging the Received Date becomes First_Received_Date and
shows of the date the first Receipt.


Purchase_Order Product Description Order_Units Order_Retail Due_Period Due_Date Received_Units Received_Retail Received_Period First_Received_Date
555555 2345678 Widget 10 $15 Jul_07 15/07/07
555555 87654321 Tester 5 $25 Jul_07 21/07/07 5 $25 Jul_07 21/07/07
888888 11111111 Yo-Yo 99 $150 Aug_07 01/08/07

Thanks in advance

 

[Guest]

Andrew,
There is no practical way to do that and no practical reason.
Since there can be one to n PO detail lines, you don't know how many fields
you will need in the table where you want to put these things.

I know there are times to de-normalize, but this is different.

Can you explain your purpose, perhaps we have an answer that will help
accomplish your goal.

 

[Guest]

Try this ---
Open a new query and put your table in it twice. Access will add a suffix
to the second copy like '_1'.
Left join the Purchase_Order fields. For purchase order fields use the left
table and set TYPE criteria as PO. Use the right table for receipt fields
and set TYPE criteria as REC.