Append Query Where Clause Coding

[kjschmitz]

Basically I am trying to combine certain fields from two tables into one using an append query. The trick comes in that I want to only include records that have changed since the query was last run. When I first run my query, I get the results expected to populate the desired table (Unit_Bid_test).

I must not be nesting my WHERE Clause correctly, because I can pass updates from my Unit_Pricing table just fine, but updates made to my Eligible_Bidders table are unseccessful. Here is the base query:

INSERT INTO Unit_Bid_test ( Bid_Tab_ID, Unit_Price_ID )
SELECT Eligible_Bidders.Bid_Tab_ID, Unit_Pricing.Unit_Price_ID
FROM Eligible_Bidders, Unit_Pricing

WHERE (((Unit_Pricing.Project_ID)=[Eligible_Bidders].[Project_ID])) OR (((Eligible_Bidders.Project_ID)=[Unit_Pricing].[Project_ID]));

I have added criteria to the WHERE clause in an attempt to limit the appending of the table to new records:

INSERT INTO Unit_Bid_test ( Bid_Tab_ID, Unit_Price_ID )
SELECT Eligible_Bidders.Bid_Tab_ID, Unit_Pricing.Unit_Price_ID
FROM Eligible_Bidders, Unit_Pricing

WHERE (((Eligible_Bidders.Bid_Tab_ID) Not In (select Bid_Tab_ID from Eligible_Bidders)) AND ((Unit_Pricing.Project_ID)=[Eligible_Bidders].[Project_ID])) OR (((Unit_Pricing.Unit_Price_ID) Not In (select Unit_Price_ID from Unit_Bid_test)) AND ((Eligible_Bidders.Project_ID)=[Unit_Pricing].[Project_ID]));

This seems to work for updates made to the Unit_Pricing table, but not updates made to the Eligible_Bidders table.

Any idea how to nest the WHERE statement to correct this issue?

 

[kjschmitz]

Lack access to the DB right now, but I think my issue was using:

((Eligible_Bidders.Bid_Tab_ID) Not In (select Bid_Tab_ID from Eligible_Bidders))

and not

((Eligible_Bidders.Bid_Tab_ID) Not In (select Bid_Tab_ID from Unit_Bid_test))

 

[JonFer]

What are the primary keys for each table?

 

[kjschmitz]

My table structure is as follows:

Eligible_Bidders
   Bid_Tab_ID   dbLong  PrimaryKey  Indexed
   Project_ID   dbLong  ForiegnKey  Indexed
   Contractor_ID   dbLong  ForiegnKey  Indexed
   Bid_Amount   dbCurrency

Unit_Bid_test
   Unit_Tab_ID   dbLong  PrimaryKey  Indexed
   Bid_Tab_ID   dbLong  ForiegnKey  Indexed
   Unit_Price_ID   dbLong  ForiegnKey  Indexed
   Unit_Amount   dbCurrency

Unit_Pricing
   Unit_Price_ID   dbLong  PrimaryKey  Indexed
   Project_ID   dbLong
   Unit_Price_Number   dbLong
   Unit_Price_Desc   dbText

The Eligible_Bidders table is created using a many to many relationship between a Projects table and a Contractors table. The Projects_ID field is also found in the Unit_Pricing table to that Project Managers can add a unit cost to a given project.

And I have confirmed that the changes work:

INSERT INTO Unit_Bid_test ( Bid_Tab_ID, Unit_Price_ID )
SELECT Eligible_Bidders.Bid_Tab_ID, Unit_Pricing.Unit_Price_ID
FROM Eligible_Bidders, Unit_Pricing
WHERE (((Eligible_Bidders.Bid_Tab_ID) Not In (select Bid_Tab_ID from Unit_Bid_test)) AND ((Unit_Pricing.Project_ID)=[Eligible_Bidders].[Project_ID])) OR (((Unit_Pricing.Unit_Price_ID) Not In (select Unit_Price_ID from Unit_Bid_test)) AND ((Eligible_Bidders.Project_ID)=[Unit_Pricing].[Project_ID]));

I am now working to incorporate a delete query which will check for Unit Prices and Contractors that have been removed from the Unit_Pricing and Eligible_Bidders tables as well:

DELETE Unit_Bid_test.Bid_Tab_ID, Unit_Bid_test.Unit_Price_ID, Unit_Bid_test.Unit_Amount
FROM Unit_Bid_test
WHERE (((Unit_Bid_test.Bid_Tab_ID) Not In (select Bid_Tab_ID from Eligible_Bidders))) OR (((Unit_Bid_test.Unit_Price_ID) Not In (select Unit_Price_ID from Unit_Pricing)));

My question now is what best practice should I be using to execute these queries in succession? Can I combine them into a single query? Should I run them via VBA to ensure that the delete is done prior to the append?