Update one table from another table

[Rick Cochran]

In Access 2000 I have a database with two tables:
(these are tables set up to test the feasibility of what I
want to do, not production tables)

Table "Levels" has three columns: RateName, Rate1, Rate2.
RateName is text, Rate1 & Rate2 are number fields.

Table "Main" has columns ClientName, RateName and
DailyRate. RateName is a combo box and "looks up"
from "RateName" in the "Levels" table. This works fine.

I would like to populate "DailyRate" (in table "Main")
from the sum of Rate1 & Rate2 in table "Levels" and
have "DailyRate" update in table "Main" whenever Rate1
and/or Rate2 in table "Levels" is changed.

Can this be done?

Thanx - Rick from Farmington

 

[Tim Ferguson]

I would like to populate "DailyRate" (in table "Main")
from the sum of Rate1 & Rate2 in table "Levels" and
have "DailyRate" update in table "Main" whenever Rate1
and/or Rate2 in table "Levels" is changed.

Oh no you don't -- and the reason is exactly contained in the second part
of your question. The underlying method of R theory and relational
databases is to avoid storing one item of information twice, because
sooner or later the two instances will disagree with each other.

To put it another way: if you _always_ work out the DailyRate by adding
up the two Rates at the time, then the DailyRate will _always_ be correct
at the time of looking it up. This is what a Query is for, and you should
get into the habit of doing _all_ your database lookups using queries.

Your basic design is fine as it is, with the exception of getting rid of
the Main.DailyRate column. Your query should look something like

SELECT ALL Main.ClientName,
Levels.Rate1 + Levels.Rate2 AS DailyRate
FROM Main LEFT JOIN Levels ON Main.RateName = Levels.RateName

The query designer will do all the fancy "left join on" stuff, but you
should be able to get the picture from that.

Hope that helps


Tim F

 

[Rick Cochran]

Thanks for the quick reply Tim,

I'm thinking that since the rates only change once a year
we could keep it simple and do an update query to mass
update the table. Thanks for pointing me in the right
direction!

Rick from Farmington

 

[Tim Ferguson]

I'm thinking that since the rates only change once a year
we could keep it simple and do an update query to mass
update the table.

Just to complicate things: do you need to "freeze" the DailyRate (etc) of
historic records? It's not much good reporting on 1989 contracts and
treating them as if they are priced on 2004 rates...

All the best



Tim F