I want to display a lookup table text, not value, in a query

[hermmy]

Hi,

I am querying a table with lookup tables associated to it and I want the query to display the text and not the value from the lookup. How do I do this?

I notice that sometimes the text will show up in a query while other times the values show up in a query. Why?
Note, for the queries I am making, I am pulling together multiple tables together, not just the look up tables with the main table - is this one of the reasons for the difference?


THANKS

 

[CJ_London]

Not a good idea to use lookups in tables, it leads you down the wrong path and creates confusion.

to answer your question you need to include the lookup table in you query

 

[hermmy]

Yes I am realizing this. My initial database teachings told me to use them so as to reduce repetition, but they end up a nightmare. Any way to stop using look up tables and have the data I desire (text) just live in the table?

I am using the lookup table in the query and it does not change the result. Furthermore, in some of my queries, the text indeed does show up for some of my fields that rely on a look up table BUT for other fields that rely on the same lookup table, I only get the values. I can't figure it out.

Thanks!

 

[CJ_London]

I am using the lookup table in the query and it does not change the result.

you need to include the column from the lookup table that is displayed in your lookup field

Any way to stop using look up tables and have the data I desire (text) just live in the table?

just stop but as Pat says to display the looked up text use a form. tables are for storing data in it's raw state (no lookups/multivalue fields or formatting, and no calculated fields either, they have limited abilities and should be done in a query)

 

[hermmy]

Thanks everyone, I need to rethink how I am doing all of this

If your forms and reports already use list/combos, they won't be affected by removing the table level lookups. Only the queries will be affected. You can change any query that actually needs the text to do a left join to the lookup table.

So, if I go into my database now, which is already populated with data, and I remove the lookup info for the fields that pulled from lookup tables, that the data would be stored (the #s) with no data loss?

Would it be possible to show your example in an access db? i.e. to show the tables, associated form that uses the combo boxes, and the query that gets the text output instead of the ID?

Thank you!

 

[hermmy]

Thanks Pat - I will give this a try and write back if any questions come up. I appreciate that you spelled this out! ,