UNION QUERY

  • Thread starter Thread starter CINDY K
  • Start date Start date
C

CINDY K

I just created a union query that is made from another
query. The original query is made from a table that some
of the fields relate to other tables, so these fields are
controled in the table as drop downs or I think they are
referred to as combo boxes.

I used the instructions from Microsofts knowledge base to
create this Union Query. This is done by opening a query
in the Design view, going to view then to SQL. Then I
copy, and paste the entire SQL statement into a SQL view
with another SQL statement....It worked very nicely.

My problem is that the fields that belong to related
tables are not functioning properly. In the original
queries, the descriptions from those tables showed up
properly. In the Union query, it is showing the number or
ID number that is assiged to that field in the related
tables.

In my original queries I did use those numbers in the
Criteria to get the proper descriptions to show up. I
have no idea what my adjustment needs to be to fix this.
Is the Union Query getting these numbers from my criteria
in the original queries, or is it looking at the wrong
column or field.
 
Cindy-

Please go read: http://www.mvps.org/access/lookupfields.htm

A UNION query won't inherit the lookup properties that you've defined for
the tables, so you're seeing the REAL values in those columns. If you want
to see the looked-up value, you need to include the lookup table in the SQL
joined to the table from which you need to do the lookup. Select the lookup
value rather than the numeric linking value to display in your query.

For example, if your query displays customer information with a lookup to
customer type and you're filtering on type 1 (retail customer), your
original query might look like:

SELECT Customers.CustomerName, Customers.Address, Customers.City,
Customers.State, Customers.CustomerType
FROM Customers
WHERE CustomerType = 1

To display the lookup value, the query might need to change to something
like:

SELECT Customers.CustomerName, Customers.Address, Customers.City,
Customers.State, CustTypes.TypeDescription
FROM Customers INNER JOIN CustTypes
ON Customers.CustomerType = CustTypes.CustomerType
WHERE CustTypes.TypeDescription = "Retail"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top