Running Totals

[Keith]

I have a problem that should be simple, but I have been away from Access
work for a number of years and keep hitting problems that a know I
should be able to do.

I have a simple database. It has three tables.

Staff - containing staff number, name and department

Entitlement - containing staff number, Year, Annual, Public

Request - containing each holiday request Staff number, year, Start
date, end date, Annual, Public

I have a form with the Staff and Entitlement tables joined by staff
number, and there Annual and Public values for the year.

In the middle I have a subform with all the Holiday requests. This is
again linked by staff number.

What I am trying to get is in the form footer the number of Annual and
public days left by adding up all the annual and public for the person
and taking it away from the Entitlement Annual and public.

Anybody got any ideas as to how I can do this?

 

[Barry Gilbert]

Try using the DLookup function to get the values from each table, then
do the math. For example,
(DLookup("Annual","Entitlement","[StaffNum] = " & me.txtStaffNum & "
AND [Year] = " & Year) + Dlookup("Public","Entitlement","[StaffNum] = "
& me.txtStaffNum & " AND [Year] = " & Year)) -
(DLookup("Annual","Request","[StaffNum] = " & me.txtStaffNum & " AND
[Year] = " & Year) + Dlookup("Public","Request","[StaffNum] = " &
me.txtStaffNum & " AND [Year] = " & Year))

Not the slickest way, though. Maybe your form's recordset already
contains these values. You could do the math on those.