Updating Table with another tables values

[Guest]

Hi,

I want to update the fields in column "Fund Value" with the "Account Value"
in another table.

Here are the tables.

Table 1:
There are 5 columns and 75000 rows
Policy Number; Fund Value; Issue Date; Age; Date of Birth

Table 2: there are also 5 columns (except with Account Value) with 3000 rows
Policy Number; Account Value; Issue Date; Age; Date of Birth

I want to update Table 1, and replace the "Fund Value" with the "Account
Value" from Table 2.

The "Policy Number" is the field that links the two tables -

This summarizes what I want to do - for every row in Table 1 with the same
"Policy Number" as Table 2, just replace the "Fund Value" in Table 1 with the
"Account Value" from Table 2.

Thanks for your help.

 

[storrboy]

Hi,

I want to update the fields in column "Fund Value" with the "Account Value"
in another table.

Here are the tables.

Table 1:
There are 5 columns and 75000 rows
Policy Number; Fund Value; Issue Date; Age; Date of Birth

Table 2: there are also 5 columns (except with Account Value) with 3000 rows
Policy Number; Account Value; Issue Date; Age; Date of Birth

I want to update Table 1, and replace the "Fund Value" with the "Account
Value" from Table 2.

The "Policy Number" is the field that links the two tables -

This summarizes what I want to do - for every row in Table 1 with the same
"Policy Number" as Table 2, just replace the "Fund Value" in Table 1 with the
"Account Value" from Table 2.

Thanks for your help.

Look in the help files for how to construct Update queries. (Trying to
help you learn).
If you can't figure it out, or need help understanding the help files
- post back.

 

[Guest]

Starboy,

In update query the criteria is that the policy number must match in both
tables. Is there a way to do this in Update Query. I was under the
impression that you could not.

 

[storrboy]

Starboy,

In update query the criteria is that the policy number must match in both
tables. Is there a way to do this in Update Query. I was under the
impression that you could not.

If the fields are the same (ie. primary - foreign key), then a join on
that field in the query does it for you.
In design view show both tables, drop the table1.[Fund Value] field
into the design grid, set the Update To line to Table2.[Account
Value]. In the top portion of the screen, if there is not a line
between the two tables then drag the table1.[policy number] field over
the same field in table2. This will create an equal join on that
field. Any record that matches will update to the table2 value.
However unmatched records will need to be dealt with separately.

 

[Guest]

The fields are not the same. That was the whole problem, and that is why the
update query was not working. There are 3000 unique policy numbers in table
2 and 75,000 unique policy numbers in table 1, and I only want to update
those fields in table 1 with the same policy numbers as table 2.

Starboy,

In update query the criteria is that the policy number must match in both
tables. Is there a way to do this in Update Query. I was under the
impression that you could not.

If the fields are the same (ie. primary - foreign key), then a join on
that field in the query does it for you.
In design view show both tables, drop the table1.[Fund Value] field
into the design grid, set the Update To line to Table2.[Account
Value]. In the top portion of the screen, if there is not a line
between the two tables then drag the table1.[policy number] field over
the same field in table2. This will create an equal join on that
field. Any record that matches will update to the table2 value.
However unmatched records will need to be dealt with separately.

 

[Guest]

There are 75,000 policy numbers (no repeats) in table 1 and 3000 policy
numbers in table 2 (no repeats). All 3000 policy numbers in table 2 are in
table 1. You cannot do a simple join because that would eliminate (75,000 -
3000 = 72,000 policies)

That was the problem with the update query, I want to update 3000 of the
75000 rows in table 1, not create a new table with only 3000 rows. Do you
know how to do this?

Starboy,

In update query the criteria is that the policy number must match in both
tables. Is there a way to do this in Update Query. I was under the
impression that you could not.

If the fields are the same (ie. primary - foreign key), then a join on
that field in the query does it for you.
In design view show both tables, drop the table1.[Fund Value] field
into the design grid, set the Update To line to Table2.[Account
Value]. In the top portion of the screen, if there is not a line
between the two tables then drag the table1.[policy number] field over
the same field in table2. This will create an equal join on that
field. Any record that matches will update to the table2 value.
However unmatched records will need to be dealt with separately.

 

[storrboy]

There are 75,000 policy numbers (no repeats) in table 1 and 3000 policy
numbers in table 2 (no repeats). All 3000 policy numbers in table 2 are in
table 1. You cannot do a simple join because that would eliminate (75,000 -
3000 = 72,000 policies)

That was the problem with the update query, I want to update 3000 of the
75000 rows in table 1, not create a new table with only 3000 rows. Do you
know how to do this?

If the fields are the same (ie. primary - foreign key), then a join on
that field in the query does it for you.
In design view show both tables, drop the table1.[Fund Value] field
into the design grid, set the Update To line to Table2.[Account
Value]. In the top portion of the screen, if there is not a line
between the two tables then drag the table1.[policy number] field over
the same field in table2. This will create an equal join on that
field. Any record that matches will update to the table2 value.
However unmatched records will need to be dealt with separately.

An Update query will not create a new table. If the policy number
field is set to No Duplicates, then the query would only update the
3000 that match. It's the same as saying WHERE table1.[policy number]
= table2.[policy number]. If it's not set to No Duplicates then use
the WHERE statement. Remember Policy Number is not in the design grid
- otherwise the query will want to update that - it's only there for
the join or criteria.