How best to remove unmatched records (pun intended)

[dcumming]

I run a records business and have all my stock, and suppliers' stock in an Access 2016 database

I have externally linked tables to Excel spreadsheets that i get from the suppliers (tblSupplierData), these are updated daily.

I need to have a main central table (tblAllData) where each individual record has a unique ID (tblSupplierData.UniqueID).

This table is exported to CSV, and used on various marketplaces to enable delete uploads, so that if an item is no longer available, it is removed from my account on the marketplace. All i have to do is upload the file - the marketplace takes care of the rest, but requires that i have a unique ID for each item, and that unique ID cannot change.

So, what i need to do is delete records from tblAllData that no longer exist in tblSupplierData

I'm sure this should be simple as it seems fairly normal thing to do

The workaround i have got in mind, if all else fails, is to run an additional query that takes the relevant data from both tblSupplierData and tblAllData and create an entirely new table, but with there being currently 200k records, that seems a little inefficient, doubley so in that I've got another 200k records to go.

Any help appreciated

Regards
David

 

[theDBguy]

Hi David. I'm sure what you want is very possible; but since we cannot see what you're seeing, I think it's a little abstract right now to be able to give you any specific suggestion. Can you post a sample copy of your db with test data or at least some screenshots to help us understand your setup a bit better?

 

[The_Doc_Man]

Check the query builder wizard, which includes a "FIND UNMATCHED" option. Starting from FIND UNMATCHED, it is not a big stretch to do a DELETE UNMATCHED.

 

[dcumming]

Hi,

@Pat - just trying to simplify the question - there's a dozen different xls in different formats, columns etc. In short, it's too time consuming for me to unpick the 18 update queries i run against it once all the data is in, hence this route.

@Doc man I can do the unmatched query, but i can't work out how to get it to delete - i'm missing something obvious perhaps?

Attached is a sanitised snippet of data.

tblAllData is the master table where all data is held
tblSupplier is the daily updated table

NEWID is the field that is matched, and should be unique (bear in mind i will have 400k items)

The data in tblSupplier is a smaller version of this. What i need to do is remove the items that are no longer in tblSupplier from tblAllData

tblAllData

AllDataID	NEWID
271917​	0000000000000-ABCD035-PH
271933​	0000000000000-ABLP026-PH
273666​	0000000000000-ARCD001-PH
273667​	0000000000000-ARCD002-PH
273668​	0000000000000-ARCD004-PH
273669​	0000000000000-ARCD005-PH
273670​	0000000000000-ARCD009-PH
355509​	0744430522595-0744430522595-PH
355510​	0744430522649-0744430522649-PH
355511​	0744430522724-0744430522724-PH
355512​	0744430522731-0744430522731-PH
355513​	0744430522762-0744430522762-PH
355514​	0744430522779-0744430522779-PH
355515​	0744430522786-0744430522786-PH
355516​	0744430522809-0744430522809-PH

 

[theDBguy]

What i need to do is remove the items that are no longer in tblSupplier from tblAllData

Make sure you have a backup copy before trying this. How about?

DELETE FROM tblAllData WHERE NEWID Not In(SELECT NEWID FROM tblSupplier)

Hope that helps...

 

[dcumming]

@theDBguy looks like that has worked
thank you very much

 

[theDBguy]

@theDBguy looks like that has worked
thank you very much

You're very welcome. Doc, Pat, and I were all happy to assist. Good luck with your project.

 

[dcumming]

Hmm - doesnt seem to like running it across 50k records. Just hangs - cant see the line progressing, so i'll leave it for 10 mins

Might have to reconsider approach - i've got a relatively high spec desktop, i7 32gb ram

 

[theDBguy]

Hmm - doesnt seem to like running it across 50k records. Just hangs - cant see the line progressing, so i'll leave it for 10 mins

View attachment 91356

Might have to reconsider approach - i've got a relatively high spec desktop, i7 32gb ram

How big is your file? I'd like to run some experiments with it. Can you send me a copy? You can take out any sensitive information.

 

[dcumming]

it's pretty big, and will need some time to remove sensitive data - think i've found a simple way round it just by thinking around the problem

 

[theDBguy]

it's pretty big, and will need some time to remove sensitive data - think i've found a simple way round it just by thinking around the problem

Please consider sharing your solution in case it might help others as well.

 

[dcumming]

Good point.

The issue i had was with the unique id for an ever changing set of stock, and often it's available from different suppliers at different prices.
Cat numbers and barcodes appear not to always be unique, so i had to create a reference field that combined the three bits of data there.

I didnt need to create a delete query, just think of a way of creating a unique ID that would always be consistent.

Not actually that hard in the end.