Fields within 3 ODBC Tables

[Alice]

First, I have 3 different ODBC Tables in which I'm pulling
certain fields together to form a Make Table Query.

Second, I would like to add a field within my Make Table
query to include a number that is a result of three
different fields. An Example of the existing fields appear
below:
Type Full Grant Number CAN
01 1 D47 AW005469-01 841116

Third, I would like my new number appear like this:

1 D47 AW0005469-01 1116

Look closely at the number I only need the last four
digits of the field labeled CAN also the second digit of
the field labeled TYPE. Is it possible to design a new
field within a Make Table Query and if so how will I
design my new Grant Number to appear like the above?

I need your Urgent Help!!!!

 

[Earl Lewis]

Alice,

If I read your post correctly this is a table structure vs. presentation
issue, I think. I'll try to explain.

If you have the data in three fields and they're useful the way the are and
you ALSO want to see them the way you described you can do that with a
query. Adding another field that is a derivation of the other three violates
basic db design principles (atomicity).

On the other hand, if you really want the data stored permanently the way
you said and don't ever want,or anticipate a need, to see the lost pieces
you would do it in your make-table query.

Either way you do this with string manipulation. Access has several flavors
of string functions available to accomplish what you want.

Here's an example:
I have users names and I want to create a system login for them. I always
want it to be the first letter of the first name and the full last name. The
expression to accomplish this would look something like this: UserID =
Left(FirstName, 1) & LastName, assuming FirstName and LastName are valid
field names.

Your case is similar. If you want the last four characters of the CAN field
you would do something like NewId = FullGrantNumber & " " Right(CAN, 4).

Again, don't make the mistake of adding a field that is the combination of
these values. Just do it in a query that builds it on the fly whenever you
need it.

Hope that helps.

Earl