Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2019, 09:48 AM   #1
LaurieW
Newly Registered User
 
Join Date: May 2002
Location: St. Paul, MN USA
Posts: 99
Thanks: 0
Thanked 1 Time in 1 Post
LaurieW
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:

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

LaurieW is offline   Reply With Quote
Old 04-17-2019, 11:07 AM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,512
Thanks: 0
Thanked 367 Times in 366 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Grouping / Counting Help

The grouping and counting is just an aggregate query. The other requirements will likely require VBA procedure.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 04-17-2019, 11:40 AM   #3
LaurieW
Newly Registered User
 
Join Date: May 2002
Location: St. Paul, MN USA
Posts: 99
Thanks: 0
Thanked 1 Time in 1 Post
LaurieW
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.
Thanks.

LaurieW is offline   Reply With Quote
Reply

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 12:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
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