delete query problem

[Guest]

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?

 

[Guest]

Maybe this?

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

- Raoul

 

[Guest]

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

Maybe this?

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

- Raoul

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?

 

[Guest]

OK try to include distinctrow again then

is tblTempStudents a query or both physical tables?

- Raoul

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

Maybe this?

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

- Raoul

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?

 

[Guest]

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

 

[Guest]

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!

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

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?

 

[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?

 

[Van T. Dinh]

Try:

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

See also the MSKB:

http://support.microsoft.com/?id=207761

 

[Guest]

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.