ashkjohnson
Registered User.
- Local time
- Today, 12:33
- 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
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