Count records since date

[sotssax]

Hello access experts,



I work in a school and I am trying to create an access database that will assist with recording and tracking our dress code and gum violations. What I have created is below:

Student table -student name and grade
Violation table - violation type, violation date, action date, action

The action is the consequence for repeated violations; detention, in school suspension, etc.

What I need is a way to create a query to see how many violations have occurred after an action date.

For example, if a student received a detention on 2/22/12 and received 3 violations since then, I would like to query to figure out the most recent action date and count how many violations occurred since then.

Any ideas on how to set this up?

[aytee111]

Query 1 - group by student and violation type, max action date
Query 2 - group by student and violation type, count records where violation date > max action date from query 1

Ideally the action fields should be on a separate table as it seems they are not always populated and do not logically fit with the other fields on that table - there isn't an action for every violation.

[sotssax]

Thank you so much!! That sounds like it will work. I can't try it until Monday because a power outage shutdown our remote server. I was toying with separating the action info into a separate table, now I definitely will. One question in the meantime, will it return records for students that do not have any action dates but do have violations (they haven't earned an action yet)?

[aytee111]

In the second query when you join the table to query 1, set the join type to include all records from the table with only those from query 1 that match - that will show all violations regardless of whether there is an action or not. In case you don't know how to do this, click on the join and then right-click and it will give you options to choose from.

[sotssax]

Ok, so I changed my setup. I know have the actions in one table called actions and the violations in another table called violations.

Here are my tables:
Students
Actions
Violations
ActionLog
ViolationLog

I created query 1 which includes:
[ActionLog].[StudentID] - "Group by"
[ActionLog].[Offense] - "Group by"
[ActionLog].[Action] - "Group by"
[ActionLog].[ActionDate] - "Max"

I then created query 2 which includes:
[ViolationLog].[StudentID] - "Group by"
[Students].[FileAs] - "Group by"
[ViolationLog].[Offense] - "Group by"
[ViolationLog].[ViolationDate] - "Group by" and criteria of ">[MaxOfActionDate]"
[Query 1].[MaxOfActionDate] - "Group by"

All this works except for two things:
1. It does not show the students who do not have an Action Date but do have Violations
2. I can not figure out how to have query 2 count how many records with the have a violation date greater than the MaxOfActionDate

[aytee111]

1. Change your join type (see previous post)
2. Your ViolationDate should be "Count" not "Group By".

It's hard to figure this out without a database! If this is still causing you problems, you can attach it.

[sotssax]

I couldn't get the join to show additional records and when I changed the ViolationDate from "Group by" to "Count" it would not show any records. Sample database is attached.

[aytee111]

See attached

[sotssax]

Thank you so much! That is what I needed. Now if I can just figure out how to get the "FileAs" name to show up on Query 2 for those students that do not yet have an action date. The studentID works, I would just have to go back to the Students table to match the ID with the student name. Any ideas?

[aytee111]

You can add it to query 1 or join in the Students table into query 2, either option would work.

Make sure that you understand what I did so that you can do it next time.

[sotssax]

Yes, understand what you did. I ended up having to create a third query that pulled in the students table and query 2. I was have trouble with adding the students table to query 2 because of something about outer joins and performing inner joins separately. However, the third query did the trick.

Thank you so much for your help. I am teaching myself how to use access. I am generally good with learning programming. I just get stuck once in a while. Thank you again for all your help.