How to get union query to pull data from another table based on a cell within the query?

[templeowls]

Confusing title. I apologize. Let me explain. I have two tables of customer complaints over the past two years, tbl2019 and tbl2020. I have the tables in a union query, qry20192020. I also have another table of manager information, tblManagers. I'm trying to figure out a way to populate ManagerName and ManagerNumber (which is in tblManagers) within the qry20192020 based on the Location, which is in all three tables. I have pasted the SQl code for the union query below.

SELECT EventNumber, SubmissionDate, SubmittedBy, CustomerAccount, CustomerFirstName, CustomerLastName, Location, HighLevelEvent, Status, Description, ActionTakenFROM 2019
WHERE Event = "Yes"
AND Status = "In Progress"


UNION SELECT EventNumber, SubmissionDate, SubmittedBy, CustomerAccount, CustomerFirstName, CustomerLastName, Location, HighLevelEvent, Status, Description, ActionTaken
FROM 2020
WHERE Event = "Yes"
AND Status = "In Progress";

Can I do this within a union query or do I need to make a union table or something else? Thank you!

[June7]

Why do you have a table for each year?

You can either JOIN each SELECT to tblManagers or build another query that joins the UNION to tblManagers.

Each location has only 1 manager?

[templeowls]

We've just always done it. Definitely makes it more difficult.

How would I go about building another query that joins them? Like what would be the SQL?

Yes each location has one manager.

[templeowls]

Never mind, I got it. Thank you!