Update Query

  • Thread starter Thread starter Adrienne
  • Start date Start date
A

Adrienne

I'm not sure how an Update query works and the
help in Access is hard to follow. I have two tables
Table #1

Table #2


In Table #1 all the transactions are listed and sorted by
different fields:
Account Number (7 digits)
date of Transaction
Transaction amount
Type of Transaction
Program Code

In Table #2

Account number (4 digits)
Reports
Measure
Department

I would like the Update Query to look at the last four
digits in the account number field in table #1 if it
matches the account number is table#2 then automatically
fill or update the fields in table #2 (Reports, Measure,
Department). The query would appear:

If table #1 Account Number (last 4 digits) =
Table #2 Account Number (last 4 digits) then automatically
fill the other fields listed in table 2 (Reports, Measure,
Department).




Table#1 Table#2
0775842 = 5842 = Reports, Measure Department

Is the Update Query the way to go for this sort of action?
Please explain how to use in detail if possible!!!!
 
Just explain what you mean with automatically fill the other fields.With what do you want to fill it?What should show in these fields?
To create the link between these tables follow the following steps.

Create a query using Table#1
Use all the fields and add a filed named Right4:=Right([Table1].[AccountNumber],4)
Save the query e.g. TEST

Create another query using Table1 and the query you saved(TEST)
Link the the Account Number field in Table2 with the Right4 field in query TEST.


Just can't figure out what you want to show in Fields:Reports,Measure, Department.


----- Adrienne wrote: -----



I'm not sure how an Update query works and the
help in Access is hard to follow. I have two tables
Table #1

Table #2


In Table #1 all the transactions are listed and sorted by
different fields:
Account Number (7 digits)
date of Transaction
Transaction amount
Type of Transaction
Program Code

In Table #2

Account number (4 digits)
Reports
Measure
Department

I would like the Update Query to look at the last four
digits in the account number field in table #1 if it
matches the account number is table#2 then automatically
fill or update the fields in table #2 (Reports, Measure,
Department). The query would appear:

If table #1 Account Number (last 4 digits) =
Table #2 Account Number (last 4 digits) then automatically
fill the other fields listed in table 2 (Reports, Measure,
Department).




Table#1 Table#2
0775842 = 5842 = Reports, Measure Department

Is the Update Query the way to go for this sort of action?
Please explain how to use in detail if possible!!!!
 
Table #2 in my example is like a VLook Up table in excel
The (4 digit) Account Number looks at the Account Number
in Table #1 if they match then it automatically fills the
other fields with the report code field, the Measure field
and the Department field. In other words, Table # 1 only
has the Account Number for each transaction. Each Account
Number has it's on Measure, Department, and Reports just
they appear in Table #2. Look at the example below

Table #1 (Transaction Table has all the transaction)

Account#(Table#1) Account#Table#2
5842 = 5842=FCAI(department)
=BIO(Measure)
=AP(Reports)

I would like to see Table #1 link to table#2 and add the
above fields next to it. So table #1 would look like this:

Account # Department Measure Reports
5842 FCAI BIO AP

Currently, Table #1 only shows each transaction and only
the Account # per transaction. Keep in mind I have 150
different account numbers based on 4,000 + transactions.
I do not want to manually enter each Department, Measure
and Reports per transaction!!!! I thought I could use
some type of query
to do a look up from one table to the next based on the
account number and automatically fill in the above
additional fields.

-----Original Message-----
Just explain what you mean with automatically fill the
Click to expand...
other fields.With what do you want to fill it?What should
show in these fields?
To create the link between these tables follow the following steps.

Create a query using Table#1
Use all the fields and add a filed named Right4:=Right ([Table1].[AccountNumber],4)
Save the query e.g. TEST

Create another query using Table1 and the query you saved (TEST)
Link the the Account Number field in Table2 with the Right4 field in query TEST.


Just can't figure out what you want to show in
Click to expand...
Fields:Reports,Measure, Department.
 
Back
Top