468,283 Members | 1,673 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,283 developers. It's quick & easy.

update/replace field data

Hello,
I'm having difficulty figuring out how to update values in a certain column,
where the new information depends on the previous. Here's a further
description:
The whole database is for insurance purposes.

tbl_Property
ID Name Client Value
------------------------------
1 HouseA Client1 300,000
2 HouseB Client1 325,000
3 HouseC Client2 350,000
4 HouseD Client2 350,000
5 HouseE Client3 400,000
6 HouseF Client3 350,000
7 HouseG Client4 450,000
8 HouseH Client4 350,000
tbl_Client
Client Index
----------------
Client1 1.02
Client2 1.03
Client3 1.01
Client4 1.04

Consider the Index in tbl_Client as the inflation rate of each client's
country. At the end of each year I want to multiply the value of each house
with the corresponding index rate in order to increase the value of the
house and then put that new value into the "value" column (replace the old
value). That way I won't break any relationships between tables, which are
necessary for other calculations.
I hope all was clear. BTW, I'm quite new at using Access. Thanks in advance
for the help!
Nov 13 '05 #1
2 1966
Create a query that includes both tables and join the tables on the Client
field. You only need the Value field in the query. Change the query to an update
query. Enter the following expression where it says Update To under Value:
[Value]*[Index]

Run the query to get what you want. Be careful not to run the query more than
once!!!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Dejan Pujic" <dp****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...
Hello,
I'm having difficulty figuring out how to update values in a certain column,
where the new information depends on the previous. Here's a further
description:
The whole database is for insurance purposes.

tbl_Property
ID Name Client Value
------------------------------
1 HouseA Client1 300,000
2 HouseB Client1 325,000
3 HouseC Client2 350,000
4 HouseD Client2 350,000
5 HouseE Client3 400,000
6 HouseF Client3 350,000
7 HouseG Client4 450,000
8 HouseH Client4 350,000
tbl_Client
Client Index
----------------
Client1 1.02
Client2 1.03
Client3 1.01
Client4 1.04

Consider the Index in tbl_Client as the inflation rate of each client's
country. At the end of each year I want to multiply the value of each house
with the corresponding index rate in order to increase the value of the
house and then put that new value into the "value" column (replace the old
value). That way I won't break any relationships between tables, which are
necessary for other calculations.
I hope all was clear. BTW, I'm quite new at using Access. Thanks in advance
for the help!

Nov 13 '05 #2
thanks a lot for the help! works great :-)
"PC Datasheet" <no****@nospam.spam> wrote in message
news:yQ****************@newsread2.news.atl.earthli nk.net...
Create a query that includes both tables and join the tables on the Client
field. You only need the Value field in the query. Change the query to an update query. Enter the following expression where it says Update To under Value:
[Value]*[Index]

Run the query to get what you want. Be careful not to run the query more than once!!!!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Dejan Pujic" <dp****@yahoo.com> wrote in message
news:40**********************@news.zen.co.uk...
Hello,
I'm having difficulty figuring out how to update values in a certain column, where the new information depends on the previous. Here's a further
description:
The whole database is for insurance purposes.

tbl_Property
ID Name Client Value
------------------------------
1 HouseA Client1 300,000
2 HouseB Client1 325,000
3 HouseC Client2 350,000
4 HouseD Client2 350,000
5 HouseE Client3 400,000
6 HouseF Client3 350,000
7 HouseG Client4 450,000
8 HouseH Client4 350,000
tbl_Client
Client Index
----------------
Client1 1.02
Client2 1.03
Client3 1.01
Client4 1.04

Consider the Index in tbl_Client as the inflation rate of each client's
country. At the end of each year I want to multiply the value of each house with the corresponding index rate in order to increase the value of the
house and then put that new value into the "value" column (replace the old value). That way I won't break any relationships between tables, which are necessary for other calculations.
I hope all was clear. BTW, I'm quite new at using Access. Thanks in advance for the help!


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Joseph | last post: by
reply views Thread by Sue Adams | last post: by
25 posts views Thread by Neo Geshel | last post: by
reply views Thread by NPC403 | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.