append new records to table where record does not exist on table. (1 Viewer)

flect

Registered User.
Local time
Today, 18:04
Joined
Feb 26, 2008
Messages
86
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)

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]);
is what I thought would work, but sadly does not.
 
Last edited:

khawar

AWF VIP
Local time
Today, 11:04
Joined
Oct 28, 2006
Messages
870
What is the unique criterea on which you think you can check for duplicates
 

flect

Registered User.
Local time
Today, 18:04
Joined
Feb 26, 2008
Messages
86
there are 2 fields that have to be unique for the record, StockNumber & ReportID

Where it gets tricky is that there are multiple duplicates of StockNumber, but only ever 1 per ReportID, so I can't make both StockNumber and ReportID primary keys - but both together *could* be a primary key.
 

khawar

AWF VIP
Local time
Today, 11:04
Joined
Oct 28, 2006
Messages
870
Try this Sql

Code:
INSERT INTO tblStocktake ( StockNumber, ReportdId )
SELECT ToBePosted.StockNumber, ToBePosted.ReportdId
FROM [SELECT tblInvoices.StockNumber, tblInvoices.ReportdId
FROM tblInvoices LEFT JOIN tblStocktake ON (tblInvoices.StockNumber = tblStocktake.StockNumber) AND (tblInvoices.ReportdId = tblStocktake.ReportdId)
WHERE (((tblStocktake.StockNumber) Is Null) AND ((tblStocktake.ReportdId) Is Null))
]. AS ToBePosted;
 

Users who are viewing this thread

Top Bottom