What type of Query and How

  • Thread starter Thread starter Allison
  • Start date Start date
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.
 
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!
 
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
 
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!
 
Back
Top