Storing data from query (1 Viewer)

[Elmobram22]

Hi,

I have a query with a sum that works out a persons annual leave based on the days in the year, the days they have worked and the average weekly hours they do. This can change throughout the year so I have a stepped process that would work out the total before the change and the total after. I have hit a stumbling block as this can change more than once and when this does happen I can't use the first total as it re-queries. What I would like to do is whenever a change is made and the first query runs, that it would store that total in a field within the table. Then when I do a second change I can add that total to the second query (held in the table). If that makes sense? Please let me know if not and also I will try and make a graphical representation of what I mean to explain further...

Cheers,

Paul

 

[Elmobram22]

Here is a visual representation of what I mean...

 

[MarkK]

In your data you should store the facts of events as they occur. Then, do calculations on those facts as required. It is unusual to store the results of calculations.

Consider someone's date of birth and someone's age. One is a fact, one is a calculation. If you store the calculation, it is just a matter of time before your stored data is factually incorrect.

So store the facts only, and perform calculations spontaneously and as required.

 

[Elmobram22]

I know what you are saying but basically when a new calculation happens I need to store the previous as a fact that doesn't change. If you imagine X + Y = Z, X would need to be stored so the next time you can equate (X) + X + Y = Z then add X+X to make the singular X for the next time.

 

[Minty]

It's easy to write down but not so easy to allow for.
Where and how are you storing the date that the change occurs on?

 

[MarkK]

But imagine you have a function Fx(D) that calculates X based on the date D, and say that is the result (X) you are talking about saving. So, instead of saving it you can calculate the whole shebang, like . . .

Fx(D) + Fx(Now()) + Y = Z

Doesn't that make sense?

 

[Cronk]

Elmobram22

What is being suggested to you is that you store the Date at the start of the period, with the number of hours, and the Date when there is a change in the number of hours, and the new number of hours

WorkDate Hours
1 Jan 2015 40
15 May 2015 38
19 Aug 2015 42

 

[Elmobram22]

Hi,

Thanks for all your replies, I'll explain further. I have a table (TblStaff) that holds the staff member details, Another table (TblStaffShifts) has a foreign key of the staff member ID and has a start and end time for the shifts (this is required to figure out breaks based on clock in and out times). A query then calculates four weeks worth of shifts and divides by 4 to get a weekly average of a persons hours. The equation to work out the leave is based on average weekly hours x 5.2 (weeks holiday) x days worked in year / days in year

eg 37.75 x 5.2 x (365/365) = 196.25 (The answer is worked out to the nearest quarter of an hour)

I save a start date and end date for staff but as I say this can change if they change their hours. The easiest way I can figure this equation out is to save the total leave on the first query to TblStaff and then add that on each time. As it reccurs every year and the amount of days in the year can change I don't want to over complicate it with multiple dates because it works the shifts out based on the start and end dates but then if their shifts change the equation won't work going backwards. I don't know if that makes any sense but I hope so haha. I was thinking just appending the total leave of the first query to the table in the leave column and then adding to it each time.

Cheers,

Paul

 

[MarkK]

A query then calculates four weeks worth of shifts and divides by 4 to get a weekly average of a persons hours.

Why 4 weeks? What is sacred about that time period? Why can't you modify that query to return the average weekly hours for any time period?

AvgWeeklyHoursForPeriod = HoursForPeriod / WeeksInPeriod

You can accurately make such a calculation for a period that is 0.5, or 2 or 15 weeks long.

 

[Elmobram22]

We have a four weekly rota for staff. Some shifts may not be the same every week but we do 28 days that are used for their rota.

 

[Elmobram22]

I'm also not suggesting I save a calculation. Just the answer to the calculation.

 

[Elmobram22]

No more suggestions on this?

 

[gemma-the-husky]

the best thing to do, in my opinion is not to store the entitlement, and to work it out as you need it.

if you still feel it's worth storing, then just store it in a field in the employee table

store the TOTAL entitlement.

if you can calculate the total on pen and paper, then you can duplicate that formula within vba. you do not need to store intermediate calculations within a database field.

you must be able to easily calculate the holiday taken from other information.