Grouping / Counting Help (2 Viewers)

LaurieW

Registered User.
Local time
Today, 06:59
Joined
May 9, 2002
Messages
99
I have a list of attendance information for a school that I want to group by student and attendance date, and then check to see if there are consecutive periods on the same date for that same student and count them if they exist. Here is sample data:

A B C D
1 ID Name Date Period
2 12345 Julie 10/2/2018 3
3 12345 Julie 10/2/2018 6
4 66778 Fred 9/7/2018 0
5 66778 Fred 9/7/2018 1
6 66778 Fred 9/7/2018 2
7 66778 Fred 9/7/2018 3
8 66778 Fred 9/7/2018 4
9 66778 Fred 9/7/2018 5
10 66778 Fred 9/7/2018 6
11 66778 Fred 10/7/2018 0
12 66778 Fred 11/2/2018 0
13 66778 Fred 11/2/2018 1
14 66778 Fred 11/2/2018 2

So I would like to end up with Juie's (ID 12345) grouped, counting her total absences (2) and checking to see if her absences were on the same date (yes they are) and, if so, were the period numbers consecutive (no they're not) and if so, how many are there (zero for Julie)

For Fred, it would group his records for ID 66778, count his total absences (11), look at his absences that are on the same date and check to see if the period numbers are consecutive. Yes he does have absences on the same days (9/7/18 and 11/2/18) and yes there are consecutive period absences (7 on 9/7/18 and 3 on 11/2/18).

Any ideas how this can be accomplished? I've tried doing this with separate formulas to count the different items (ID, days, periods, etc) but don't know how to put it all together.

Thanks for any help!
Laurie
 

June7

AWF VIP
Local time
Today, 02:59
Joined
Mar 9, 2014
Messages
5,423
In Access, the grouping and counting is just an aggregate query. The other requirements will likely require VBA procedure.

If you want this all in Excel, sorry, no help.
 
Last edited:

LaurieW

Registered User.
Local time
Today, 06:59
Joined
May 9, 2002
Messages
99
I know how to group using the Subtotals and that works for the ID#, but not the date.
I don't know VBA. If anyone can help further, it would be appreciated.
Thanks.
 

scott-atkinson

I'm with the Witch.......
Local time
Today, 10:59
Joined
Aug 31, 2006
Messages
1,622
You could use a series of COUNTIFS formulas, these allow counting on multiple criterias and data, look for examples on Google of how to use them.
 

Users who are viewing this thread

Top Bottom