Delete Query Help Needed

[tushar05]

Hi,
I am new to Access. Can someone please me with following query.
It compares data from two tables and deletes entry from table 1 for entries in table 2.
I have two tables with one field each.

Sample data-
Table 1:Field1-
A
B
C
B

Table 2:Field1-
B

Expected Query Result
A
C
B



Thanks in advance for help.
Tushar

 

[Mr. B]

Using the data you provided, you cannot expect to be able to delete only one record with a "B" in table2 if you are only wanting to use the value from table2.

You can delete records from Table1 using a value from Table2 as the criteria, but in the case of the data you provided, you would get :
A
C
as the remaining records in Table1.

If this is what you are looking for, the following sql statement will do what you want:

SELECT Table1.Field1 FROM Table1
WHERE (((Table1.Field1) In (SELECT Table2.Field1
FROM Table2;)));

 

[tushar05]

Thanks Mr. B. I would want to keep B, I dont want to delete all records. If table 1 has 3 Bs and table 2 has 1, I want to delete only one and keep one.


Regards,
Tushar

 

[Mr. B]

I have two tables with one field each.

I you only have one field in each table then you will not be able to restrict which "B" record is deleted. All records with a "B" will look the same to a query.

Try adding an "AutoNumber" record ID field to your table. Then you can use that RecordID value to identify one single record and then be able to delete that specific record.