IIF statement to count multiple records instances in a query

[Pat Hartman]

No, each tardy stands on its own.

What I was trying to say, was that the data entry for a full day would be complete BEFORE you ran any queries so today, you would be looking at yesterday and earlier but never today.

I don't have to use a date range.

You do unless you are completely replacing the set of data each time you run this process. And if that is the case, you should link to the spreadsheet or csv file rather than importing it to prevent bloat. However, the best solution is to link to the source of the data if the PTB will allow you to.

I have to track when they reach each threshold level

As several of us have already indicated in one way or another, you can't do that with the method you are planning on using. What happens if in set1 a student has 6 lates and then in set2 he (she's are never late ) has 8 more? you would never reach that threshold without both sets of data available. When does the count reset? After a warning? Never?

 

[rmcafee]

What I was trying to say, was that the data entry for a full day would be complete BEFORE you ran any queries so today, you would be looking at yesterday and earlier but never today.
You do unless you are completely replacing the set of data each time you run this process. And if that is the case, you should link to the spreadsheet or csv file rather than importing it to prevent bloat. However, the best solution is to link to the source of the data if the PTB will allow you to.
As several of us have already indicated in one way or another, you can't do that with the method you are planning on using. What happens if in set1 a student has 6 lates and then in set2 he (she's are never late ) has 8 more? you would never reach that threshold without both sets of data available. When does the count reset? After a warning? Never?

Yes, data would be complete for a full day. We never run it during the school day, but can run it after school. The data is completely refreshed each day and includes all tardies for students from day one of the semester.
Yes, in my real database the file is linked. We can't link directly to files in the SIS though.
Yes, the count never resets until the end of the semester. Then, when I query out the data from the SIS for second semester, there is a date range. The date range is addressed before I do anything in Access.

I thought the simple solution was adding the "between" criteria, but that's not the case. It simply was a coincidence that the data came out like it was working.
Back to the drawing board.

 

[rmcafee]

I thought the simple solution was adding the "between" criteria, but that's not the case. It simply was a coincidence that the data came out like it was working.
Back to the drawing board.

 

[isladogs]

As I said at the start, I was a teacher for 38 years and for the last 15 years or so in charge of data policy and analysis at several schools. Amongst many other things that included managing lates and follow ups to those lates.

There has been a lot of criticism in this thread but it has been directed at what appears to be a poorly thought out policy rather than at yourself. If nothing else, I would urge you to feedback the criticism asking the senior management to review the policy with the aim of creating one that is simple to implement and clear in its aims and objectives.

With your current setup, if I was a problem student with a bit of common sense I would probably be late to 6 periods a day and on time for the other one. One less threshold to be followed up

 

[Mark_]

To add to Colin's post, does per period absence fall into this?
In other words, if I skip 4 periods, but am on time for the 5th, this seems to not have an impact.

 

[Pat Hartman]

We still haven't seen the school's written policy on tardy. They do have one, don't they?

 

[rmcafee]

We still haven't seen the school's written policy on tardy. They do have one, don't they?

Tardiness To Class Policy

All students are expected to be in class on time as listed on the bell schedules. There is adequate time between classes and before school for students to arrive on time. Students should be in the classroom or gym before the tardy bell begins to ring. Tardies to class may only be excused by school personnel. All other tardies are considered unexcused.

Procedures

Students who are not in class on time will be required to report the closet Grade Level Office where the student will be processed by office personnel and/or the administration and assigned consequences when needed. A late permit will be given for admission to class. Students who arrive at the Grade Level Office excessively late (7 or more minutes) after the tardy bell will be considered AWOL and will receive additional consequences. Tardies are cumulative for a student’s entire school day, therefore it is possible for a student to accumulate 6 tardies in 1 school day. Tardies will be reset at the beginning of each semester.

1st – 6th tardies = Warning

7th Tardy – 3 days restricted lunch

9th Tardy – 5 days restricted lunch

11th Tardy – Wednesday / Saturday School

13th Tardy – 1 Day of ISS

15th Tardy – 3 days of ISS

17th Tardy – 2 days OSS

19th Tardy – will be handled at the discretion of the administrative staff

Time out of class due to tardiness will be considered unexcused.

 

[rmcafee]

As I said at the start, I was a teacher for 38 years and for the last 15 years or so in charge of data policy and analysis at several schools. Amongst many other things that included managing lates and follow ups to those lates.

There has been a lot of criticism in this thread but it has been directed at what appears to be a poorly thought out policy rather than at yourself. If nothing else, I would urge you to feedback the criticism asking the senior management to review the policy with the aim of creating one that is simple to implement and clear in its aims and objectives.

With your current setup, if I was a problem student with a bit of common sense I would probably be late to 6 periods a day and on time for the other one. One less threshold to be followed up

Honestly, the policy isn't the problem. I posted the policy in another response, you can check it out there.
I haven't see a lot of criticism, mainly people putting their thoughts out there on the way they think this can be solved. There have been so many responses, I haven't even been able to look at all of them yet.
I have seen a couple people who are just rude, but I've just pretty much ignored it. I'm not going to respond their posts anymore.....unless they can solve this problem. haha.
My only frustration is that I feel like I've repeated myself a dozen times trying to explain this loophole.. I guess I'm not explaining it adequately. I'm about ready to give up, but I will give it one more day and only look at responses.

If you were late 6 periods a day, you would be identified the next day when you got 6 more, see the policy. Tardies are cumulative over the course of the semester. I can easily identify students who reach each threshold in the policy. The problem is...students that reach one threshold and proceeds on to the next threshold, on the same day. A student hits the 7th late (tardy) threshold and then two periods later hits the 9th threshold. I can identify the 9th threshold, but I also want to identify that they hit the 7th late threshold and have it show in the query I will use for a report. BTW - remember that we check this every day and try to identify students that hit a threshold, every day. So I also have the priority to pull data for the day and check their "cumulative" tardy total for the semester.
I think I can still use the "between" option that you posted and that will make my product a little better and help out the clerks some too.

I'm really just trying to refine the final product to help our clerks.
My workaround is just to have a query for each threshold. It will still catch each student and the threshold they hit, but it won't show that they've hit the other threshold earlier in the day. It will only show the highest threshold. That's OK, just have to train the clerks to look for that.

 

[Pat Hartman]

Your data needs to span the entire semester so you can't just be looking at last week's data since you need to have an accumulation that goes all the way back to whenever the counting starts. You also will need to check the tardy status every day or you could miss a stage.

I think we're getting somewhere. I don't have a solution but I have an idea of an approach. You need all the detail records for the entire semester. You need a running sum (we can post that if you don't know how to do it). Then you need a custom function which we can help you write. The custom function will examine the running sum and at the error levels, flag that record with some indicator. Then you take that query as input to today's tardy query and any record that reached any threshold "today" is extracted and that is the "tardy" list for today. You are not looking for specific thresholds, your function marks all thresholds and your final query just picks out the ones that happened today for yesterday.

If that sounds like what you need, we can help you build it of you don't know how. I just don't want to waste time building something that doesn't actually solve your problem.

 

[rmcafee]

Your data needs to span the entire semester so you can't just be looking at last week's data since you need to have an accumulation that goes all the way back to whenever the counting starts. You also will need to check the tardy status every day or you could miss a stage.

I think we're getting somewhere. I don't have a solution but I have an idea of an approach. You need all the detail records for the entire semester. You need a running sum (we can post that if you don't know how to do it). Then you need a custom function which we can help you write. The custom function will examine the running sum and at the error levels, flag that record with some indicator. Then you take that query as input to today's tardy query and any record that reached any threshold "today" is extracted and that is the "tardy" list for today. You are not looking for specific thresholds, your function marks all thresholds and your final query just picks out the ones that happened today for yesterday.

If that sounds like what you need, we can help you build it of you don't know how. I just don't want to waste time building something that doesn't actually solve your problem.

Yes, this sounds very promising. The last part of your response looks ideal. You're correct, I don't think I have the expertise to do all this, but I'm a pretty quick learner. Much of what I've learned has been looking at other databases and learning from them....and of course, looking for answers on the posts in this forum. I've found the more I learn about Access, the more I need to learn.
Any help would be appreciated - thanks

 

[Pat Hartman]

It's late here and I have to get to bed. I'm off to a bridge tournament for a few days and I need my brain cells to be rested. I know there are many posts here regarding running sums so start by searching for one of those and that's the first step. I'm sure if my idea will solve the problem that someone will jump in and help write the VBA function. If not, I'll be back on Sunday and I'll look in.

 

[arnelgp]

here is you cumulative late.
just add the start date and end date of the sem.

tip. just ignore. make them invisible. talk to the hand.

 

[rmcafee]

here is you cumulative late.
just add the start date and end date of the sem.

tip. just ignore. make them invisible. talk to the hand.

Thanks. I've had some health issues and some other things going on and I've just now been able to revisit this.

I see the SQL for the query, but where exactly do I add the dates? The start date would be 8/5/2019 and end date would be 12/20/2019. Not sure where or how to plug these in. Looks like after the <=, but not sure the syntax to use.
thanks

 

[arnelgp]

sorry to here that, get well soon!
I put the parameter in the query, it will ask you.

 

[rmcafee]

Your data needs to span the entire semester so you can't just be looking at last week's data since you need to have an accumulation that goes all the way back to whenever the counting starts. You also will need to check the tardy status every day or you could miss a stage.

I think we're getting somewhere. I don't have a solution but I have an idea of an approach. You need all the detail records for the entire semester. You need a running sum (we can post that if you don't know how to do it). Then you need a custom function which we can help you write. The custom function will examine the running sum and at the error levels, flag that record with some indicator. Then you take that query as input to today's tardy query and any record that reached any threshold "today" is extracted and that is the "tardy" list for today. You are not looking for specific thresholds, your function marks all thresholds and your final query just picks out the ones that happened today for yesterday.

If that sounds like what you need, we can help you build it of you don't know how. I just don't want to waste time building something that doesn't actually solve your problem.

Yes, please post how to do the running sum. I've been looking at it and I'm having trouble with it because the field that records the tardy (Period Absence) is a text field. You can use the attached sample database. It has the same fields that I will be using.

 

[rmcafee]

sorry to here that, get well soon!
I put the parameter in the query, it will ask you.

Thanks, but I need something that doesn't prompt the user every time.
My plan is to use the query or queries to set up a report that the clerks can access everyday and I want to avoid any pop ups. The report will show the students that reached each threshold on a specific day (probably the previous day) and I will have the report linked to a button on a form. They have to pull the report every day, I plan on having them do it by simply clicking on a form button.
I just need the query set up and I can do the rest. The data has to be refreshed everyday and several clerks will be accessing the report, so I want to make it as simple as possible. One clerk will be updating the data everyday from our student information system on the back end. I will have a front end database that each clerk can access, so if I can get this query right it should work for them. The missing piece is getting this complicated query to work. Hope this makes sense.

 

[Pat Hartman]

sorry rmcafee, my suggestion is different from what arnel is attempting to implement for you. A response from me to your request will only confuse the issue. I did use the wrong terminology though. I said running sum but a more accurate term is rank. You want to count the records in a set as 1, 2, 3 based on how many have a lower ID value for the same studentID.

 

[rmcafee]

I thought I would post that I found a way to accomplish my goals for this database.

The first step was to get a “running count” of the each student’s tardies (late to class), in the order in which they occurred.(Per Pat's suggestion)

In my query of all tardies that have occurred to the present, I used the following DCount expression below.

You will notice that I used “Val” at the beginning of the DCount too. I added this later when I realized that the report I created based on this query would not sort the running count of the tardies properly.

It’s also important to note that I used unique field identifiers like Student ID and ID (Primary Key) because those make the function work as intended, versus a text field like Student Name.

RunningCount: Val(DCount("*","[Tardy]","[Student ID] = '" & [Student ID] & "' And [ID]<=" & [ID]))

In the same query, I selected the “SUM” function option while in Design View and sorted the Student Name (Ascending) and Date (Ascending). I chose “Expression” in the “Total:” option (Design View) for the above DCount Expression.

The second step was to create another query based on this query. I did this to identify when each student reached one of the Tardy thresholds (See Tardy Policy posted earlier).

In the Date field, I use the criteria Date() or Date()-1 Or Date()-2 Or Date()-3. Each day, when the tardy data is updated, the report I created will give the thresholds reached today and in each of the last three days.

I combined this Date criteria with another one under the RunningCount field. The criteria for the RunningCount field is "7" Or "9" Or "11" Or "13 or 15" Or "17" Or "19" or >=”20” (See Tardy Policy posted earlier). I use this in combination with the above Date criteria to create a report to show which students reached each tardy threshold during today and the past three days. The report groups by date and then sorts the students by RunningCount, Student Name, and Student ID. As I noted earlier, using the “Val” in the DCount expression made the report sort the RunningCount properly for the report.

 

[Pat Hartman]

Thanks for posting back. It looks like you were able to make use of my suggestion on how to start.