Delete Records based on Data in separate table

[GlennG]

Hello,

I receive a listing of sales results from Marketing which I import into Access. There are many closed stores in the file I import and would like to remove them. All the closed stores are listed in a separate table. This is what I tried but to no avail..... Can someone help me ? Is this feasible ?
Thanks for your help...... ray:

mYString = "DELETE * FROM INP_STORES " & _
"WHERE (INP_STORES.STORES = STORES_CLOSED.STORES)"
dbs.Execute mYSTRING

Sales figures are listed in a table called INP_STORES
Closed strores are listed in table called STORES_CLOSED

 

[Norie]

Why do you want to remove them?

Can't you just use criteria in queries to exclude them?

Also why have a seperate table for closed stores?

You could add a Yes/No field to the main table that indicates whether a store is open/closed.

 

[GlennG]

Thanks for the reply,

I'm new to Access and am not too sure how to go about it. Besides, given that I'm working in a process that's already in place, I don't want to get too involved in changing this or that....

 

[tonnic]

Hi,

try this SQL-Statement, it also should work on other Databases like DB2.

mYString = "DELETE a.* FROM INP_STORES as a " & _
"WHERE exists (SELECT distinct stores from stores_closed as b " & _
"WHERE a.stores=b.stores);"

 

[GlennG]

THank you for your responses ..... I found a SQL statement that does the trick:

DELETE Leavetbl.*
FROM Leavetbl
INNER JOIN Holidays
ON Leavetbl.tempdate = Holidays.holidate
WHERE Leavetbl.tempdate = Holidays.holidate

I got this snippet from....
http://www.tek-tips.com/viewthread.cfm?qid=955198

Many thanks to all....