delete query problem

M

Mary Fran

I am doing the most basic delete query:
DELETE DISTINCTROW tblStudents.*
FROM tblTempStudents INNER JOIN tblStudents ON tblTempStudents.[Student
ID]=tblStudents.[Student ID];
and Access acts like it's running it but when I check the records are still
there - no error message - what am I missing?
 
J

JaRa

Maybe this?

DELETE tblStudents.*
FROM tblStudents INNER JOIN tblTempStudents ON tblStudents.[Student
ID]=tblTempStudents.[Student ID];

- Raoul
 
M

Mary Fran

Then I get "could not delete from specified table" if I do that.

JaRa said:
Maybe this?

DELETE tblStudents.*
FROM tblStudents INNER JOIN tblTempStudents ON tblStudents.[Student
ID]=tblTempStudents.[Student ID];

- Raoul

Mary Fran said:
I am doing the most basic delete query:
DELETE DISTINCTROW tblStudents.*
FROM tblTempStudents INNER JOIN tblStudents ON tblTempStudents.[Student
ID]=tblStudents.[Student ID];
and Access acts like it's running it but when I check the records are still
there - no error message - what am I missing?
Click to expand...
Click to expand...
 
J

JaRa

OK try to include distinctrow again then

is tblTempStudents a query or both physical tables?

- Raoul

Mary Fran said:
Then I get "could not delete from specified table" if I do that.

JaRa said:
Maybe this?

DELETE tblStudents.*
FROM tblStudents INNER JOIN tblTempStudents ON tblStudents.[Student
ID]=tblTempStudents.[Student ID];

- Raoul

Mary Fran said:
I am doing the most basic delete query:
DELETE DISTINCTROW tblStudents.*
FROM tblTempStudents INNER JOIN tblStudents ON tblTempStudents.[Student
ID]=tblStudents.[Student ID];
and Access acts like it's running it but when I check the records are still
there - no error message - what am I missing?
Click to expand...
Click to expand...
Click to expand...
 
B

BDM

Mary,

My experience is that you can never delete rows from a table with a query
that uses a join. A Work around:

Create an extra flag (yes/no) field in the table that you want to delete
rows from

Use your join query to populate this field to "Yes" wherever your criteria
are met

Create a new query and only ad the table that you want to delete rows from

Find your flag field and put "Yes" under criteria

Now run your delete query. This should delete rows whenever "Yes" is
present under criteria
 
M

Mary Fran

Both are tables. So you really have to go to all that trouble to delete
records that match fields in joined tables? Shouldn't there be an easier way?
Thanks for all your help!

BDM said:
Mary,

My experience is that you can never delete rows from a table with a query
that uses a join. A Work around:

Create an extra flag (yes/no) field in the table that you want to delete
rows from

Use your join query to populate this field to "Yes" wherever your criteria
are met

Create a new query and only ad the table that you want to delete rows from

Find your flag field and put "Yes" under criteria

Now run your delete query. This should delete rows whenever "Yes" is
present under criteria

Mary Fran said:
I am doing the most basic delete query:
DELETE DISTINCTROW tblStudents.*
FROM tblTempStudents INNER JOIN tblStudents ON tblTempStudents.[Student
ID]=tblStudents.[Student ID];
and Access acts like it's running it but when I check the records are still
there - no error message - what am I missing?
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Nothing that I can see. That should work assuming that there is a match between
the two tables on the Student ID.

You can try

DELETE DISTINCTROW TblStudents.*
FROM TblStudents
WHERE TblStudents.[Student ID] IN
(SELECT tblTempStudents.[Student ID]
FROM tblTempStudents)

If you just make your Delete query a SELECT query, does it return records?
 
B

BDM

I know what you're saying. I've just run in to this myself. My method with
the update flag has to be done if you're using query design view. There may
be some other way with the SQL view.

I think the reason it doesn't work with a join, is that you would be
deleting the records that you're basing the join on, so you have to do the
update flag first. Then you can delete based on that flag as a second step
once you're no longer joining.
 
Top