If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update one table from another table
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 |
#2
|
|||
|
|||
"Rick Cochran" wrote in
: 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 |
#3
|
|||
|
|||
"Rick Cochran" wrote in
: 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
"Rick Cochran" wrote in
: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update a query to value in another table | Tim | Running & Setting Up Queries | 1 | August 13th, 2004 11:35 PM |
Fields Calculations & Update table | Faio | General Discussion | 0 | August 10th, 2004 11:23 AM |
Update two subforms based on one table together? | Flora | Using Forms | 0 | August 6th, 2004 12:59 AM |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |