Full Version: delete query using linked tables
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
pandorah
Dec 3 2009, 10:42 AM
Hi
I have two tables linked in a delete query by id number:
Main table-Table1.EmpID
Secondary table-Table2.ID Number

I am trying to delete any records that show in Table2.ID Number from Table1.

How can I do this?

Thanks!!!
Jack Cowley
Dec 3 2009, 03:20 PM
If your tables are set up like this:

Table1
EmpID (Autonumber)
...other fields...

Table2
ID (Autonumber)
EmpID (FK to Table1)
...other fields..

In the relationship window click on the link between the tables and check the Cascading Delete checkbox. Now deleting a record in the Parent table (Table1) will delete the Child records in Table2... No need to join the tables in query...

hth,
Jack
vtd
Dec 3 2009, 05:03 PM
Try the SQL:
CODE
DELETE [color="red"]DISTINCTROW[/color] Table1.*
FROM Table1 INNER JOIN Table2
  ON Table1.EmpID = Table2.[ID Number]
pandorah
Dec 4 2009, 08:10 AM
Hi Jack...Neither field is an Autonumber. They are just fields in the tables.

Hi Van T. Dinh...when I try your solution I get the message:
Run-time error 3128 = Specify the table containing the records you want to delete.

Any ideas??

Thank you both
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.