Delete all one side records with no many side records

[Sam Davis]

Hi and thanks for reading,

Working in VBA - this is part of an automated import function...

I have two tables Students and Reports. Most students have many reports, but
some don't have any reports. I wish to delete the ones with no reports.

This SELECT query shows all the records I wish to DELETE

SELECT Students.*
FROM Students LEFT JOIN Reports ON Students.StudentID = Reports.StudentID
WHERE Reports.StudentID Is Null;

Change to:

DELETE Students.*
FROM Students LEFT JOIN Reports ON Students.StudentID = Reports.StudentID
WHERE Reports.StudentID Is Null;

And Access gives the error "Could not delete from specified tables"

What am I doing wrong? I'm sure I've done this before.

Regards, Sam

 

[Allen Browne]

Try a subquery:
http://allenbrowne.com/subquery-01.html#DeleteUnmatched

 

[Sam Davis]

Thanks Allen,

Of course, now I remember...

Here's the SQL I used for anyone reading in the future...

DELETE FROM Students
WHERE NOT EXISTS
(SELECT Reports.StudentID
FROM Reports WHERE Reports.StudentID=Students.StudentID);

Regards, Sam

 

[Sam Davis]

The DELETE query is part of an import function.

This is a school reports (to parents) application and the students, classes,
reports (which link students to classes) and teachers are imported from the
schools timetable software application. They need to import all year 7
students, classes and reports in say week 1. Then in say week 3 they'll
import year 8 data, then in week 6 year 10 data. This is so teachers can
write reports for different year levels over time.

The timetable files contain the whole school (years 7 to 12 in this case).
Now in some cases a student in say year 9 can be enrolled in a year 10 or
even year 11 class (cleaver kid!). So I import all the students, import just
the classes for the required year (or years) and also all the reports for
these classes. I then DELETE any kids who have no reports.

Hope that makes some sense!

Sam

Just curious, why are you deleting the records of the students?

Thanks Allen,

Of course, now I remember...

Here's the SQL I used for anyone reading in the future...

DELETE FROM Students
WHERE NOT EXISTS
(SELECT Reports.StudentID
FROM Reports WHERE Reports.StudentID=Students.StudentID);

Regards, Sam

Try a subquery:
http://allenbrowne.com/subquery-01.html#DeleteUnmatched

[quoted text clipped - 24 lines]

Regards, Sam

 

[Krzysztof Naworyta]

Sam Davis wrote:


(...)
| DELETE Students.*
| FROM Students LEFT JOIN Reports ON Students.StudentID =
| Reports.StudentID WHERE Reports.StudentID Is Null;
|
| And Access gives the error "Could not delete from specified tables"
|
| What am I doing wrong? I'm sure I've done this before.

Your Students table has no primary key - that's the reason!
But you still can run your query:

DELETE DISTINCTROW Students.*
FROM Students LEFT JOIN Reports
ON Students.StudentID = Reports.StudentID
WHERE Reports.StudentID Is Null