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

flect

Registered User
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
Joined
Oct 28, 2006
Messages
870
What is the unique criterea on which you think you can check for duplicates
 

flect

Registered User
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
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 (Users: 0, Guests: 1)

Top Bottom