I've searched and searched to no avail to find the answer, hopefully you can help.

The story is:

At work, our holidays are now taken as hours off. For example...if I take a Monday off, its 8hrs and 5 min and a Monday morning would be 4hrs and 5 min...What I want to do is calculate how many hours I have taken off throughout the year.

The way I was going to do it was:

Create Table called Times,

Fields = Week, Day, Morning, Afternoon, Friday and 1hour all Data Type "Date/Time"

Week = 37hrs, Day = 8hrs 5min, Morning = 4hrs 5min, Afternoon = 4hrs, Friday = 4hrs 40min, and 1hour = 1hr.

Then create another table called Hol Booking

Similar field names Wk, Days, Morn etc. They would be Data Type "Number"

Now in my simple head, I thought I could simply record how many weeks or days off i had off. Then create a query (for example) Week*Wk to calculate the amount of hours I had off.
In simple terms if I had 2 weeks and 2 days off, the query would multiply Week (37) by Wk (2) and Day (8:05) by Days (2), giving a total of 90 hrs and 10 min.

I know there is a datatype mismatch, but is there a way round this.

Thanks for listening

You are on the right track, but you need to make some changes:

tblPeriods
PeriodID (PK Autonumber)
PeriodName (text)
PeriodMinutes (long Integer)

This would replace your Times table. It would look like this:

PeriodID...PeriodName...PeriodMinutes
1.............Week...........2220
2.............Day.............485
3.............Morning........245

etc.

You would then have:

tblHolBooking
HolBookingID (PK Autonumber)
HolStart (Date/Time)
PeriodID (FK to tbPeriods)
PeriodQty (default to 1)
EmployeeID (FK)

Now you can create a query that joins these two, so you can multiple the PeriodMinutes by the PeriodQty and then Sum them by Employee. You could then divide by 60 to get the hours.

Off to give it a go...cheers