Data displayed wrongly in Union query

[CW]

I have a Union query that merges data from 3 queries. Those 3 queries pull
from my Inquiries table. One of the fields in question is Inquiry Type. We
have 3 of those in an Inquiry Types table and they are entered by users
through a combo on an Inquiry input form.
When it is saved into the Inquiries table it is stored as text e.g. Private,
Corporate, Trade.
Each of the 3 queries finds these values correctly and displays them as text.
However, when I run the Union query based on these 3 queries, the Inquiry
Type comes out as 1, 2, 3 etc., which is the ID in the Inquiry Types table.
But none of the queries is looking at that table so why on earth does the
Union query do that and how can I correct it?
Thanks
CW

 

[Roger Carlson]

I suspect you are using a Lookup Field in your table, which distorts what is
actually stored in the table. Even though it is displaying the description,
it is actually storing the number. For this reason, I never use lookup
fields. (This should not be confused with lookup *tables*, which are
another matter altogether.) Join your queries with the lookup table, using
the description in the field list.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[CW]

Roger -
Thanks...there is a Combo Box as the Lookup on this field in the Inquiries
table.
I have tried to add the lookup table (the Inquiry Types table) to the select
queries but get a data mismatch error when I try to make the join, whatever
type of join I try.
I don't understand what you say about the stored value being different from
what is shown in the table. If the table shows (for example) "Corporate"
isn't that what is stored in the table??? I thought tables were the
indisputable source for data - how can it be anything different? If we're
saying that it says one thing but actually means another, I can only conclude
that Access must have been designed by a politician....
Thanks
CW

 

[John Spencer]

When you use a Lookup field in a table, the data stored in the field is a copy
of the value in the bound column you have specified. The bound column may or
may not be the column that is shown in the combobox - that depends on how you
have set the properties of the field. And the unfortunate part that the real
stored value can be completely hidden when you are working in a query or in
the table.

To make things worse - you can put number or date data from the bound table or
query into a text field. Then when you realize the problem and try to use
join the text field in the table to the number field in the lookup table ---
error! mismatched data type.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Roger Carlson]

This link shows what LookUp Fields (or Lookup Columns) are and how to create
them: http://office.microsoft.com/en-us/access/HA101637731033.aspx See
especially "Understanding the bound value and the display value in a lookup
column" here: http://office.microsoft.com/en-us/access/HA101637731033.aspx#4

This link explains why you should NOT use them:
http://www.mvps.org/access/lookupfields.htm

Lookup fields were created as a shortcut so novice Access users wouldn't
have to learn about relating tables and Joins. But while they might make
things easier at the beginning, they become a headache later on, as you are
discovering.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[CW]

Thanks for the explanation John, that makes it a little clearer.
CW

 

[CW]

Roger - that's very helpful, I understand things rather better now
Many thanks
CW