Lookup table question...

[Beringer]

I have created tables that use the "Lookup Wizard" as the "data type" for
some columns in a record. This is convienent because instead of getting a
number (key) that means nothing, the cooresponding text/name/etc is
displayed when the tables data sheet is opened. In the example below,
[Brandname], [Strength], and [Units] are all Lookup results in the dataset.

The problem I'm having is that I would like to create a calculated text cell
using that lookup result. When I concatenate that cell to the string I get
the number(key), not the text which is the result of the lookup.

For example my calculated cell syntax is as follows:
Drug:[Brandname] & " " & [Strength] & [Units].
What I get in the calcualted cell is something like:
"1 1214". Which is simply the keys all concatenated together.
What I want is: "Amoxil 250mg"

My question is, "Is there a simple way to utilize the lookup result to
create the a string like what I want and not get a string of numbers?" I
have found a way to make it work, but my method essentially doubles the
number of columns in my datasheet. What I end up doing is creating another
column that performs a DLookUp function using the key in say [Brandname] for
the criterion. I'm hoping there is an easier way.

Thanks in advance,
Eric

 

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/lookupfields.htm at "The Access
Web" to see what most of us think of lookup fields...

What you need to do is create a query that joins your table to the lookup
table, so that rather than using DLookup repetitively, you get the value
"for free" from the query.

 

[fofa]

Basically a LookUp table contains the information you want. The mai
table holds a key (pointer) to the data you want. Lets take a query a
an example.
Select L1.BrandName & " " & L2.Strength & L3.units as Drug
From MainTable as MT
Inner Join Lookup1 as L1 on MT.L1KEY = L1.Key
Inner Join Lookup2 as L2 on MT.L2KEY = L2.Key
Inner Join Lookup3 as L3 on MT.L3KEY = L3.Key
Where blah blah blah

So you tell it to join the tables on the keys, nad pull the readabl
human data from the lookup tables