Union query reporting different values than original queries (1 Viewer)

ashkjohnson

Registered User.
Local time
Today, 09:36
Joined
Dec 5, 2019
Messages
12
Hello! I am very new to access and not a programmer. But, I need some help with a union query.

BACKGROUND: I have a union query that is working, however two of the columns report back values from the primary key (default unique ID column). I do NOT want the primary keys' values, I want the other values associated with the primary keys. The two individual queries used to create the union query work as expected, but once combined, the primary key is reported (again, not what I want). The queries run from tables that include lookup fields (combo boxes). I think I need to add directions in SQL to reference the related tables. I think this is likely the reason.

SPECIFICS:

The queries run from the two tables:
Incident Report, Inspection Log

Within the tables the columns I need values (not primary keys) from include:
HAR and HRS
(both tables have a HAR and HRS table)
These columns have a lookup table to related tables

There is one query for each table, and they work individually as expected.
But, once joined as a union, the values of HAR and HRS report the primary ID (default unique ID) not the value desired.

What I want: Is for the HAR and HRS fields to report the values from their lookup table and not the primary keys in the lookup table.

UNION QUERY:

SELECT Licensees.Licensee, [Incident Report].[Report Date], [Incident Report].Violation, [Incident Report].HAR, [Incident Report].HRS, [Incident Report].Description
FROM Licensees INNER JOIN [Incident Report] ON Licensees.ID = [Incident Report].Licensee
WHERE ((([Incident Report].[Report Date]) Between [Start Date] And [End Date]) AND (([Incident Report].Violation)=True))

UNION SELECT Licensees.Licensee, [Inspection Log].[Inspection Date], [Inspection Log].Violation, [Inspection Log].HAR, [Inspection Log].HRS, [Inspection Log].[Violation Description]
FROM Licensees INNER JOIN [Inspection Log] ON Licensees.ID = [Inspection Log].Licensee
WHERE ((([Inspection Log].[Inspection Date]) Between [Start Date] And [End Date]) AND (([Inspection Log].Violation)=True));

I hope I am making sense. Any suggestions will help! Mahalo!!!

-Ashley
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:36
Joined
Aug 30, 2003
Messages
36,118
You should be able to join the lookup table into each of the SELECT clauses in the UNION. Take one of individual queries, put it in a new query. Add the lookup table to the grid with the appropriate join, and change the value returned to the description field of the lookup table. Then copy that SQL back into your UNION query.
 

ashkjohnson

Registered User.
Local time
Today, 09:36
Joined
Dec 5, 2019
Messages
12
Thanks for your reply pbaldy! Sorry, I am so Access illiterate. How do I Add the lookup table to the grid with the appropriate join? I am pretty sure I understand your approach, I just don't know how to deal with specifics. And what which join would I use?

Thanks again!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:36
Joined
Aug 30, 2003
Messages
36,118
With the query in design view, right click in the upper section and select "show table", then add the appropriate table. If it doesn't create a join line automatically, click/drag between the two appropriate fields. If you edit the join, the appropriate selection should be obvious.

By the way, I deleted your duplicate thread.
 

ashkjohnson

Registered User.
Local time
Today, 09:36
Joined
Dec 5, 2019
Messages
12
Thanks for the help, I will try it. And thanks for catching that duplicate thread. I noticed the prompt message of approval after the second submission.

-
Ashley
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:36
Joined
Aug 30, 2003
Messages
36,118
No problem Ashley, post back if you get stuck.
 

ashkjohnson

Registered User.
Local time
Today, 09:36
Joined
Dec 5, 2019
Messages
12
Happy Friday! I tried to join the lookup table into the individual queries by adding tables to the grid. I am clear on the instructions, but I think the joins I am using are incorrect because the data reported are not expected. I am not sure which join I should use. I need some help before I move on to the next step of the suggestion (change the value returned to the description field of the lookup table).
For the time being I have attached a document containing images of the database. I will add the database as soon as I can troubleshoot the prompt "missing security token". Hope the images will help clarify my vagueness.

Individual Queries to build the Union Query are called
Violations- Incident Report
Violations-Inspection Log

The query I have been dabbling with is called
TESTViolations-Inspection Log

To reiterate, my overall goal is for the HAR and HRS fields in the UNION Query to report the values from their lookup table and not the primary keys in the lookup table.

Many thanks in advance!!!!!

Ashley
 

Attachments

  • union query design view.zip
    403 KB · Views: 97

Users who are viewing this thread

Top Bottom