By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

update/replace field data

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.