notification of which posts are in one table and not in the other

[carolineJ]

I have a table with products- One of the fields is product price. Once a month I get a new price list in Excel format from our parent company consisting of product number and the new price for each product. I convert this list into a a separate access table and run an update query which changes the old price to the new price for each product in the product table. How ever sometimes the price list contains new products which are not registered in my access table. That means I have to add them do my database manually. I was wondering if there is some way I can write the query so that it notifies me which product numbers that are new (i.e does not have an entery in the access table) so that I don't have to manually compare the two lists every time?

 

[BNPMike]

Do an outer join between your new table and the existing table. The Where criterion should be something that will not be true for the existing table eg PRICE IS NULL. That will then just show the new records. You can then turn this query into an insert if you want to add the new records.

Of course you can just insert all the new price records if you want. Access will reject all the duplicates and tell you therefore how many were actually added.
mike.stephens@bnpparibas.com

 

[carolineJ]

thank you BNPMike, but can you help me a little more? I tried your second suggestion, i.e just write an insert query that will add all the new records but it din't work. I made a test table consisting of three product with product number and price, then I made a second table (identical to the first one, ie product number and price Not the same price though, the new ones) consisting of the same three products and a new product. When i run this query, the office assisant tells me that three posts were not added because of primary key error or something like that, which is good because I don't want any duplicates. However the fourth, new product that were supposed to be added is not added either? Please tell me what I did wrong Mike...

 

[carolineJ]

moving up so that BNPMike or anyone else can see it

 

[carolineJ]

problem solved! thank you a lot BNPMike!