Delete query based on values in another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I have a table of people who have signed up for an event. In a second
table, I have a list of the people who signed up but did not participate.
What I want to do is delete the nonparticipants from the signup table so I
have less records to deal with.

I have tried the following but it doesn't work.

DELETE [Copy of Runner].*, [Copy of Runner].RunnerID
FROM [Copy of Runner], tblNonParticipatingRunners
WHERE ((([Copy of Runner].RunnerID)=[tblNonParticipatingRunners]![RunnerID]));


In the grid, I used the RunnerID field from the nonparticipants table as my
criteria but it didn't work.

Any ideas???

Thanks!
 
Hi there,

I have a table of people who have signed up for an event. In a second
table, I have a list of the people who signed up but did not participate.
What I want to do is delete the nonparticipants from the signup table so I
have less records to deal with.

I have tried the following but it doesn't work.
Click to expand...

Use a JOIN rather than a Cartesian join with a WHERE clause:

DELETE [Copy of Runner].*
FROM [Copy of Runner] INNER JOIN tblNonParticipatingRunners
ON [Copy of Runner].RunnerID=[tblNonParticipatingRunners]![RunnerID];

Note that this will only work if RunnerID is the Primary Key of [Copy Of
Runner], or at least has a unique index.

John W. Vinson [MVP]
 
I have set Runner ID as the primary key and tried this but still no luck.
All I get is an error message saying "Could not delete from specified
tables". Any more suggestions?

Thanks for all your help!

J
--
www.brightfuture.ca/bright
My email address can be found on my site.


John W. Vinson said:
Hi there,

I have a table of people who have signed up for an event. In a second
table, I have a list of the people who signed up but did not participate.
What I want to do is delete the nonparticipants from the signup table so I
have less records to deal with.

I have tried the following but it doesn't work.
Click to expand...

Use a JOIN rather than a Cartesian join with a WHERE clause:

DELETE [Copy of Runner].*
FROM [Copy of Runner] INNER JOIN tblNonParticipatingRunners
ON [Copy of Runner].RunnerID=[tblNonParticipatingRunners]![RunnerID];

Note that this will only work if RunnerID is the Primary Key of [Copy Of
Runner], or at least has a unique index.

John W. Vinson [MVP]
Click to expand...
 
I have set Runner ID as the primary key and tried this but still no luck.
All I get is an error message saying "Could not delete from specified
tables". Any more suggestions?
Click to expand...

Please copy and paste the SQL of the query you're using, and let us know the
primary key of each table; whether there is a relationship established; and
verify that these are both local Tables, rather than queries or linked to some
other system.

John W. Vinson [MVP]
 
Try the following

(Probably won't work)
DELETE DISTINCTROW [Copy of Runner].RunnerID
FROM [Copy of Runner], tblNonParticipatingRunners
WHERE ((([Copy of
Runner].RunnerID)=[tblNonParticipatingRunners]![RunnerID]));

(Should work)
DELETE DISTINCTROW [Copy of Runner].RunnerID
FROM [Copy of Runner] INNER JOIN tblNonParticipatingRunners
ON [Copy of Runner].RunnerID=[tblNonParticipatingRunners]![RunnerID]

(Will Work)
DELETE [Copy of Runner].RunnerID
FROM [Copy of Runner]
WHERE RunnerID IN (SELECT RunnerID FROM tblNonParticipatingRunners)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top