I'm too stupid to work this out (1 Viewer)

[bbthedog]

Hello Access Gurus,

I'm quite new to Access, but have nevertheless successfully built a database for a small language school which allows them to track students' details, exam performance, payment records, etc. They asked for a number of reports, all of which were quite easy to create, including one which gives them a list of which students have paid what amounts between a certain date range. The one that's stumping me is that they also a report which shows students who haven't made a payment between a date range. The issue is that the query correctly finds students without a payment within the range, but also returns those who paid on any other date outside the range (multiple times). It's obvious that this is what would happen, but I have a complete mental block about how to do this correctly.

Please see attached for the query/table layout.

If someone could de-stupid me I'd be very grateful!

 

[June7]

I am confused. Students who made payments outside the specified range and still haven't made payments within the range are selected. So why would you not include them? What other criteria must be considered? Inactive students, etc?

 

[plog]

A = All Students
B = Students who made payment within date range
C = Students who didn't make payment within date range

A = B + C --> they either paid in the range or they didn't, all sutdents should be accounted for in either B or C

A is a table, B is a query you successfully built. All you have to do now is subtract B from A and you will get C. So, modify the query you posted like so:

Remove T_Payments and in its place put the query that derives B. Link it to T_Students via StudentID and then change it so that the JOIN shows all records from T_Students regardless if there is a match in B. Then bring down STudentID from B and put NULL in its criteria. Run that and you will have C.

 

[theDBguy]

Hi. Welcome to AWF!

Instead of how you're trying to do it, I would do it like @plog suggested. Take your other query that pulls all the students who paid and compare it to the list of all students to see who are missing.

 

[bbthedog]

Wow - thanks to everyone for the quick replies, much appreciated! I'll try as suggested and report back.

 

[The_Doc_Man]

There is a method that will work if you do a "Divide and Conquer" approach.

First build your list of students that DID pay in the restricted date range.

Then build a query that uses a SUBQUERY (you can look up that keyword) based on the first query. It will look SOMETHING like this, and my names are made up to show function.

SELECT StudentID, other-student-info-fields FROM students-table AS ST WHERE ST.student-enrollment = this-year AND StudentID NOT IN (SELECT SQ.StudentID FROM students-who-paid-query AS SQ ) ;

Since you show us that you are new to Access, I don't expect you to immediately see this, but here is the English of what I just asked:

Show me the students enrolled this year who are NOT in the list of students who have paid during the period of interest.

This technique uses a sub-query. It is an incredibly powerful method. Chose to use the AS clause because you are dealing with two data sources and some of the fields will have the same names so needed to be qualified by source to keep Access from getting confused about WHICH of the two student ID fields you would be referencing.