IIF statement to count multiple records instances in a query

[rmcafee]

Try this...

SELECT TableName.StudentID, TableName.MarkDate, Count(TableName_1.Tardy) AS CountOfTardy
FROM TableName INNER JOIN TableName AS TableName_1 ON TableName.StudentID = TableName_1.StudentID
GROUP BY TableName.StudentID, TableName.MarkDate
HAVING (((TableName.MarkDate)=Date()) AND ((Count(TableName.Tardy))>0) AND ((Count(TableName_1.Tardy))=8));

The above uses a self join
It doesn't account for dates within a semester as I'm unclear how you record the semester value

Will check it out, thanks.
Going to create some dummy data for people to look at first. Will be tonight or tomorrow. USA EST

 

[rmcafee]

Here is a table with dummy data.

 

[arnelgp]

see the queries I made if this is what you need.

 

[rmcafee]

Here is a database with some real dates that will work for today.

I have one query that will show me who reached 7 tardies (late to class) today included too.

Looking for a way to find who had their 7th AND 8th tardies on today's date.

 

[rmcafee]

see the queries I made if this is what you need.

I think you're looking at and using the wrong field in the queries to get totals/sums.
The original table has each single tardy recorded, the date, and the class period [bell period] it occurred. The tardy is in the [Period Absence] field. "TDY" is the code for the tardy, on that day, and period. The number of tardy codes is what has to be counted over the course of the entire semester.
The [bell period] field is only the class period in which the tardy was recorded. It is only a label for the period and shouldn't be counted as a number to total up.
See my recent database I uploaded and this should make more sense.

thanks

 

[arnelgp]

you just make a copy of "7 Tdy Today" query.
rename the copy.
the criteria instead of 7, will be:

Between 7 and 8

 

[Mark_]

Let me see if I know what you are really asking;
What is the DATE of a given student's 7th tardy, if there is one within a given date range?

You care that their 7th was on 03/03/19, but you don't worry if they also have their 8th on that date, correct?

 

[rmcafee]

you just make a copy of "7 Tdy Today" query.
rename the copy.
the criteria instead of 7, will be:

Between 7 and 8

I think that will actually work well enough for what I need.
I would like to have a column that somehow shows Tardy 7 and Tardy 8, but this will work. It will catch those students reached and passed the 7th Tardy threshold on today's date.
Thanks - so simple.

Created another column and added this, just to show which students got both on the same day. 7and8: IIf([CountOfPeriod Absence] Like "8","7,8",IIf([CountOfPeriod Absence] Like "7","7"))

 

[arnelgp]

if you need it in separate columns, [Tardy 7] and [Tardy 8], you will be needing a Criteria like this.

 

[Pat Hartman]

You really need a date range. Just looking at "today" only works if you are somehow eliminating all data prior to some starting period.

What if someone has 9 tardies? I don't understand this desire to find 7 tardies plus one. Why would you not simply look for > 7 and pull all those records?

As I said, if you only want to count all tardies on a single day as 1, then you need to first summarize the data so you pull only ONE record for each student that has ANY tardies during the period. Then your second query which uses the first as its "table" would then count the number of days which have any tardies and you can again look for GREATER THAN 7. It doesn't make any sense to look for an exact number for anything like this.

 

[The_Doc_Man]

I begin to suspect a descriptive problem.

You can get up to 7 tardies on any day. Got that. But they accumulate. You cannot get 8 tardies in a day. Takes at least two days to do that.

SO... if someone has six tardies by close of school yesterday and gets two tardies today, the 7th tardy will register today. The 8th (and subsequent tardies) will still accumulate until your next threshold for disciplinary action. Does that reasonably describe the way you are trying to work?

 

[rmcafee]

I begin to suspect a descriptive problem.

You can get up to 7 tardies on any day. Got that. But they accumulate. You cannot get 8 tardies in a day. Takes at least two days to do that.

SO... if someone has six tardies by close of school yesterday and gets two tardies today, the 7th tardy will register today. The 8th (and subsequent tardies) will still accumulate until your next threshold for disciplinary action. Does that reasonably describe the way you are trying to work?

Yes, you're right, they keep accumulating towards the next threshold. You're seeing the exceptions that make all this difficult. Now that I have this query it will identify the student you described because it checks today's date with the thresholds their hitting. You're saying they get both the next day AFTER they get their 6th, so the query would still catch these students.
The way we're going to handle the next threshold (9 tardies) is to rely on a separate query to identify students hitting the 9th tardy threshold.
As an alternative, I may expand this initial query (7th threshold) to also include the students that hit the 7th, 8th, and 9th on the same day. I could even expand it all the way up to the 13th if I wanted. They would have to get all on the same day to have hit additional thresholds and show in the results of the query. Luckily, that's very few students.
I'm constructing reports from these queries that puts the student with each threshold they hit, so that everything will be on one page for the clerks to track.

 

[MrHans]

My understanding from this long post is that you need a Transaction type structure.
Each tardy is registered and counts for +1.
Each discipline is registered and counts for -7.

That will easily show you what students have reached the threshold of 7, regardless of the day and when they received their discipline the count is reset.

With this structure you should also be able to easily take semesters into account.

 

[rmcafee]

You really need a date range. Just looking at "today" only works if you are somehow eliminating all data prior to some starting period.

What if someone has 9 tardies? I don't understand this desire to find 7 tardies plus one. Why would you not simply look for > 7 and pull all those records?

As I said, if you only want to count all tardies on a single day as 1, then you need to first summarize the data so you pull only ONE record for each student that has ANY tardies during the period. Then your second query which uses the first as its "table" would then count the number of days which have any tardies and you can again look for GREATER than 7. It doesn't make any sense to look for an exact number for anything like this.

You really need a date range. Just looking at "today" only works if you are somehow eliminating all data prior to some starting period.
Yes, when I pull the data from our student information system (SIS), it automatically pulls tardies from the first day of school. So it sort of has a built in date range.

What if someone has 9 tardies? I don't understand this desire to find 7 tardies plus one. Why would you not simply look for > 7 and pull all those records?

Yeah, I thought of that and may do that. Actually posted in a response a few minutes ago. We do check for additional thresholds, like 9, but we have to know WHEN they hit that threshold. Since they can only get a total of 7 in one day the highest threshold would be 13 for one day. I also have to have an easy way for clerks to see the tardies on a report. That's the only reason I was looking to actually display the numbers (like 8) in the query results. When administrators talk to the students, they can point out to them (and parents) how close they are to getting another disciplinary action. It will be easy to see on a report.


As I said, if you only want to count all tardies on a single day as 1, then you need to first summarize the data so you pull only ONE record for each student that has ANY tardies during the period. Then your second query which uses the first as its "table" would then count the number of days which have any tardies and you can again look for GREATER THAN 7. It doesn't make any sense to look for an exact number for anything like this.[/QUOTE]

Maybe I misspoke, we don't count all tardies on a single day as 1. Each tardy to a class is counted as one. If a student is late to 1st, 2nd and 3rd period, that's three tardies and it shows as three separate records when I pull it from out student information system. It's just as critical to know WHEN a student reaches a threshold as it is WHAT threshold they've reached.
That's why they have to receive a tardy on the day it's checked AND it has increased their accumulated tardies, so that they've reached one of the thresholds.
I have to mention, this process is run every day at the end of the day. So the student must have received a tardy on today. That's the first criteria. The second criteria is that they reach a threshold. First one is 7. For every threshold they receive a different disciplinary action. The query looks at today's date, who received a tardy, and then determines if the student reached one of the tardy thresholds.

I have additional backups if there is a reason it's not run each day.

 

[rmcafee]

My understanding from this long post is that you need a Transaction type structure.
Each tardy is registered and counts for +1.
Each discipline is registered and counts for -7.

That will easily show you what students have reached the threshold of 7, regardless of the day and when they received their discipline the count is reset.

With this structure you should also be able to easily take semesters into account.

See my post below this one, it may help to clarify things.
One note though, student's discipline is not "reset" after they reach 7, they continue to accumulate and can reach additional thresholds and additional disciplinary actions. So I have to know WHEN they reach each threshold, that's why this is checked every day.
I can easily adjust my data pull from our SIS to account for semester change or any date range.

 

[Mark_]

We are all looking at the wrong problem.

@OP, you have some record that shows a student has been dealt with for their tardy issue, correct? If not, this is something you will find you need rather quickly.

Once you know WHEN their tardy issue has been addressed, you only need worry about those who've reached the next break point. You know they were tardy today as you are running only on that days data. When you do, you can use a DCount to find out their total number of tardies. You can see if this is in the same range as their last talking to. If it is, no action need be taken. Otherwise you know they do need to be talked to.

How this will work.

Mary was tardy 6 times yesterday.
Mary was tardy 4 times today.
Today, you do a count and see she has 10 tardies. This calls for X.
Tomorrow, she is tardy 3 times so she has 13 tardies. This calls for Y. Tomorrow she is addressed, with her parents by the schools administration. You make a records that says she was talked to for her 13 tardies.

A month from now, she is tardy only once. When you check, you see she has 14 tardies total, but she was talked to for having 13. Your are comparing your running total to a "last action taken".

This also means that as tardies accumulate you continue to complain about ones not addressed. This means your 7th yesterday will still show up when you have your 8th today, so long as it hasn't been addressed yet.

Does this make a bit more sense than just trying to find out on which day an arbitrary tardy occurs?

 

[Pat Hartman]

Assumptions:
1. You only count complete days.
2. You use a date range.
3. You store a record of when previous actions were taken.

It would be ever so helpful if you would post the actual school policy rather than us having to guess what it might be. This seems way harder than it needs to be.

 

[rmcafee]

We are all looking at the wrong problem.

@OP, you have some record that shows a student has been dealt with for their tardy issue, correct? If not, this is something you will find you need rather quickly.

Once you know WHEN their tardy issue has been addressed, you only need worry about those who've reached the next break point. You know they were tardy today as you are running only on that days data. When you do, you can use a DCount to find out their total number of tardies. You can see if this is in the same range as their last talking to. If it is, no action need be taken. Otherwise you know they do need to be talked to.

How this will work.

Mary was tardy 6 times yesterday.
Mary was tardy 4 times today.
Today, you do a count and see she has 10 tardies. This calls for X.
Tomorrow, she is tardy 3 times so she has 13 tardies. This calls for Y. Tomorrow she is addressed, with her parents by the schools administration. You make a records that says she was talked to for her 13 tardies.

A month from now, she is tardy only once. When you check, you see she has 14 tardies total, but she was talked to for having 13. Your are comparing your running total to a "last action taken".

This also means that as tardies accumulate you continue to complain about ones not addressed. This means your 7th yesterday will still show up when you have your 8th today, so long as it hasn't been addressed yet.

Does this make a bit more sense than just trying to find out on which day an arbitrary tardy occurs?

You're post makes perfect sense.
However, from our perspective and what we do, what I'm doing right now makes sense too. It's not arbitrary, it's when the tardy reaches a specified threshold on a specific day. We have to identify quickly and call the student in.
We have to catch these (threshold levels) as they occur. 8th tardy doesn't matter because it doesn't reach a threshold for disciplinary action. 14th doesn't matter until the student reaches the next threshold. The only reason I included the 8th tardy originally was if they reached 7 and 8 on the same day. If I only put in 7, then students who got 7 and 8 on the same day, would've been skipped. Not now, with the query I have.
Man, I appreciate the thought everyone is putting into this!!! We actually have a separate procedure for the disciplinary actions. Those disciplinary actions have to be entered into a separate system and tracked there. My charge was to simply identify the students who reach each threshold, on each day.
HOWEVER, I may come back and explore this more based on these great posts. you guys are thinking ahead of me and that's appreciated. I hope the people who responded will check back. I've learned a lot just from the one post and it will help me with other tasks. Forum is awesome!!!
Base on what's been posted I may explore a way to track the disciplinary action from each threshold reached. This would be awesome!!

 

[rmcafee]

Assumptions:
1. You only count complete days.
2. You use a date range.
3. You store a record of when previous actions were taken.

It would be ever so helpful if you would post the actual school policy rather than us having to guess what it might be. This seems way harder than it needs to be.

1. You only count complete days.
No, each tardy stands on its own. Each tardy is a counted as a separate entry from the SIS. A student can receive several tardies on any given day. See my database example and you will see how tardies are pulled from the SIS (Student Information System)

2. You use a date range.
I don't have to use a date range. When I pull the data from the SIS, the date range is already taken care of. I query the SIS and my query (separate query language used to pull date from the SIS) is written so that it takes all tardies from the first day of school up to the current day.

3. You store a record of when previous actions were taken.

Previous disciplinary actions don't matter, I have to track when they reach each threshold level. I haven't been charged with doing anything with tracking whether or not students comply with the disciplinary actions. HOWEVER, that may be something to explore based on these great posts.
The school uses this data to assign disciplinary action, which has to be entered into another system. Disciplinary actions can get serious, so it has to be entered in the SIS. I'm just giving them the tool to identify these students.

It would be ever so helpful if you would post the actual school policy rather than us having to guess what it might be. This seems way harder than it needs to be.[/QUOTE]

I agree, I was just trying to answer all the question posted to me.
The policy is....at each threshold, 7, 9, 11, 13, 15, students receive a specific disciplinary action based on the threshold they've reached. After a certain level (probably 17), the detentions, suspensions, etc. end and they have to report directly to an Administrator, it gets serious then.
I haven't been charged with a procedure to track the student's discipline action or if the student actually complied..... that may come though and I would like to come back here for help. Some posts have already explored possible scenarios.

 

[Mark_]

Since it sounds like you don't have the other piece that would normally be needed in this to work easily, here is another approach.

Start by making a query on StudentID, DateTardy, PeriodTardy
This query is used by a function that you pass the student ID and number of tardies to.
The function would open the query, set the Where=StudentID. It would use your rules to calculate the difference between the passed number of tardies and the threshold for discipline.

IF the difference is 0, it returns TRUE
If there is a difference, it would open your query (query's order by would be TardyDate, TardyPeriod DESC) with a SELECT TOP and it would use the difference to tell how many records to fetch.

You then find the oldest record. If it is for today's DATE, then it returns TRUE, else it returns FALSE.

This means that for Mary, if its her 7th tardy the function immediately returns TRUE.
If it is her 8th, would SELECT TOP 2 StudentID, TardyDate, TardyPeriod FROM TblTARDY WHERE TblTardy.StudentID = PassedStudentID ORDER BY TardyDate, TardyPeriod DESC

You would then do a movefirst / movelast on the query. If the last date in your returned recordset is today, you return TRUE, else you return FALSE. This answers your "Was the 7th (or what ever) tardy TODAY?"

Now this would all drive a query that has
StudentName, StudentID, TotalTardies: DCount(Stuff for all of the tardies this student has had), YourCustomFunction(StudentID, TotalTardies)

So you'd end up with
Mary - IDNumber - 10 - TRUE for today, but
Mary - IDNumber - 6 - FALSE for yesterday.

It also means that if yesterday Mary had 7 tardies and she was tardy once today, you'd get
Mary - IDNumber - 8 - FALSE for today.
If Mary had 6 tardies yesterday but had 2 today, you'd see
Mary - IDNumber - 8 - TRUE for today.

Does this make sense?