Consolidate records from one table into another?

[CW]

Due to a design error, we have a table for Invoices and a table for
InvoiceLines and the fields for the various lines are set up as InvLine1,
InvLine2, InvLine3, InvLine4, InvLine5. Accordingly there is InvLine1Text,
InvLine1Code, InvLine1Charge, InvLine1Tax, and so on.
The lines should really be records in one field, rather than being separate
fields.
A number of invoices have been created already. I need to gather these
existing lines all together and consolidate them into one new field i.e.
Get all the occurrences of InvLine*Text and put those into a new Text field
Get all the occurrences of InvLine*Code, into a new Code field
All the Charges into Charge
All the Tax values into Tax

It would be a monster task to do this manually or even by copying and
pasting in Excel, so if there some way I can do it through a query or two,
that would be great. Or any other way, for that matter!!
Thanks a lot
CW

 

[Arvin Meyer [MVP]]

First make a backup copy of your database. Create a new append query and use
it to add the InvoiceID, InvLine2, and whatever specific information there
is to the record. Update to line1. Do that for each successive field until
they are all appended to the first field, then rename the field, if you
like. Make sure that any forms, queries, and reports are renamed
accordingly. Once you are satisfied that everything is running correctly,
make another backup copy, and then delete the unnecessary fields.

 

[Olduke]

Due to a design error, we have a table for Invoices and a table for
InvoiceLines and the fields for the various lines are set up as InvLine1,
InvLine2, InvLine3, InvLine4, InvLine5. Accordingly there is InvLine1Text,
InvLine1Code, InvLine1Charge, InvLine1Tax, and so on.
The lines should really be records in one field, rather than being separate
fields.

Create your new table the way you want it. Then do an update query from the
original table or tables into your new table.

You might also try a make table query but this gives you much less control.

 

[CW]

Ah, I did suspect that an Append Query might be a solution, but wasn't sure
how to go about it. Many thanks for your prompt response and clear
explanation!
CW

 

[CW]

Great - thanks a lot, Arvin - I'll get to work on that rightaway. I
appreciate the detailed advice!
CW

 

[CW]

Thanks for your suggestions, Olduke
CW

Create your new table the way you want it. Then do an update query from the
original table or tables into your new table.

You might also try a make table query but this gives you much less control.