IIF statement to count multiple records instances in a query (1 Viewer)

rmcafee · Aug 23, 2019

Title may not adequately describe what I'm trying to do.
I work for a school and I'm developing a way to track student tardies.

I need a way to determine if a student has received multiple tardies in a single day and to count how many they got on that day.
Specifically, we have a threshold of 7 tardies. Once a student gets their 7th tardy they receive disciplinary action, like a detention. The problem is, I can easily determine if they get 7 tardies on a certain day, but frequently, they get an 8th tardy on the same day too. Since my query identifies 7 and not 8, it will skip over those students who got two tardies that day (7th and 8th).

I think an IIf statement in a new column is what I need, but I don't have the expertise to write it.

It would go something like. ..If the number of total tardies for the student is 8 and the student has received more than one tardy today, then enter an 8 in this column.
The next threshold is 9 and I have that covered, so I'm looking at for a way to identify students who have 8 tardies and received more than one tardy today. [Date] is a field in my query, so Date() can be used in the statement and [Total of Date] is also a field. Total of Date = total tardies. Thanks for any help you can give.

theDBguy · Aug 23, 2019

Hi. Can you post the SQL statement of your query? Maybe we can fix it.

rmcafee · Aug 23, 2019

The query works as expected, I just need to add in the specific circumstance of students getting those multiple tardies on the same day and skipping over the 7th tardy threshold. Thanks

SELECT [Tardy CURRENT TOTAL Query_Crosstab].[Last Name], [Tardy CURRENT TOTAL Query_Crosstab].[First Name], [Tardy CURRENT TOTAL Query_Crosstab].[Student Name], [Tardy CURRENT TOTAL Query_Crosstab].[Student ID], [Tardy CURRENT TOTAL Query_Crosstab].Grade, [Today Tardy Query].Date, [Tardy CURRENT TOTAL Query_Crosstab].[Total Of Date], [Tardy CURRENT TOTAL Query_Crosstab].[1], [Tardy CURRENT TOTAL Query_Crosstab].[2], [Tardy CURRENT TOTAL Query_Crosstab].[3], [Tardy CURRENT TOTAL Query_Crosstab].[4], [Tardy CURRENT TOTAL Query_Crosstab].[5], [Tardy CURRENT TOTAL Query_Crosstab].[6], [Tardy CURRENT TOTAL Query_Crosstab].[7]
FROM [Tardy CURRENT TOTAL Query_Crosstab] INNER JOIN [Today Tardy Query] ON [Tardy CURRENT TOTAL Query_Crosstab].[Student ID] = [Today Tardy Query].[Student ID]
WHERE ((([Tardy CURRENT TOTAL Query_Crosstab].[Total Of Date])=7));

theDBguy · Aug 23, 2019

rmcafee said:
The query works as expected, I just need to add in the specific circumstance of students getting those multiple tardies on the same day and skipping over the 7th tardy threshold. Thanks

Hi. Thanks for posting the query SQL. What happens if you change the criteria to something like this?

Code:

...WHERE ((([Tardy CURRENT TOTAL Query_Crosstab].[Total Of Date])>=7));

rmcafee · Aug 23, 2019

Yes, it will display tardies of 7 or more, but I don't want that. I only want students who have reached 7 today OR have multiple tardies and have reached 8 tardies by having more than one tardy recorded today. Since the query identifies only students with 7 tardies, I need to add a column that determines, yes, they got tardy 7 and 8 today. Otherwise it will skip over them, because they've passed the 7th tardy on the same day.
I have to limit the number of tardies to 7 because I have other queries that will tell me the other thresholds like 9, 11, 13 , etc.

rmcafee · Aug 23, 2019

Another way to do this might be to have a column that counts the number of records for the student on the date. If they have 8 tardies and have more than one record for today, that would mean they got both the 7th and 8th tardy today. Just not sure how to write the statement for that column in the query. Something like IIF number of tardies = 8 and number of student records is 2 or more, then enter an 8 in this column. Maybe have it count the number of records for a student?

theDBguy · Aug 23, 2019

rmcafee said:
Another way to do this might be to have a column that counts the number of records for the student on the date. If they have 8 tardies and have more than one record for today, that would mean they got both the 7th and 8th tardy today. Just not sure how to write the statement for that column in the query. Something like IIF number of tardies = 8 and number of student records is 2 or more, then enter an 8 in this column. Maybe have it count the number of records for a student?

Hi. It might be easier to understand what you mean/want if you could post a sample set of data for us to play with.

isladogs · Aug 23, 2019

Can you please explain what a 'tardy' means?
After 38 years as a teacher, I've no idea!

rmcafee · Aug 23, 2019

Tardy is simply, late to class.

I don't think I can post data, all I have is live data and I'm required to protect the privacy of the students. It will take some time to put together some dummy data, will try to do that in the next couple of days.

isladogs · Aug 23, 2019

OK - I also don't see this should be difficult but it may depend on your table structure.
All we need is some realistic data to work with - no names required.
It would be better to wait until you can upload something

Pat Hartman · Aug 24, 2019

I think the problem is that you are basing this on a crosstab query and that is making it more difficult than it needs to be.

I am also confused about how you are counting "tardy". How many periods are there in a day? If there are 7, how are you getting 8 tardies?

If you mean 8 tardies in a time period, that is pretty easy.

Select studentID, Count(*) as TardyCount From YourTable
Where AttnDT Between Forms!yourform!txtStartDT and Forms!yourform!txtEndDT
Having Count(*) > 7

If you want to count multiple tardies on a single day as 1 and then only find a problem when there are more than 7 days with 1 or more tardies in the time period, you would have to use nested queries or a sub query because you have to come up with the set of days in the period that have 1 or more tardies and from that determine how many have 7 instances of days with 1 or more tardies.

arnelgp · Aug 24, 2019

if you have the column [8] in [Tardy CURRENT TOTAL Query_Crosstab].[Last Name] crosstab, you can add it to the query that you posted:

Code:

..., [Tardy CURRENT TOTAL Query_Crosstab].[7], 
 [Tardy CURRENT TOTAL Query_Crosstab].[8] 
FROM [Tardy CURRENT TOTAL Query_Crosstab] INNER JOIN …

rmcafee · Aug 25, 2019

Pat Hartman said:
I think the problem is that you are basing this on a crosstab query and that is making it more difficult than it needs to be.

I am also confused about how you are counting "tardy". How many periods are there in a day? If there are 7, how are you getting 8 tardies?

If you mean 8 tardies in a time period, that is pretty easy.

Select studentID, Count(*) as TardyCount From YourTable
Where AttnDT Between Forms!yourform!txtStartDT and Forms!yourform!txtEndDT
Having Count(*) > 7

If you want to count multiple tardies on a single day as 1 and then only find a problem when there are more than 7 days with 1 or more tardies in the time period, you would have to use nested queries or a sub query because you have to come up with the set of days in the period that have 1 or more tardies and from that determine how many have 7 instances of days with 1 or more tardies.

Pat Hartman - Yes, there are 7 pds in a day. It's possible a student could be late to every period and have 7 tardies in one day. The school counts the total tardies, so it's how many they accumulate over time.

Right now, I have a way to see if they got a tardy for today and they've reached the 7 tardy threshold. My problems is, if they get there 7th and 8th (or more) tardies on the same day, my query skips over them because it's looking for only those students that reached "exactly" 7 on today's date.

I need to find students who not only got their 7th tardy today, but also got their 8th (or more) tardy today.

The catch is, I can't do 7 or 8 as a criteria or >=7 because they could've received their 7th tardy in the past and then when they reach their 8th today, it will included them on the list.

I will look over what you posted too. Thanks

isladogs · Aug 25, 2019

I'm still confused.
If there are 7 periods in one day, how can students get more than 7 lates/tardies in the same day?
Anyway why can't you do an aggregate query grouping by student ID and date (and if you want, filtering by date), counting the lates and filtering where the count = 7 or 8.

For example

Code:

SELECT StudentID, MarkDate, Count(Tardy) AS CountOfTardy
FROM TableName
GROUP BY StudentID, MarkDate
HAVING (((Count(Tardy))=7 Or (Count(Tardy))=8));

In my opinion, a crosstab query is unhelpful for this problem.

Pat Hartman · Aug 25, 2019

I told you how to solve the problem using the underlying data rather than the crosstab. It is the crosstab that is confusing you.

rmcafee · Aug 25, 2019

isladogs said:
I'm still confused.
If there are 7 periods in one day, how can students get more than 7 lates/tardies in the same day?
Anyway why can't you do an aggregate query grouping by student ID and date (and if you want, filtering by date), counting the lates and filtering where the count = 7 or 8.

In my opinion, a crosstab query is unhelpful for this problem.

Students can't get more than 7 in one day, but they can get as many as 7 on any given day. The school counts the total number of tardies. How many they get in one day is not relevant. It's how many total they get over the course of the semester. I only include how many tardies they get per period for other data I'm keeping track of.

I use the crosstab in conjunction with another query that tells me if they got a tardy on today's date too. For me, that identifies the students that got a tardy today AND reached the 7th tardy threshold (since the beginning of the school semester).

What you've posted may help, I will look it over. That may work, I just haven't done many of those, but it sounds promising. Thanks

isladogs · Aug 25, 2019

I'm sorry but your explanations seem to change from one reply to the next.
I am still confused.

Anyway you may have replied before I edited my last answer to add an example aggregate query

rmcafee · Aug 25, 2019

Pat Hartman said:
I told you how to solve the problem using the underlying data rather than the crosstab. It is the crosstab that is confusing you.

OK, will do, thanks.

rmcafee · Aug 25, 2019

Just to clarify in the simplest terms...
1. A Tardy is late to class.
2. School records each time a student is tardy (late to class)
3. My school has 7 pds in a day.
4. A student can be late to any period, every day.
5. School issues a discipline response (like a detention) at specific tardy thresholds. The 7th tardy is the first threshold.
6. Tardy count is a cumulative total over the course of the semester.
7. My objective is to identify students AND the specific day they reach that first threshold. (Unrelated to this specific problem, I also track how many they have per period, so the school can try and identify specific periods/problems to correct the issue.)
8. My Problem: Students who reach AND pass the 7 tardy threshold on the SAME day. Like receiving their 7th and 8th cumulative tardy on the same day.

I tried to be consistent in my replies to "specific" questions. Perhaps, I was unclear. I have some posts to work with and hope I can solve this issue. The consensus from the experts here on the forum is... a crosstab is the wrong way to go .

I will post a simple table with some dummy data tomorrow, so users can see an example of the raw data.
I'm obviously a novice with Access, but I know just enough to be dangerous. I would like to solve this problem for my school and help these kids get to class on time. Tracking is critical. We've found that students who get to class actually perform better, imagine that!! I appreciate all the help and suggestions given so far. thanks

isladogs · Aug 25, 2019

Try this...

Code:

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

IIF statement to count multiple records instances in a query (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Registered User.

I’m here to help

MVP / VIP

Registered User.

MVP / VIP

Super Moderator

..forever waiting... waiting for jellybean!

Registered User.

MVP / VIP

Super Moderator

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread