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!

 

[Pat Hartman]

No. Lookup fields on tables - bad. Lookup fields on forms - good.

Only YOU look at raw tables and queries so you either get used to looking at the numbers or you always open a query with a join to the lookup table if you want to see the text.

Do NOT store the text. Store the ID. 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. Probably the only query you would need to modify would be a query you are using to export data to Excel or another app.

I can't figure it out.

Neither could Microsoft. There are inconsistencies and that is one of the primary reasons for not using table level lookups. They are a crutch. They are only useful until you get to the point of using queries and writing VBA. Then you run into all the bad things. They are only for people who are not capable of creating a query.

 

[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)

 

[Pat Hartman]

For example, to show a Customer Address on an Order form:

Select tblOrders.*, tblCustomers.Addr
From tblOrders Inner Join tblCustomers on tblOrders.CustID = tblCustomers.CustID;

The Customer Name is what will be visible in the combo so you don't need it also.

 

[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!

 

[Pat Hartman]

you can do this to see your own data. Me taking the time to create a database for you to show this example is not a good use of my time especially since it will be MY tables and data rather than yours so you still might not understand the results.

MAKE A BACKUP BEFORE MAKING ANY STRUCTURAL CHANGES OR RUNNING ANY ACTION QUERIES FOR THE FIRST TIME.

Turn off the lookup for ONE of the tables.
When you open the table, you will see the id rather than the text
Create a query. Select this table and the lookup table. Draw the join line to the lookup field. Select the text value from the lookup table. Voila,
The query now has BOTH the ID and the text. Your lookups on forms/reports will all still be bound to the ID field. The difference is that now YOU know what you are actually looking at.

Open your forms and reports that use this field and you will see they still work.

I recommend that you NEVER format any column in a table definition. All that does is obfuscate the data and confuse you. I can't begin to tell you how many people have managed to get time into their date field because they used Now() rather than Date() but formatted the field as short date so they NEVER SAW the time when they opened the table and therefore couldn't figure out why their query was not working as they expected.

 

[hermmy]

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