Go Back   Access World Forums > Apps and Windows > Excel

Thread Tools Rate Thread Display Modes
Old 04-17-2019, 09:48 AM   #1
Newly Registered User
Join Date: May 2002
Location: St. Paul, MN USA
Posts: 99
Thanks: 0
Thanked 1 Time in 1 Post
Grouping / Counting Help

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:

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!

LaurieW is offline   Reply With Quote
Old 04-17-2019, 11:07 AM   #2
June7's Avatar
Join Date: Mar 2014
Location: The Great Land
Posts: 2,297
Thanks: 0
Thanked 536 Times in 532 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Grouping / Counting Help

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.
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 04-25-2019 at 09:29 AM.
June7 is offline   Reply With Quote
Old 04-17-2019, 11:40 AM   #3
Newly Registered User
Join Date: May 2002
Location: St. Paul, MN USA
Posts: 99
Thanks: 0
Thanked 1 Time in 1 Post
Re: Grouping / Counting Help

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.

LaurieW is offline   Reply With Quote
Old 04-25-2019, 06:40 AM   #4
I'm with the Witch.......
scott-atkinson's Avatar
Join Date: Aug 2006
Location: West Midlands, (Formally Essex Barrow Boy....)
Posts: 1,584
Thanks: 25
Thanked 29 Times in 26 Posts
scott-atkinson will become famous soon enough
Re: Grouping / Counting Help

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.

As you slide down the banister of life, may the splinters never point the wrong way.
scott-atkinson is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping & Counting zozew Queries 10 10-01-2015 05:33 PM
Counting by range, and grouping by month APHNB Queries 3 03-18-2011 10:45 AM
Grouping and Counting Issue - HELP! scottay Queries 2 02-01-2008 11:20 PM
Grouping in Report - counting no of headers wilkob Reports 3 01-23-2007 11:09 AM
Report Question - Counting & Grouping Vegboy General 1 12-27-2006 11:44 AM

All times are GMT -8. The time now is 08:38 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World