sqlms-accesscomposite-key

How can I check if A COMPOSITE KEY doesn't exist in a table that I am appending to from a query on two other tables?


I am trying to append data from two tables in my dataBase to a third table with this code in a Microsoft Access database:

INSERT INTO percentage ( productId, productName, salesPrice, currentProvider, offerProvider, offerPrice, percentage )
SELECT products.productId AS productId, products.productName AS productName, products.pricePerUnitOrKg AS salesPrice, products.providerId AS currentProvider, productsByProviders.providerId AS offerProvider, productsByProviders.pricePerUnitOrKg AS offerPrice, Round(products.pricePerUnitOrKg/productsByProviders.pricePerUnitOrKg,3) AS percentage
FROM products INNER JOIN productsByProviders ON products.productId = productsByProviders.productId

Since the primary key in percentage is a composite key combined of the two fields: productId and offer provider, I want to make sure the row, (offer) - doesn't already exist in the percentage table with this code:

WHERE NOT Exists (SELECT 1 FROM percentage WHERE percentage.productId = productsByProvider.productId AND percentage.offerProvider = productsByProvider.providerId));

which I saw used here: How to use NOT EXISTS with COMPOSITE KEYS in SQL for inserting data from POJO

This isn't working, and I keep getting an error when trying to append to the table, which says: enter image description here

And when I click yes, it doesn't append the rows that don't exist in the percentage table, which is what I want it to do.

Why is this happening?


Solution

  • I removed the NOT EXISTS part of the query, like June7 suggested in the comments, and it works, the error is still showing up, but for my purposes, this is enough.