You have currently a table with structure something like
tblCurrentInvoices: CurrentID, InvoiceNo, InvoiceDate, ..., ArticleNo, ArticleQty, ArticlePrice, Amount
and you want to have 2 tables with structure like
tblNewInvoices: NewInvoiceID, InvoiceNo, InvoiceDate, ...
tblNewInvoiceRows: NewInvoiceRowID, NewInvoiceID, ArticleNo, ArticleQty, ArticlePrice, Amount
I assume NewInvoiceID and NewInvoiceRowID are autonumeric Primary Keys. CurrentID may be whatever.
Keep old file intact until you are sure all went well. Create structures for both new files, but add a column for InvoiceNo into tblNewInvoiceRows too.
Use INSERT query to add invoice info into tblNewInvoices - something like
Code:
INSERT INTO tblNewInvoices (InvoiceNo, InvoiceDate, ...)
SELECT DISTINCT InvoiceNo, InvoiceDate, ...
FROM tblCurrentInvoices
(the primary key is filled automatically)
Use INSERT query to add invoice rows info into tblNewInvoiceRows - something like
Code:
INSERT INTO tblNewInvoiceRows (InvoiceNo, ArticleNo, ArticleQty, ArticlePrice, Amount)
SELECT InvoiceNo, ArticleNo, ArticleQty, ArticlePrice, Amount
FROM tblCurrentInvoices
Use Update query to get NewInvoiceID from tblNewInvoices into tblNewInvoiceRows
Code:
UPDATE tblNewInvoiceRows INNER JOIN tblNewInvoices ON tblNewInvoices.InvoiceNo = tblNewInvoiceRows.InvoiceNo
SET tblNewInvoiceRows.NewInvoiceID = tblNewInvoices.NewInvoiceID
When done, you can remove field InvoiceNo from table tblNewInvoiceRows