Collecting same value from different tables

[JosephNierenberg]

I have been unable to accomplish what I think is a simple task, so I'm throwing this to the experts....

One Access database syncs with three SharePoint lists that live in three different subsites within the same SPO root tenant site. The Access db also has a few additional tables that support the three main tables, like names, clients, etc. Each of the three main tables has a column for priority. I would like to have a view which shows in one place all the high priority items from all three of the lists. When I've tried to do this using one of the in-app wizards, I receive error messages that I do not even understand. Is it possible to do what I'm trying?

[#2]

@JosephNierenberg 

This will probably be slower, but it should work. Create a UNION query with three select clauses:

SELECT Field1, Field2, YourPriorityFieldGoesHere

FROM SHAREPOINTLIST1

WHERE 

YourPriorityFieldGoesHere = "High"

UNION

SELECT Field1, Field2, YourPriorityFieldGoesHere

FROM SHAREPOINTLIST2

WHERE 

YourPriorityFieldGoesHere = "High"

UNION

SELECT Field1, Field2, YourPriorityFieldGoesHere 

FROM SHAREPOINTLIST3

WHERE 

YourPriorityFieldGoesHere = "High"

[#3]

Thank you. I think this might work, but I'm both busy and inexperienced in what it takes to create this, so unfortunately it will be a couple of days until I can return here to mark this as an answer (or otherwise to reply).