What type of Query and How

A

Allison

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.
 
J

Jeff Boyce

Allison

Don't go there! If you already have a table with the "lookup" values, and a
way to join the tables (by the code/account#), there's no reason you need to
put redundant data in your database. Use a query, instead, to SHOW the
other values associated with the code/#.

If you DO put redundant data in, what is your plan for ensuring
synchronization among the different copies of the "same" data? You'll need
one!
 
A

Allison

Jeff,

For some reason your suggested join does not work?????
I trimmed the account#'s down to be the same
when I tried to join the select query with my table
I receive an error message: (type mismatch in expression).
Keep in mind I'm working with a select query and a table.
The field labeled Account# is a combination of two fields
inside my query but the Account# in the table is the raw
#. Again please give step by step instructions on how to
accomplish this?? If the join does not work then I can't
fill the other columns.

Select Query Table
Join
New Account# ----------------------- Account #
combination of two fields) Reports
Total Award Measure
Division Department

Look at query and if the transaction = Account# 5698
then fill the other columns with this information etc.
Reports - ARP
Measure - PCC
Department - AP (I have 150 different account numbers)

Allison
 
J

Jeff Boyce

Allison

For a join to work, the fields have to be of like type (and hold the same
values). If you don't have a field in each "table" that is of like type and
values, you only get garbage when you try to query/join.

Are you saying that you are using the same field name (Account#) to mean two
different things? Apples and oranges!
 
Top