Calculated Query Field

[Guest]

Hi,

I've tried figuring this out before but ended up using a report with unbound
boxes. However I would like to make a new query. Basically I want to
develop a query that will calculate how much leave an employee has taken and
then using their leave entitlement field I would like to calculate how much
leave they have remaining.

Now I can get a query to display the Leave Entitlement and the Sum of Hours
Taken however I need to somehow calculate these two fields in order to get
the amount of leave remaining. I.e.

Leave Entitlement - (Sum of) Hours Taken = Leave Remaining


Both fields are Double number data types.

I would appreciate any advice, many thanks

Ian

 

[Wayne Morgan]

Since you haven't stated where these numbers are located, I'm going to
assume that leave taken and leave entitlement are in two different tables.
If so, you could use a DSum() function to get the sum of hours taken and
subtract that value from the Leave Entitlement. You would need to use
criteria on the DSum() statement to limit what you're summing to the time
frame desired and to the employee whose record you're currently on. What
this DSum() statement will look like will also depend on how your storing
(i.e. the format of) the leave taken. For example, if you are storing taking
1.5 hours as 1+30, you'll need to change this into a number that can be
summed.

 

[Guest]

The leave entitlement is stored in the staff table. I do not store the sum
of hours taken as this is a calculated field which I only need for this
reason. I thought it would be a diff calculation between Leave Entitlement
and the sum of Hours Taken.

Ian

 

[Wayne Morgan]

Ian,

I understand you're not storing the sum, the comment is that there are many
formats in which to store times and time intervals. Not all of the formats
readily lend themselves to doing calculations.

To subtract the sum of hours from the leave entitlement, the format will
need to be something that VBA can calculate. One thing to remember is that
an elapsed time, such as the amount of leave taken is not a "time". A "time"
is a particular point in time, such as 1 pm. The time between 1 pm and 4:30
pm is 3.5 hours. While you may want to display this as 3:30 for three hours
and 30 minutes, 3:30 is either 3:30 am or 3:30 pm, not an elapsed time of
3.5 hours. Once you have the total time taken in a format that you can use
in an equation, such as 3.5 hours or 210 minutes instead of 3 hours and 30
minutes, you can then subtract that value from the time allotted. I
recommend that you do all of your calculations in the smallest increment of
time that you need then format it back to how you want it displayed. For
example, if the employees can take leave as parts of an hour, then I would
recommend doing the calculations in minutes and format it back to hours and
minutes later when you're done. This makes the math simpler and prevents
floating point round-off errors.