Sum function in a form

Question

I have been out of the "Access" loop for about 11 years and I'm trying to create a database to make my life easier at work.  I first of all need my Employee form to report the # of hours they have available or have used. 

There are different types of absences, such as: Sick, Vacation, Personal, Unpaid...How can I get the form to show the total for each type for the employee shown?

I have two tables that the info is coming from.  I get it to display the # of sick days (for instance) but it displays the same # for every employee, so it's not keeping the data for each employee separate.  I know this should be easy but it's been so long since I have messed with Access...I used to love it!

Answer 1

As you are recording the absences as hours credited or debited you don't need to worry about weekends or public holidays in computing the balance of leave available per employee.  It's simply a question of summing the Hours Missed/Awarded values per employee.

With the following query you can either enter a date range at runtime, leave both the start date and end date parameters blank, in which case the net credit hours per employee per absence type would be returned for the whole of the period covered by the PTO-Track table, or enter a start date but leave the end date blank, in which case the net credit hours  per employee per absence type from the start date to the end of time will be returned.  You could also do the reverse by leaving the start date blank and entering an end date, but you'd be unlikely to want to do that.

PARAMETERS [Enter start date:] DATETIME,

[Enter end date:] DATETIME;

SELECT EmpList.ID, [FirstName], [Last Name], [Absence/Award Type],

SUM([Hours Missed/Awarded]) As [Hours Available]

FROM EmpList INNER JOIN [PTO-Track]

ON EmpList.ID = [PTO-Track].ID

WHERE ([Date of Absense/Award] >= [Enter start date:]

OR [Enter start date:] IS NULL)

AND ([Date of Absense/Award] < [Enter end date:]+1

OR [Enter end date:] IS NULL)

GROUP BY EmpList.ID, [FirstName], [Last Name], [Absence/Award Type];

Answer 2

I have the following tables:

EmpList: ID PK, Last Name, First Name, Address, Inactive (Y/N) etc...

PTO-Type: Absence Type PK (this will have + or - hours, as they are earned/used)

PTO-Track: ID PK, EmployeeID (lookup list box, pull from query of EmpList table), Date Requested, Date of Absense/Award, Absence/Award Type (lookup combo box, pull from PTO-Type table), Hours Missed/Awarded, Approved by

ApprovedBy: ApprovedBy PK

Answer 3

Wow, Ken Sheridan, you are very helpful! Thanks for taking the time to go into such detail. I do have an Absence table. I'm a little overwhelmed with Access at this point, never thought that would be the case, because I have always felt it came natural to me...I'm embarrassed.  I not only forgot almost everything I knew, but then the different versions are really making it a problem for me.  I need to get on some vitamins for brain functionL!  I may have to throw in the towel on this project.  Thank you both for your answers.

Answer 4

Marshall has assumed that your Absences table is incorrectly designed, which may or may not be the case.  If you do have separate columns (fields) for each type of absence then that is a bad design as it encodes data as column headings.  A fundamental principle of the database relational model is the Information Principle.  This requires that all data be stored as values at column positions in rows in tables, and in no other way.

What you have is a many-to-many  relationship type between employees and absence types.   A correct design would have three tables, e.g.

Employees

....EmployeeID (PK)

....FirstName

....LastName

....etc

AbsenceTypes

....AbsenceType (PK)

The latter would have one row for sickness, one for vacation etc.  To model the many-to-many relationship type between the two you'd have a third table which resolves the many-to-many relationship type into two one-to-many relationship types:

EmployeeAbsences

....EmployeeID (FK)

....AbsenceType (FK)

....DateFrom

....DateTo

The one-to-many relationships between Employees and EmployeeAbsences on EmployeeID, and between AbsenceTypes and EmployeeAbsences on AbsenceType should be enforced.

A query to return the total each absence type per employee would sum the difference between the DateTo and DateFrom times, but this does not allow for weekends or public/concessionary holidays.  To discount these the following function can be used:

Public Function WorkDaysDiff(varLastDate As Variant, _

                            varFirstDate As Variant, _

                            Optional blnExcludePubHols As Boolean = False) As Variant

    Const conSATURDAY As Integer = 6

    Const conSUNDAY As Integer = 7

    Dim lngDaysDiff As Long, lngWeekendDays As Long

    Dim intPubHols As Integer

    If IsNull(varLastDate) Or IsNull(varFirstDate) Then

        Exit Function

    End If

    ' if first date is Sat or Sun start on following Monday

    Select Case WeekDay(varFirstDate, vbMonday)

        Case conSATURDAY

        varFirstDate = varFirstDate + 2

        Case conSUNDAY

        varFirstDate = varFirstDate + 1

    End Select

    ' if last date is Sat or Sun finish on following Monday

    Select Case WeekDay(varLastDate, vbMonday)

        Case conSATURDAY

        varLastDate = varLastDate + 2

        Case conSUNDAY

        varLastDate = varLastDate + 1

    End Select

    ' get total date difference in days

    lngDaysDiff = DateDiff("d", varFirstDate, varLastDate)

    ' get date difference in weeks and multiply by 2

    ' to get number of weekend days

    lngWeekendDays = DateDiff("ww", varFirstDate, varLastDate, vbMonday) * 2

    ' subtract number of weekend days from total date difference

    ' to return number of working days

    WorkDaysDiff = lngDaysDiff - lngWeekendDays

    ' exclude public holidays if required

    If blnExcludePubHols Then

        intPubHols = DCount("*", "PubHols", "HolDate Between #" _

        & Format(varFirstDate, "yyyy-mm-dd") & "# And #" & _

        Format(varLastDate - 1, "yyyy-mm-dd") & "#")

        WorkDaysDiff = WorkDaysDiff - intPubHols

    End If

End Function

This requires a table named PubHols with a column HolDate in which each public/concessionary holiday date is entered.  A query to return the total absences per employee per absence type, discounting public/concessionary holidays, would be:

SELECT EmployeeID, AbsenceType,

SUM(WorkDaysDiff(DateTo, DateFrom, TRUE)) AS DaysAbsent

FROM EmployeeAbsences

GROUP BY EmployeeID, AbsenceType;

You can create a form in continuous forms view based on this query and embed it as a subform within your employees form, linked to the parent form on EmployeeID as Marshall described.

It gets a little more complex if you need to return separate values for different date ranges.  In my own organisation our leave year ran from 1 April to 31 March each year, which was our standard accounting year for leave allowances as well as financially, so it would be appropriate to return the totals per accounting year.  For this another table can be created:

AccountingYears

....AccountingYear

....StartDate

....EndDate

so for the last two years in our case this would have rows:

2011-12        2011-04-01    2012-03-31

2012-13        2012-04-01    2013-03-31

I've used the ISO standard of YYYY-MM-DD for date notation here, but you'd normally enter the dates in your local regional format of course. If we assume that no period of absence spans the boundary between accounting years then a query to return the absences per employee per absence type per accounting year would be:

SELECT EmployeeID, AccountingYear, AbsenceType,

SUM(WorkDaysDiff(DateTo, DateFrom, TRUE)) AS DaysAbsent

FROM EmployeeAbsences INNER JOIN AccountingYears

ON EmployeeAbsences.DateFrom >= AccountingYears.StartDate

AND EmployeeAbsences.DateFrom <= AccountingYears.EndDate

GROUP BY AccountingYear, EmployeeID, AbsenceType;

If a period of absence could span the boundary between accounting years it gets more complex, as it is then necessary to detect intersecting ranges.  This in itself is not difficult, but it also becomes necessary to compute how many days of the period of absence fall with  each accounting year.  It's simpler if in such a case the period of absence is entered in the EmpoyeeAbsences table as two rows, e.g. if employee 42 is sick from 28th March to 4th April:

42    Sickness    2012-03-28    2012-03-30

42    Sickness    2012-04-02    2012-04-04

The 31st march and 1st April were the weekend this year, so don't appear in the ranges, but even if they did the WorkDaysDiff function would discount them.

Answer 5

I suggest you use a subform for the totals. The subform's record source query might look something like:

SELECT EmpID, Sum(SickDays) As TotalSick, Sum(Vac) As TotalVac,

Sum(PersDays) As TotalPers, ...

FROM tblAbsemces

GROUP BY EmpID

Then set the subform controls LinkMaster/Child properties to the EmpID field.

Once you have that, the main form can do calculations using those totals by refering to the relevant subform text boxes with subformcontrolname.Form.TotalSick, etc.