Linking table to query

[Edgar Thoemmes]

I am designing a db to store my hours worked for an
organisation. My hourly rate changes quite regularly so as
well as a time sheet table I also have a hourly rate table
with the following columns Rate ID (pk), Amount per hour,
date commen, date finish.

I also have a query which works out the total hours i work
multilplied by hourly rate. I can get this to work with a
static hourly rate but I would like to link this to hourly
rate table - could anyone give me any advice on how to do
this?

TIA
Edgar Thoemmes

 

[[MVP] S.Clark]

Sometimes, it's good to denormalize data, especially when point-in-time(PIT)
info needs to be retained. Anything that deals with prices falls into this
category. The normalized alternative is to store a start and enddate of the
hourly rate, and then try to deduce it through queries.

Going the non-normalized route requires adding the hourly rate to every line
item entry, but it pays off in that there is no detective work required to
perform the linking later when reports are needed.

Otherwise, your rates table is going to have to store date and hour start
and end time, then perform more links in the query. Not fun.