0

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?

Thunder Coder
  • 104
  • 14
  • access is not full databse, so that many things that sql server can do, msacces doesn't support or has it only rudimentary implementation. your message indicates, that your data types in the exist subqery doesn't fit to compare, so that an internal convertion is made, therefore not the query per se is problematic, your data is – nbk Oct 22 '22 at 22:31
  • 1
    Why do you need to replicate this data into another table as opposed to just retrieving in query? If you truly have those two fields defined as compound key (or compound index), records that duplicate the key pair should be rejected and those that don't should insert. Not really following need for NOT EXISTS. Edit question to show sample data and desired result. – June7 Oct 23 '22 at 00:33
  • Use a _Left Join_ as shown in the [answer](https://stackoverflow.com/a/7147315/3527297) you link to yourself. – Gustav Oct 23 '22 at 09:23
  • @june7, I tried doing what you suggested, it's appending the lines that don't exist, but it is still showing the error. – Thunder Coder Oct 23 '22 at 12:44
  • Okay, now I get what you were attempting with NOT EXISTS. However, posted attempt doesn't really seem to fully employ examples from linked answer. The first doesn't even use NOT EXISTS. – June7 Oct 23 '22 at 14:29
  • @june7, in the example I linked there are only two tables, so I wasn't sure what I should do with my third one, link it like shown in the example? So, I tried to add the code that seemed most reasonable. and because I just started a sql course (this wasn't an assignment of course) I don't really know what I'm doing. – Thunder Coder Oct 24 '22 at 08:13
  • Build a query that joins Products and ProductsbyProviders (this would be the `yourtable` dataset) then join that query to percentage. – June7 Oct 24 '22 at 15:03
  • @june7, ok I'll try it out. – Thunder Coder Oct 25 '22 at 13:35

1 Answers1

0

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.

Thunder Coder
  • 104
  • 14