Delete query. Tab

[ImGettingThere]

I'm relatively new to Access. I'm using Access 2003.

I have created a database that has 2 tables,let's call them Table1 & Table2 (top marks for imagination), each with the exact same structure. The database acts as 'back end' and has no forms etc.

Table 1 contains about 200,000 records.Table 2 contains much less than that.

What I want to do is write a query that will delete from Table1 the data that is matched in Table2. The data will match on 2 fields, 'Reference' and 'Period'. The tables are added to the database automatically from the a separate 'Front End' database. It does not define any relationships between the tables.

So for example if Table1 contained the following data

Reference Period
Ref1 1
Ref1 2
Ref2 2
Ref3 1
Ref3 2
Ref4 2

And Table2 contained

Reference Period
Ref1 2
Ref2 2
Ref3 2

Once the query is ran I would expect Table1 to contain the following data

Reference Period
Ref1 1
Ref3 1
Ref4 2

I would greatly appreciate it if someone could show me the SQL statement that would produce these results

Regards

 

[dinger2121]

I think you will have to use a subquery - (NOT tested)

delete from table1 where reference in (select reference from table2) and period in (select period from table2)

I would test if first to make sure it is selecting the right data by using -
select * from table1 where reference in (select reference from table2) and period in (select period from table2)

 

[JerryKlmns]

DELETE T1.*
FROM Table1 As T1 INNER JOIN Table2 As T2 On
(T1.Reference = T2.Reference ) AND
(T1.Period = T2.Period);