Field Automatically

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

John,

Which query should I use and I'm not using Zip Codes I'm
working with 150 CAN numbers that have four different
fields assoicaited with it one table has the CAN Number
the other table has the other four fields that relates to
the CAN Number on the other table that has the 6900
transactions. By the way what is Ziplow and ZipHigh where
do I find this????
 
Is this question directed at me? I seem to recall a thread last week that
had something to do with "CAN" numbers, but you didn't reply to the original
thread, so I have no clue about the context of your question.

--
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)
 
I'm trying to determine your solution below. It seems
like a good idea but I'm not sure the instructions are
clear. When you use the word join tables and make a query
I can JOIN tables based on the Grant Number not the CAN #.
The CAN# in one table is 8 charaters long the CAN# in the
other table is 4 characters long. I want to develop a
query that will look at one table with the transactions
and look at the other table by query and say if:
CAN # 8999 (in table one)= CAN# 75558999 (in table two)
then fill these four columns Research, Pool, Division and
Notes with certain information. Is there a query solution
and which query should I use? Also, keep in mind all data
is coming from linked ODBC's!!!!
 
Please post the definition of the relevant fields in the two tables and a
small sample of the output you expect.

--
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)
 
Table 1 Fields: Table 2 Fields:
Full Grant Number CAN (4 character)
PI Name Research
Total Award Amount Division
Future Year 1 Pool
Future Year 2 Notes
Future Year 3
Future Year 4
CAN (8 characters)
RFA/PA
Appl ID
Co-Fund
PCC
Award IC
Activity
Type


I would like my query to Look at the last for digits of
the CAN # in Table 1 and go to table 2 and find the
corresponding 4 digit CAN# and automatically fill the
columns associated with that 4 digit CAN# the fields are
in Table 2 which include:
Research
Division
Pool
Notes

I have about 6900 transactions in table #1 I'm basically
trying to use Access like a VLook. I understand that
Access is a relational database??? I want my query to go
to table #1 determine the last 4 digits of the CAN# and
then automatically fill the four columns above.
 
"Relational" means that the data is stored in Relations - a technical term
that corresponds roughly to a Table in Access. It also means you can
logically associate data in multiple Relations as long as a Primary Key in
one relation matches a Foreign Key in the second relation -- but the keys
must match.

You're trying to match on fundamentally unmatchable values - a 4 character
field with an 8-character field.

But the good news is it can be done - in SQL view - in Access. The bad news
is the query will probably run very slowly. Assuming the tables are called
"Table 1" and "Table 2" as you have noted below, the SQL is:

SELECT [Table 1].*, [Table 2].*
FROM [Table 1] LEFT JOIN [Table 2]
ON Right([Table 1].CAN, 4) = [Table 2].CAN

--
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)
 
Back
Top