Auto lookup Query

[Adrienne]

My supervisor developed the table below. As you can see it
has Common Account Numbers (CANS), type of Research and
the Division along with the Pool and Notes. I also have
an existing table that has the CAN information but
my CAN # looks like this (8460926) it does not display the
other fields - Research, Division ,Pool and Notes. I'm
trying to develop a query from two tables but one table is
sort of like a look up table(table below). In that if the
4 digit CAN # changes per transaction on my report the
query will automatically fill the other fields -Research,
Division, Pool and Notes. I need your detailed help on
how to develop this query. P.S. should I use the table
below in an AutoLookup query ?


CAN RESEARCH DIVISION POOL Notes:
0789 BIOD DAIDS RPG
0926 BIOD DAIT RPG
0927 BIOD DAIT CAREERS
0928 BIOD DAIT FELLOWSHIPS
0929 BIOD DAIT INSTITUTIONAL TRAINING
0930 BIOD DAIT OTHER
0931 BIOD DAIT SREA
0932 BIOD DAIT CENTERS Research Projects & Centers
0933 BIOD DAIT RSUMS
0934 BIOD DAIT BRIDGE AWARDS
0935 BIOD DAIT DISCRETIONARY
0936 BIOD DAIT SELECT PAY
0937 BIOD DAIT EOY SUPPLEMENTS
0938 BIOD DMID RPG
0939 BIOD DMID CAREERS

 

[John Viescas]

It's not clear how the CAN # in your table relates at all to the table you
want to use as a lookup. Are the last 4 digits of your number the same as
the CAN in the other table? If so, it might be possible to link the tables
on an expression, but the query will be very slow. What is the data type of
your CAN # and the data type of CAN in the other table? If only part of
your CAN number matches, is it possible to split this field into two fields?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

Yes the last 4 digits of the CAN # appear in my report as
well as the report below. It's a numeric field. What I'm
trying to do in the query is if for example:

CAN
0789 appears in my table of transaction

it should autofill the

Research column, Division column, Pools column and Notes
column.

Keep in mind my supervisors table

0789= BIOD DAIDS RPG


The table below is used in Excel as a V-Lookup table.
My table has all the transactions within it. What I'm
trying to accomplish is if one of my transactions has the
above CAN (example:0789) it should go out and look up that
CAN and if it does equal 0789 it should auto field the
other fields with the above Research -BIOD, Division -
DAIDS, Pools - RPG, and any Notes associated.

 

[John Viescas]

Are you working in Microsoft Access or Excel? If in Access, is the CAN # in
your table 7 digits or 4 digits? What is the Record Source of the report?
If it is a query, what is the SQL from the query?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Adrienne]

My Make Table Query has all types of fields that represent
a transaction it's in Access the fields are text and
numerical the example of the report below is in Microsoft
Excel. The CAN # is a 7 digit number within my query and a
4 digit number on my supervisors report. My supervisor
would like to have the query go out and look at the
example of the Table below and import it into my table
based on the factors.

 

[John Viescas]

OK. So you create a table in Access using a Make Table query. If the CAN #
is 7 digits in your table and only 4 in your supervisor's table (and where
is that table, anyway - in Excel or Access?), it will be difficult to
construct the lookup. If both tables were in Access and the CAN were the
same size and data type, you could do the "lookup" in a query like this:

SELECT MyTable.*, SupTable.*
FROM MyTable LEFT JOIN SupTable
ON MyTable.CAN = SupTable.CAN

I used an outer (LEFT) join just in case your table has CAN numbers that
don't exist in the supervisor's table.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)