C
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
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