I have one table that is a time card.
The total hours worked in any day are collected in there and I also want to calculate how many extra hours are worked in any day.
How many extra hours is calculated by deducting a set number of hours from the total number of hours worked in any day.
The tricky bit is that the set number of hours to deduct varies depending on the day of the week.
Let me show the difference in Set Hours in a table
Day of Week Set Hours Mon 07:30 Tues 07:30 Weds 07:30 Thur 07:30 Fri 07:00 Sat 07:00 Sun 07:00
So depending on the day of the week, the amount to be deducted to calculate extra hours is different, for example, if the hours worked every day was 7h 30m, then the extra hours would vary like this
I want this extra time to be calculated in the same place as the employee fills out their daily time card.
Day of Week Hours Worked Set Hours (as in table above) Extra Time (h:mm) Mon 7:30 7:30 0:00 Tue 7:30 7:30 0:00 Wed 7:30 7:30 0:00 Thur 7:30 7:30 0:00 Fri 7:30 7:00 0:30 Sat 7:30 0:00 7:30 Sun 7:30 0:00 7:30
I have created two tables, one for the daily time card input, and one to hold the set hours.
The only relationship between the two tables is the 'Day of Week' . In the time card table, this is a calculated field using the input date, so I can't join the two tables using this. I have created a query which joins the two sets of data (I think), but I cannot work out how to then put this query back into the Time Card table so that it can shows the extra time for each row in the Time Card table.
These are the two table fields:
Time Card
---------------
Date: dd.mm.yyyy
Start Time hh:mm
End Time hh:mm
Total Hours: End Time - Start Time (hh:mm)
Weekday: WeekdayName([Day of Week],True,2)
Day of Week: Weekday([Date],2)
Set Hours
-----------
Day of Week : Short text (matches to values calculated in Weekday column above
Hours: hh:mm
Query
----------
SELECT [Set Hour].Hours
FROM [Set Hours] INNER JOIN [Time Card] ON [Set Hours].[Day of Week] = [Time Card].[Week Day];
How do I add the result of this query into a field in the Time Card table?