Prevent Access update query from overwriting distinct records?

Question

To start, I am fairly new to Access and databases in general. I am trying to design a database which will allow my company to precisely track items as they come through our shipping, receiving, and inspection areas. Currently, I have a linked Excel sheet which gets updated daily via a company data extract. I then run an update query to append only new records to the database primary table. The table fields to append are: Part_Number, Lot_Number, Item_Description, PO_Number, ProjectID, Project_Name, Quantity, Reason_Code, and Dock_Receipt_Date.

The Issue I am having is that there can be multiple records with any combination of same-valued fields with a minimum of one field that makes them distinct (no true duplicate records exist in the Excel sheet), and when the update query runs, all records with same-valued fields become true duplicates. That is, the first update query, when the db is empty, appends all distinct records. During the second and subsequent updates, records with the same part number, or lot number, or PO number...even though they are distinct...get overwritten and become duplicates. I am wondering if there is a way to preserve the distinct records with an update query while appending new records to the db; or if there is a way to only append new records, ignoring existing ones?

As a note, I have tried adding an index to the Excel sheet, but when I run the update query, the database grows by hundreds of records as it's treating the Excel records as new each time.

P.S. The current SQL is:

UPDATE [Tbl 01 RDM DB] 
 RIGHT JOIN [Tbl 02a Pending Inspection Items] 
         ON [Tbl 01 RDM DB].Part_Number = [Tbl 02a Pending Inspection Items].Part_Number 
        SET [Tbl 01 RDM DB].Lot_Number = [Tbl 02a Pending Inspection Items].[Lot_Number]
          , [Tbl 01 RDM DB].Part_Number = [Tbl 02a Pending Inspection Items].[Part_Number]
          , [Tbl 01 RDM DB].Item_Desc = [Tbl 02a Pending Inspection Items].[Item_Desc]
          , [Tbl 01 RDM DB].PO_Number = [Tbl 02a Pending Inspection Items].[PO_Number]
          , [Tbl 01 RDM DB].[Project/CID] = [Tbl 02a Pending Inspection Items].[Project/CID]
          , [Tbl 01 RDM DB].Dock_Receipt = [Tbl 02a Pending Inspection Items].[Dock_Receipt]
          , [Tbl 01 RDM DB].Reason_Code = [Tbl 02a Pending Inspection Items].[Reason_Code]
          , [Tbl 01 RDM DB].QTY = [Tbl 02a Pending Inspection Items].[QTY]
          , [Tbl 01 RDM DB].Project_Name = [Tbl 02a Pending Inspection Items].[Project_Name];

When your join is only on "Part Number", the script will look for every record in table 1 with a part number that matches table 2, and update accordingly. If you want to update records with more granularity, you need to further specify your join. — Mike Abramczyk
Thank you for your reply, Mike. How do you add additional ON locations? — Nic Gaudio
Hey Nic - your code should look like 'tableA join tableB on condition1 and condition2 and ...' — Mike Abramczyk

Nic Gaudio Nic Gaudio · Accepted Answer · 2013-09-20T20:43:39

Nope, not at all. I haven't done anything to the Excel sheet. Here is the code that I've gotten to work.

  INSERT INTO [Tbl 01 RDM DB]
(Part_Number, Lot_Number, [Project/CID], Project_Name, Dock_Receipt, Item_Desc, QTY, Reason_Code)
SELECT 
[Tbl 02 Inspection Items].Part_Number, 
[Tbl 02 Inspection Items].Lot_Number, 
[Tbl 02 Inspection Items].[Project/CID], 
[Tbl 02 Inspection Items].Project_Name, 
[Tbl 02 Inspection Items].Dock_Receipt, 
[Tbl 02 Inspection Items].Item_Desc, 
[Tbl 02 Inspection Items].QTY, 
[Tbl 02 Inspection Items].Reason_Code
FROM [Tbl 02 Inspection Items] 
LEFT JOIN [Tbl 01 RDM DB]

Below, I'm trying to only insert the records that are unique in relation to the specified combinations of fields.

ON 
[Tbl 02 Inspection Items].Part_Number <> [Tbl 01 RDM DB].Part_Number
AND [Tbl 02 Inspection Items].Lot_Number <> [Tbl 01 RDM DB].Lot_Number
AND [Tbl 02 Inspection Items].[Project/CID] <> [Tbl 01 RDM DB].[Project/CID]
AND [Tbl 02 Inspection Items].Project_Name <> [Tbl 01 RDM DB].Project_Name
AND [Tbl 02 Inspection Items].Dock_Receipt <> [Tbl 01 RDM DB].Dock_Receipt
AND [Tbl 02 Inspection Items].QTY <> [Tbl 01 RDM DB].QTY;

When I run the query with a blank db, it works fine. But later executions produce the mentioned error.

Prevent Access update query from overwriting distinct records?

1 Answers