Best way to lookup data from another table?

[Guest]

I have a Table called "Work Order Log", I currently have a 'lookup' for a few
fields when entering new records, such fields as "Customer" from the
"Customer" table.

Now that I am trying to develop reports based a single record these 'Lookup'
fields are really making things difficult. I can not seem to retrieve that
data that I want.

Your help is greatly appreciated

 

[Rick Brandt]

I have a Table called "Work Order Log", I currently have a 'lookup'
for a few fields when entering new records, such fields as "Customer"
from the "Customer" table.

Now that I am trying to develop reports based a single record these
'Lookup' fields are really making things difficult. I can not seem to
retrieve that data that I want.

Your help is greatly appreciated

Wow, twice in one hour. See info at this link..

http://www.mvps.org/access/lookupfields.htm

 

[Guest]

Thanks for the quick response, in the first post I asked about the problem I
was having and you kindly directed to the the site you suggested. In the
second post I asked of a different way to incorporate these fields, and you
again directed me to the exact same link. Which tells me of the evils of
"looking up" fields but offers absolutley no suggestions on how to implement
a better technique.

Thanks Again for your help

 

[Rick Brandt]

Thanks for the quick response, in the first post I asked about the
problem I was having and you kindly directed to the the site you
suggested. In the second post I asked of a different way to
incorporate these fields, and you again directed me to the exact same
link. Which tells me of the evils of "looking up" fields but offers
absolutley no suggestions on how to implement a better technique.

Step 1) eliminate the lookup fields from your tables.

Step 2) in your report use a query that uses BOTH the base table and the table
where the lookup values are stored (joining on the appropriate field). Then you
can pull the desired data from the lookup table.

If you aren't using a lookup table but instead are using value lists in your
tables then you typically need to use expressions on your report based on
Choose(), IIf(), or Switch() functions that can translate the ID values into the
text that you want to see instead.