Macro to keep 15 specific records and delete 1000 other rows.

[Sam]

Hi Gang,

I work inventory control for a company in Texas and have to check stock
levels for around 15 parts daily. The download for these parts contain
an additional 1000 other rows of part numbers I dont need. I have used
macros in the past that delete rows based on specific criteria but how
do I do the reverse? Essentially write code that says "keep these 15
specific part numbers" but delete everything else that isnt them.

Your help is greatly appreciated.

Sam

 

[Steve Schapel]

Sam,

Well, this is not really a job for a macro. Or for "write code" either,
which is a different thing again. This is a job for a Delete Query.

Make a table that has a PartNumber field, and list in there the 15 part
numbers that you are interested in. Let's say you call this table KeyParts.

Make a query based on the main table of downloaded data. Make this into
a Delete Query (in design view of the query, select Delete Query from
the Query menu). Add the PartNumber field from this query separately to
the query design grid, and in the Criteria of this column put the
equivalent of this...
Not In (SELECT [PartNumber] FROM KeyParts)
Run the query (on a backup copy ) by clicking the button with the
red [!] icon on the toolbar, it should do what you want.
The SQL view of this query should look something like this...
DELETE PartsTable.*
FROM PartsTable
WHERE (PartsTable.[PartNumber] Not In (SELECT [PartNumber] FROM
KeyParts));

If you want to automate this via a macro, you would use the OpenQuery
action.