Complicated Append Query

[skwilliams]

I have a master table in my database (tblOOS). This list currently gets replaced each week from a .csv file that contains the latest list of stocks, descriptions, etc. including additions and deletions.

Most of the descriptions really need to be updated for accuracy. My problem is I can't edit the descriptions in my master table because they will be replaced the following Monday with the new list.

Is it possible to just append new stocks from the list to the master table and delete stocks from the master table not on the new list but leave the good records untouched?

Does this make sense?

 

[vbaInet]

Is it possible to just append new stocks from the list to the master table ...

This bit I follow and yes you could by using the Find Unmatched Query Wizard and creating an Append Query from that one.

... and delete stocks from the master table not on the new list but leave the good records untouched?

Again, another Find Unmatched Query Wizard solution and a Delete Query based on that. But with the highlighted bit, what are the "good records"?

 

[skwilliams]

By good records, I mean the records that are in both tables. I got the append query to work. I also have a query for the records to be deleted. Not sure how to reference these in a delete query though.

 

[vbaInet]

I thought you said you got the query for deleting the records to work? Explain a bit more.

 

[skwilliams]

The append query to add records is working. I can't figure out the delete query though.

 

[vbaInet]

What you need is a subquery.

You're deleting based on the ID right?

 

[skwilliams]

Deleting based on stock number (each is unique).

 

[vbaInet]

Ok, here's how:

1. Create a query and include the Master table in that.
2. Change the query to a Delete query in design view
3. Under the criteria for Stock Number put something like this:

(SELECT [COLOR=Red]unmatchedQuery[/COLOR].StockNumber FROM [COLOR=Red]unmatchedQuery[/COLOR] WHERE [COLOR=Red]unmatchedQuery[/COLOR].StockNumber = [B]MasterTableName[/B][COLOR=Blue][/COLOR].StockNumber)

I've colour coded it so you don't get things mixed up. Try it on a backup copy first or copy of your table.

 

[skwilliams]

That's what I need.

Thanks.

 

[vbaInet]

You're welcome.