Create query with grouping of data

[Woelfe]

I was not sure how to correctly phrase what I am trying to accomplish in the title. I have a table of data for students that includes the following fields:

UniqueID (primary key)
StudentID
Name
Course
Grade
LastDateAttended

In this table, a student will be listed each time for each course they enrolled in. So if they are in 3 classes, there will be three records for them.

What I want to do is return query results by looking at the group of courses for the student and returning all records for each class if the attend date on any class is not greater than May 7, 2017.

It is an all or nothing sort of situation. So if they attended three courses, and none of these courses has LastDateAttended greater than 7 May, I want all three lines in the query results. If any one of the classes is greater than 7 May, I want none of the classes in the result.

I hope that makes sense. I have tried to think of a way of doing it, but so far my Access skills have been limited to general query types, the easy peasy kind. I'm suspecting I may need to do some kind of VB code or SQL? I'm just not sure how to go about it.

Any thoughts on it are appreciated.

Thank you!

 

[Joe4]

You can do this pretty easily in a series of two queries.

First Query
Make an Aggregate Query where you just return the StudentID field and the LastDateAttended field.
In making it an Aggregate (Totals) Query, group on StudentID and take the Max value of LastDateAttended.
Then add Criteria under the Date field to only select records after May 7, i.e.

>= DateSerial(2017,5,7)

Second Query
Now, simply do a Match query between your first query and your original data table, joining on the StudentID field.

That should return what you want, no VBA required!

 

[Woelfe]

Joe4, thank you for the input!

Quick question to verify, you have the aggregate query selecting records that are greater than or equal to 7 May, I think I want the reverse, less than 8 May? This gives me everyone whose last date attended was not at least 8 May. We want to eliminate those students who "earned" their F's making it to the end of the semester.

 

[Joe4]

Sorry. misread that.

Just flip the ">=" in the criteria to "<=" and I think that will give you what you want.

 

[Woelfe]

Got it, no worries. Ok, I made the aggregate query, looks like I am getting the right info.

As for the 2nd part, I was supposed to create a relationship between the original table and First Query, correct?

I'm not real sure how to make the matching query though. I saw an option for unmatched in the query wizard, but not a matching.

 

[Woelfe]

Ok, I think I figured it out. I created a query in design view, and I updated the relationship link to be only where the records between the table and query are equal, and included all fields I needed from original table. I get far less records now. I'll have to comb through and make sure data looks ok. Does that sound right?

 

[Joe4]

Just create a brand new query, add the original table and the first query, then drag a line from the StudentID field in your table to the StudentID field in your query.
That is all you need to do to create the relationship in the query.

Then select whatever fields you would like to return in the query.

 

[Woelfe]

So I did not need to change the type of relationship, just drag and drop. I removed and reset it, and got same data count. This looks good, I think I followed your steps correctly.

Thank you so much for the quick reply and easy solution. This was pretty clever and saved me a lot of time manually deleting rows, lol. You are the man!

 

[Joe4]

No problem! Glad I was able to help.

It can be done in one single query, if you know how to write SQL code and nest queries, but that is not necessary. It essentially does the same thing we just did here.
There really isn't any issue with having one query depend on another.