Update only one field with info from another table

[Guest]

I would like to update one field in my table with info from another table. I
have 3 primary keys. Can someone please help? I am a novice at Access.
thank you!

 

[Guest]

You need either an append or update query: Create a query in design view
from where the main info is. Let's say you want to add Table1.Field1 to an
existing record in Table2.Field1A.

Crete a select query with Table2. At the top of the select query window
(when in design) is a drop down box that lets you choose the type of query --
choose update. In the Update to: row add which field from talbe one you want
to paste in. The the seond column add a field to to use as a where clause
(e.g. Table1.ID = Table2.ID)

The append query will add a whole new record so you would create the select
query with Table1, change the query type to append -- it will prompt for
which table to append to, then choose the correct field to match the data up
to.

Hope this helps Ian.

 

[Guest]

Hi Diane,
I'm right across the big muddy river from you!

Anyway, it's usually not a good idea to update data from one table with
information in another. Often what you need to do it link the two tables
together so to get the information displayed in a query.

You say that you have 3 primary keys? Is that one primary key per table or
three fields that make up one PK in a table?

We'll probably need a lot more information about your tables and business
needs.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez. I teach Access over at SWIC.
;-)

 

[Guest]

Hey, neighbor! thanks for responding

Actually, it's 2 primary keys: job # and line #. And they are for the whole
table. I get shipping reports and they show the details by Job# and Line#
(there is always more than 1 line per job, which is why I need both keys to
be primary).

Each week I get these shipping reports. Some shipments show up on 2-3 weeks
of reports, and each week they have updated pricing. So, I want to update
the previous week's table with the pricing information from the upcoming
week's table. Do you have any sugggestions?

And thanks for the book recommendation! I will look for it.

Thanks again,
Diane

 

[Guest]

UPDATE ShippingReportUpdate INNER JOIN ShippingReport
ON (ShippingReport.[Line #] = ShippingReportUpdate.[Line #])
AND (ShippingReportUpdate.[Job #] = ShippingReport.[Job #])
SET ShippingReport.Pricing = [ShippingReportUpdate]![Pricing];

You need to join the two key fields in the QBE grid and bring down the
pricing field that you want updated. Then go up to Query and select Update
query. In the Update To field you need to put the table and field name where
you want to get the new pricing data.
[ShippingReportUpdate]![Pricing]

Now if I'm reading into things correctly, it looks like you have a table for
each week. If so, that could be a real problem as it could become very hard
to manage. Instead you should have a Date/Time field with the date for that
week. That would be the third field in the primary key or unique index. You
may need to add this data in the weekly shipping report so that you just
update the correct data.

Now if you need to keep track of what happened to the prices on a certain
week in the past, that brings up a bunch of different problems.

Whatever you do, make a backup before trying any of my ideas in case things
go wrong.