UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> delete query using linked tables    
 
   
pandorah
post Dec 3 2009, 10:42 AM
Post #1

UtterAccess Member
Posts: 43
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!!!
Go to the top of the page
 
+
Jack Cowley
post Dec 3 2009, 03:20 PM
Post #2

Retired Moderator
Posts: 37,716
From: The San Francisco Bay Area
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
Go to the top of the page
 
+
vtd
post Dec 3 2009, 05:03 PM
Post #3

Retired Moderator
Posts: 19,667
Try the SQL:
CODE
DELETE [color="red"]DISTINCTROW[/color] Table1.*
FROM Table1 INNER JOIN Table2
  ON Table1.EmpID = Table2.[ID Number]
Go to the top of the page
 
+
pandorah
post Dec 4 2009, 08:10 AM
Post #4

UtterAccess Member
Posts: 43
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
Go to the top of the page
 
+
« Next Oldest · Access Forms · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th May 2013 - 09:14 AM