Update Query

[Allison]

I have two tables in Access. I want one table to look at
the other tables field called Common Account Number example
(0591) and automatically update 4 other fields entitled
Research, Division ,Pool and Notes based the different
transactions. It's like a VLook in Excel where you have
one table with 6,900 transaction but the account number
changes per transaction so you go out to a VLook up table
and it imports based on the CAN # the other 4 fields
needed. There are 150 different occurances based on 6,900
transactions. All I want the query to do is if it's this
CAN# go to the CAN table and automatically update the
other fields on the transaction table. Can someone explain
how to develop a query???

 

[John Vinson]

I have two tables in Access. I want one table to look at
the other tables field called Common Account Number example
(0591) and automatically update 4 other fields entitled
Research, Division ,Pool and Notes based the different
transactions. It's like a VLook in Excel where you have
one table with 6,900 transaction but the account number
changes per transaction so you go out to a VLook up table
and it imports based on the CAN # the other 4 fields
needed. There are 150 different occurances based on 6,900
transactions. All I want the query to do is if it's this
CAN# go to the CAN table and automatically update the
other fields on the transaction table. Can someone explain
how to develop a query???

Well... I'll be glad to help you develop a query, but I'd suggest that
you reconsider your table structure. ACCESS IS NOT A BIG VERSION OF
EXCEL. It's a relational database, and one basic principle is that you
should avoid ever storing data redundantly. If you can store the
values of Research, Division, Pool and Notes once and once only, and
find it using the CAN number, then your Transactions table should
contain the CAN number *ONLY*; it *should not contain* the Research,
Division, Pool, or Notes fields, *at all*.

Instead, create a new Query in the query design window. Add your
Transactions table and the CAN table; join the two by CAN (unless
Access has already done so for you). Select the transaction fields
from the Transaction table, and Research, Division, Pool and Notes
from the CAN table.

This Query can now be used on a Form, as the basis for a Report, even
as the basis for another query.