I'm having a wee bit of confusion with what I assume is something really basic.
I have tblInvoices which contains StockNumber & ReportID
I then have an append query which appends StockNumber & ReportID to
tblStocktake
The problem is that sometimes there are multiple duplicates if the StockNumber is on more than one invoice, and also on tblStocktake but with different ReportID's - therefore I cannot make StockNumber & ReportID the primary keys on tblStocktake (similar to the solution as per http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html)
is what I thought would work, but sadly does not.
I have tblInvoices which contains StockNumber & ReportID
I then have an append query which appends StockNumber & ReportID to
tblStocktake
The problem is that sometimes there are multiple duplicates if the StockNumber is on more than one invoice, and also on tblStocktake but with different ReportID's - therefore I cannot make StockNumber & ReportID the primary keys on tblStocktake (similar to the solution as per http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html)
Code:
INSERT INTO tblStocktake ( StockNumber, ReportID )
SELECT tblInvoices.StockNumber, tblInvoices.ReportID
FROM tblInvoices INNER JOIN tblStocktake ON tblInvoices.StockNumber = tblStocktake.StockNumber
WHERE ([tblInvoices].[StockNumber]<>[tblStocktake].[StockNumber]);
Last edited: