Same problem!

[Rea Griffiths]

I have what appears to be the same problem, and I am
pulling my hair out. When I create a query to pull
information from seperate tables, any of the fields that
are lookup fields only display the ID number, not the next
field, the one I need to see. When I remove the ID from
the "show" in the query, it gets ugly. I have looked at
the Row Source Information from the original table and
sometimes they look like this:
SELECT [ID] AS xyz_ID_xyz, [PersRate] AS
xyz_DispExpr_xyz, [PersRate] FROM [tblPersRate] ORDER BY
[PersRate]
How do I make the data after the look up appear?

Thanks,

Rhea

 

[Michel Walsh]

Hi,


"Lookup" fields ( I assume you mean a "data type" being a "lookup") is,
in reality, a value that is automatically PRESENTED to something else, such
as an employee number reformatted to present the name of the employee,
instead of the employee number... but the field is really the employee
number. If you include it in a query, you would get the employee number, not
the name of the employee. To get the employee name, you have to select it
from the associated table (or, luckily, from the same table), through an
inner join:


SELECT employee.ID, personal.Name
FROM employee INNER JOIN personal
ON employee.ID=personal.ID


as example.



Hoping it may help,
Vanderghast, Access MVP

 

[John Vinson]

I have what appears to be the same problem, and I am
pulling my hair out. When I create a query to pull
information from seperate tables, any of the fields that
are lookup fields only display the ID number, not the next
field, the one I need to see.

That's because Microsoft's misdesigned, misleading, and all but
useless Lookup Wizard has deceived you (and thousands of other
people).

The table CONTAINS THE ID NUMBER. This simple fact is concealed from
your view by the idiotic Wizard feature.

When I remove the ID from
the "show" in the query, it gets ugly. I have looked at
the Row Source Information from the original table and
sometimes they look like this:
SELECT [ID] AS xyz_ID_xyz, [PersRate] AS
xyz_DispExpr_xyz, [PersRate] FROM [tblPersRate] ORDER BY
[PersRate]
How do I make the data after the look up appear?

By using Access as a relational database. Create a Query joining your
table to the lookup table by ID; pull the information you want to see
from your main table; pull the looked-up value from the lookup table.
You do NOT need the "lookup" feature to do this, and (as you can see)
having the lookup feature in your database makes it harder to discover
how to create relational queries correctly. That's just one reason
that I hate them!