Access help needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Excel, you can modify one table based on another table by using Vlookup.
This is beneficial when you have a huge table of customers and a select few
needs to be updated. (Sort of a copy and paste based on a formula for each
cell)

Can you, in Access, do the same thing? I have a table/form with roughly
2000 customers. Each month, a seperate program does a run to display which
customers are 'active', or had a change in their information.

Table 1 (Main)
ID Name Salesperson Credit Limit Terms
1 Joe Tom B. $100 CIA
2 Bob Tom B. $0.00 N30
3 Sue Eric M. $0.00 N30
4 Pat Tom B. $150 CC


Update shows these changes:

ID Name Salesperson Credit Limit Terms
1 Joe Tom B. $100 CIA
2 Bob Mary C $0.00 N30
3 Sue Eric M. $200 CIA
4 Pat Tom B. $500 COD

*Not every customer had a change, some changes were in multiple fields. Is
it possible in Access, to take the ID as the primary key and do "replace" or
"update" some how with the main table?? **Without having to change each
thing one at a time?

Thanks for any help.
 
In Excel, you can modify one table based on another table by using Vlookup.
Click to expand...

In Access, you can do the same using an Update Query.
This is beneficial when you have a huge table of customers and a select few
needs to be updated. (Sort of a copy and paste based on a formula for each
cell)
Click to expand...
Can you, in Access, do the same thing? I have a table/form with roughly
2000 customers. Each month, a seperate program does a run to display which
customers are 'active', or had a change in their information.

Table 1 (Main)
ID Name Salesperson Credit Limit Terms
1 Joe Tom B. $100 CIA
2 Bob Tom B. $0.00 N30
3 Sue Eric M. $0.00 N30
4 Pat Tom B. $150 CC


Update shows these changes:

ID Name Salesperson Credit Limit Terms
1 Joe Tom B. $100 CIA
2 Bob Mary C $0.00 N30
3 Sue Eric M. $200 CIA
4 Pat Tom B. $500 COD

*Not every customer had a change, some changes were in multiple fields. Is
it possible in Access, to take the ID as the primary key and do "replace" or
"update" some how with the main table?? **Without having to change each
thing one at a time?
Click to expand...

Certainly. Create a Query joining the two tables by ID. Change the
query to an Update Query. Update [Salesperson] to

[Update].[Salesperson]

and the same with the other fields. This will simply overwrite
whatever's in the fields with the value from the Update table, even if
the values are the same... not sure if that's what you want!

John W. Vinson[MVP]
 
Back
Top