Display on Forms of Query vs Table lookups

[arumbus]

Hi - brand new here and would appreciate any help.

I am using Access 2012. I have a table (Character) where I have numerous look up fields. A few from other tables but most from Queries. In both cases I am storing the unique ID . When I created a form (automatically) it displays query lookup differently from those based on tables. For the lookups based on a table it always show the lookup not the ID (this is what I want).

Character_Type for example shows "NPC".

But the lookups based on a query it displays the ID not the lookup (unless you click the arrow).

Name_Prefix for example shows "7" not "King"

Is this normal? I want the query lookups to display like the Table looks ups do.

Thank you, in advance, for you advice.

Tried to display an image here or a link but evidently I do not have enough posts hope the words are clear enough.

 

[spikepl]

In Forms/Reports OK. In tables, forget lookups: evils of lookups https://www.google.com/search?q=evi...s=org.mozilla:en-US:official&client=firefox-a

One of the reasons not mentioned specifcially is the exact confusion you are experiencing, that a query on tables using lookups shows one thing in one instance, but when used in other context it shows another.

 

[Alansidman]

You should not be doing lookups in your tables. It is not a good database development practice.
Read here:

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

You should be storing the IDs of the values in your tables and not the description value.

Read here on relational database design.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

If you need to see the actual description, you should not be looking in the tables but in queries, reports and forms. To see the info in queries and reports, you would join the lookups to the main table.

Alan

 

[arumbus]

Thank you for your quick reply. Never knew. OK time for a little redesign work.

 

[lmsm76]

This means that when we do this kind of mistake, the only solution is a table restructure?

 

[Pat Hartman]

Welcome aboard
You aren't restructuring the table. You are simply removing the "lookup". Now when you open the table in datasheet view, you will see the actual stored value rather than the lookup value. Your form will still have a combo and it will still show the lookup value. The only time you will have trouble is if you are showing users queries or tables directly. As long as the user interface is based on forms and reports, nothing in the interface needs to change. For your own purposes, you might want to change some of the queries that used to show the "lookup" value to join to the lookup table so the query can show both the real value and the lookup value.