Prevent Duplication when ruunning Append query

[Jahanzaib]

I have an append query (source MS sql server database table) having 4 fiedls

Date
Branch
Product
Sales

Sometimes data of one or more branch for one or more date does not received
and I dont wait for receiving complete data and run the query for current
date, when missing data receives I want to append only missing records for
specific date or branch.
Need your help,
Thanks in advance

 

[Brendan Reynolds]

I have an append query (source MS sql server database table) having 4
fiedls

Date
Branch
Product
Sales

Sometimes data of one or more branch for one or more date does not
received
and I dont wait for receiving complete data and run the query for current
date, when missing data receives I want to append only missing records
for
specific date or branch.
Need your help,
Thanks in advance

You can select only records that are not already in the target table with a
subquery as in the following example ...

INSERT INTO target ( targetid, [date], branch, product, sales )
SELECT source.sourceid, source.date, source.branch, source.product,
source.sales
FROM source
WHERE (((source.sourceid) Not In (select targetid from target)));

 

[John Spencer]

INSERT INTO [YourDestinationTable] ([Date],Branch, Product, Sales0
SELECT TS.Date, TS.Branch, TS.Product, TS.Sales
FROM [YOURSource] as TS LEFT JOIN [YourDestinationTable] as TD
ON TS.Date = TD.Date and TS.Branch = Td.Branch
WHERE TD.Date is Null


You could also build a compound unique index onf Date and Branch to keep any
duplicate records from being added.

To create a multiple field unique index (Compound index)
--Open up the WorkingTable in design mode
--Select View: Index from the menu
--Enter Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are
included.
--Close the index window and close and save the table

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..