Access Formula

M

Mike

We are developing a database to track attendance. One factor we use is the
amount of occurrences. For instance, I was out ill 12 day on two occurrences
for the year. That means I was out two times in one year. One occurrence
could be one day or 100 days as long as the days consecutive. For example:

9/1/05
9/2/05
9/3/05

In excel I have a formula that helps in most cases:
=IF(DATEVALUE(B2)-DATEVALUE(B1)>2,1,0)
The B column are the dates.
Is there any way I could group consecutive days and count as one occurrence?

Thanks for your help...

Mike Chamis
 
J

James A. Fortune

Mike said:
We are developing a database to track attendance. One factor we use is the
amount of occurrences. For instance, I was out ill 12 day on two occurrences
for the year. That means I was out two times in one year. One occurrence
could be one day or 100 days as long as the days consecutive. For example:

9/1/05
9/2/05
9/3/05

In excel I have a formula that helps in most cases:
=IF(DATEVALUE(B2)-DATEVALUE(B1)>2,1,0)
The B column are the dates.
Is there any way I could group consecutive days and count as one occurrence?

Thanks for your help...

Mike Chamis
Click to expand...

tblDaysMissed
DMID AutoNumber
AbsentDate Date/Time
EmployeeID Long

DMID AbsentDate EmployeeID
1 8/8/05 3
2 8/9/05 3
3 8/11/05 3
4 8/12/05 3
5 8/15/05 3
6 8/16/05 4

qryOccurrences:
SELECT DISTINCT EmployeeID, (SELECT Nz(Count(*) + Sum((Select
Nz(MIN(A.AbsentDate)) FROM tblDaysMissed AS A WHERE A.AbsentDate >
B.AbsentDate AND A.EmployeeID = tblDaysMissed.EmployeeID) = DateAdd('d',
1, B.AbsentDate)),0) FROM tblDaysMissed AS B WHERE B.EmployeeID =
tblDaysMissed.EmployeeID) AS Occurrences FROM tblDaysMissed;

!qryOccurrences
EmployeeID Occurrences
3 3
4 1

This seems to work but I didn't test it much. You can also supply
'WHERE EmployeeID = 3' or restrict the range of dates to test. It
assumes that every day is a possible attendance date and calculates an
expression equivalent to boolNextDateIsConsecutive. When this
expression is True (-1), the -1 subtracts from the 1 added from
Count(*). One subquery is used to get the next date following the
current date. The other nested subquery singles out the records for the
current employee. There may be a simpler expression to do this but it's
getting late. Also, I didn't understand how your Excel cell formula
could give you what you wanted.

Hope this helps,
James A. Fortune
 
D

Dextergiii

I've been working on a similar attendance project myself and like Mike I've
been trying to distinguish between the number of days missed and number of
violations where one violation can = multiple days. So a new record is
created in the violations table for every day missed. Every record has a
unique violation id (counts the days missed) and has a non-unique violation
id to (hopefully) count the number of violations.

ViolationIDAdv, Date, ViolationUniqueID
1, 1/2/08, 1
1, 1/3/08, 2
1, 1/6/08, 3
2, 2/5/08, 4

Here's the query I have (this also has multiple sub-queries that count:
Tardies, Partial Days worked, No-Shows, etc).

SELECT e.EmpFullName AS [Full Name],
(Select count(distinct v.violationidadv) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 1 and v.excusable
= no and v.employeeid = e.employeeid) AS Violations,
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 1 and v.excusable
= no and v.employeeid = e.employeeid) AS Days,
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 7 and v.employeeid
= e.employeeid) AS [No Call/No Show],
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 2 and v.employeeid
= e.employeeid) AS Tardies,
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 6 and v.employeeid
= e.employeeid) AS [Partial Days],
(Select v.dateofoccurance from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 5 and v.employeeid
= e.employeeid) AS [Final Warning],
Tbl_Supervisors.SupervisorName
FROM Tbl_Employees AS e
INNER JOIN Tbl_Supervisors ON e.SupervisorID=Tbl_Supervisors.SupervisorID
WHERE (((e.Inactive)=False) AND ((e.PositionID)<>5))
ORDER BY Tbl_Supervisors.SupervisorName, e.EmpFullName;

As soon as I added "Disctinct" in the first subquery count, Access returns a
syntax error. Any ideas how I can revise the first subquery to count only
the number of unique non-unique id's?

Dex
 
J

James A. Fortune

Dextergiii said:
I've been working on a similar attendance project myself and like Mike I've
been trying to distinguish between the number of days missed and number of
violations where one violation can = multiple days. So a new record is
created in the violations table for every day missed. Every record has a
unique violation id (counts the days missed) and has a non-unique violation
id to (hopefully) count the number of violations.

ViolationIDAdv, Date, ViolationUniqueID
1, 1/2/08, 1
1, 1/3/08, 2
1, 1/6/08, 3
2, 2/5/08, 4

Here's the query I have (this also has multiple sub-queries that count:
Tardies, Partial Days worked, No-Shows, etc).

SELECT e.EmpFullName AS [Full Name],
(Select count(distinct v.violationidadv) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 1 and v.excusable
= no and v.employeeid = e.employeeid) AS Violations,
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 1 and v.excusable
= no and v.employeeid = e.employeeid) AS Days,
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 7 and v.employeeid
= e.employeeid) AS [No Call/No Show],
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 2 and v.employeeid
= e.employeeid) AS Tardies,
(Select count(v.violationuniqueid) from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 6 and v.employeeid
= e.employeeid) AS [Partial Days],
(Select v.dateofoccurance from Tbl_Violations v where
(v.DateofOccurance>DateAdd("m",-12,Date())) and v.typeid = 5 and v.employeeid
= e.employeeid) AS [Final Warning],
Tbl_Supervisors.SupervisorName
FROM Tbl_Employees AS e
INNER JOIN Tbl_Supervisors ON e.SupervisorID=Tbl_Supervisors.SupervisorID
WHERE (((e.Inactive)=False) AND ((e.PositionID)<>5))
ORDER BY Tbl_Supervisors.SupervisorName, e.EmpFullName;

As soon as I added "Disctinct" in the first subquery count, Access returns a
syntax error. Any ideas how I can revise the first subquery to count only
the number of unique non-unique id's?

Dex

:

James... Your wonderful. I can't thank you enough.

Mike.

:
Click to expand...
Click to expand...

This is an interesting problem. I'll take a look at it and maybe post
back on Monday. In retrospect, I should not have relied on True being
-1 in my response to Mike. In the meanwhile, I invite others to chime
in. It might be helpful to have a separate Tbl_ViolationsMain that has
one line for every unique ViolationIDAdv in Tbl_Violations (similar in
concept to tblOrders, tblOrderDetails). That would allow you to combine
Tbl_Violations with tblDaysMissed by including a ViolationIDAdv in the
table as a foreign key that only gets populated when the AbsentDate is
part of a group of violation days. It makes sense that missed days
include both normally missed days as well as violations. A table with
one violation per line (with the possibility of multiple dates) might
also allow you to create appropriate subqueries more easily. Maybe
rename the Date field to something like AbsentDate or MissedDate because
Date is a reserved word in Access. Also, why doesn't Tbl_Violatons
contain the EmployeeID? I'm thinking something like:

tblEmployees
EmployeeID AutoNumber
EmpFullName Text (It might be better to use EmpFirstName along with
EmpLastName instead.)
PositionID Long (Foreign Key)
SupervisorID Long (Foreign Key)
Inactive Y/N

tblPositions
PositionID AutoNumber
PositionDescription Text

tblSupervisors
SupervisorID AutoNumber
SupervisorName Text

tblMissedDays (this table [your Tbl_Violations] acts somewhat like a
details table to tblViolations except that it can contain missed days
that are not violations)
DMID AutoNumber
AbsentDate Date/Time <= your Date
EmployeeID Long (Foreign Key)
VID Long (Foreign Key) <= your ViolationIDAdv

tblViolations (DateofOccurance can be obtained as tblMissedDays.AbsentDate)
VID AutoNumber <= your ViolationIDAdv
KOVID Long (Foreign Key) <= your TypeID

tblKindOfViolation
KOVID AutoNumber
KindOfViolation Text

Then

tblMissedDays
DMID AbsentDate EmployeeID VID
1 1/2/08 44 1
2 1/3/08 44 1
3 1/6/08 44 1
4 2/5/08 52
5 2/6/08 52 2
6 2/7/08 52 2

tblViolations
VID KOVID
1 5
2 4

That schema at least gives us a starting point for discussion.

James A. Fortune
[email protected]
 
Top