Big Big Query Problem!!??

[Vo0do0uk]

i have a tblemployee with hourly wage in.

i have a query which takes the Avg of all these wages.

This is used for my employee rota DB to work out labour % at the end of the night. so this looks at cost of labour against gross profits.

However i've just relised a flaw...

Say on monday my Avg labour hourly wage was £7 this will be calculated for monday and return all the correct values..

my problem however is if i then employee MR X on tuesday and because he's expensive my avg hour wage goes up to £7.50!!

so... when i (or the client) whishes to review mondays financial details the've all changed!! because of the extra 50p!

i need it to stay the same, so i still want monday to show £7, but now i need tuesday to reflect the extra rise in salary and show £7.50?!

any ideas??

my only thought was to append the old information into a new table that won't have any query's attached it'll just show the data.. would this work?? or would this still update with the new rise in hourly wage.

please advice, can zip up examples if required

 

[Kodo]

if you employ a guy on tuesday, how does it affect monday? Your query should be date specific off of the days the person worked.

 

[Vo0do0uk]

why would it?

my hourly wage is coming from my employee table, it's used in a query for my finances. so when i bring up the query to show all the finances it will update the avg salary everyday!

you post doesn't make sense dude!

 

[Kodo]

I understood your post to say that you would take the avg pay of any given day of the week. So if joe and bob worked monday at 7 pounds/hr then you would query for THAT monday's date to find that avg wage for that date only; and then jane comes in on tuesday and you would find that avg wage for that date only, or am I missing something.

 

[Vo0do0uk]

not sure how i'd do it by day!

i just have a single query on the hourly wage column of my tblEmployee which returns a single value say £7

this query is then combined with another query so i can tie in the avg wage to compute my daily figures

could you suggest how i would do this on a daily basis? sounds very complicated!!

 

[Kodo]

not very complicated. Build your query by joining the employees table on the table that tracks the hours/dates worked by employeeID.