Grouping, then counting groups.

[RedWolf3x]

Ok, I have a very simple query that I'm looking to make a lot more difficult. A little background. I'm designing a database for a company that has hours Monday - Friday. I'm working on tracking occurences (people absent). I've currently made a simple query posted below.


SELECT tblAttendance.Name, Count(tblAttendance.Name) AS CountOfName
FROM tblAttendance
WHERE ((((tblAttendance.[Scheduled/Unscheduled])="Unscheduled")) And ((tblAttendance.Date) Between Forms!SupAttForm!CSROCCTStart And Forms!SupAttForm!CSROCCTEnd))
GROUP BY tblAttendance.Name;

This simply counts the days that a person has been recorded absent. However I'm looking to count occurences instead of absences. A single occurence can last several consecutive days and can bridge over a weekend/holiday. For instance if a person was absent Mon, Tues, and Wed that would be 1 occurence. Or absent Friday and Monday, still 1 occurence. If they were absent Monday and Tuesday, here on Wednesday and gone on thursday, that would be 2 occurences. This of course complicates the query, to complicate it further, I need to find a way to accomodate for holidays as well, if a person is out Friday and Tuesday, but Monday was a Holiday, only 1 occurence. Any Help would be appreciated.

Thanks.
Red

[jzwp11]

First, using "name" and "date" as field names is not a good idea since both are reserved words in Access. Here is a complete list of reserved words and symbols in Access. Also it is not a good idea to use special characters such as a /, ?, # etc. in your table or field names. A "/" happens to be a reserved symbol in Access.

With respect to counting different periods/occurrences of absences (separated by work days where the person was in attendance), that cannot be done with a query. It will require a custom function. With that said, I assume that you are recording individual absent dates in your tblAttendance, is that correct?

[Robeen]

My first thought - looking at your scenario - would be to put a Function call in place of this:

Count(tblAttendance.Name) AS CountOfName

It might look something like this in your query design {this is not working code - just an example}:

Occurances: Count_Occurances(Forms!SupAttForm!CSROCCTStart As Date, Forms!SupAttForm!CSROCCTEnd As Date, tblAttendance.Name)

In your Function, you would use the three variables to count how many occurances there are.
Are you comfortable writing VBA Code?
If not, I could post a code sample for you to use as a starting point to calculate your occurances.

Let me know how you want to proceed.

P.S. 'Name' is a reserved word in VBA . . . and VB as well. It's better not to create a variable called 'Name'. You could try Emp_Name.

[Robeen]

Sorry jzwp11 . . . your post wasn't there when I started my reply.

[jzwp11]

No problem Robeen. I've been thinking about how I would code the function & just finally decided to post an initial reply.

[RedWolf3x]

Thanks to all that replied. I decided this morning to go with a workaround. I've simply added another field to the table for noting a continuous absence. Thanks for all your help.

[jzwp11]

Out of curiosity, how would you group by that field and do the count of occurrences you mentioned?