I have two tables that look like this:

1) Employees
Employee ID (key)
Employee Name
[other irrelevant fields]
Holiday Due (this is the entitlement per year)
[other irrelevant fields]

2) Holidays
Holiday ID (key)
Employee ID (lookup in above table)
Notified (date)
Start (date)
Finish (date)
Duration (integer)

I have a form based on table 2 - Holidays. I enter details when an employee takes a holiday.

How can I create another table/query that keep a tally of holiday remaining, using the Holiday Due field in table 1 - Employees.

I need it to show Employee name and show Holiday entitlement, total taken so far (using 'Duration' from 'Holidays') and holiday remaining (new field/expression?). All holiday values are in days (single number).

Any help is appreciated as I can't get my head around it - it is starting to ache!

Scott.

Use a totals query, Sum days taken - HolidayDue group by EmpID. You don't need duration as a stored field it can be calculated using DateDiff

Great thanks Rich, it worked a treat!

Scott.