Trouble UNIONing queries together - where am I going work?

Question

.....

Answer 1

Hi,

What was the trouble you're having? If you're getting an error, what was the error message?

Answer 2

Hi,

Thanks for the additional information. Perhaps it won't make any difference but try setting it up as follows:

(SELECT...'first query)

UNION

(SELECT..'second query)

Basically, try enclosing each query in brackets/parens.

Hope it helps...

Answer 3

Hmm, so couple of things come to mind...

1. When only pulling from one table I remove the table name from everywhere except the FROM clause when writing a UNION query.  (Gives a clearer error message in the event of a misspelled a field name.)

2. Does Global_Monthly_Headcount indeed have underscores in the name or spaces, Global Monthly Headcount in the name?  If spaces then you need to remove the underscores and put spaces leaving the Object name bracketed.

If none of the above net any positive results make a copy of the query and remove all but one field leaving the FROM clause and see if it runs.

---

Gina Whipp Microsoft Access MVP 2010-2015 Access Diva Tips: https://www.access-diva.com/tips.html

Answer 4

You removed the underscores in the FROM clause.  Put them back since you say the query name does include them.

---

Gina Whipp Microsoft Access MVP 2010-2015 Access Diva Tips: https://www.access-diva.com/tips.html

Answer 5

Reasoning behind what 1 or 2?

---

Gina Whipp Microsoft Access MVP 2010-2015 Access Diva Tips: https://www.access-diva.com/tips.html

Answer 6

I just spotted something.  When you open the query what do the column names look like?  I'm thinking they say MinOfScenario and in your UNION query that is what it is looking for.

---

Gina Whipp Microsoft Access MVP 2010-2015 Access Diva Tips: https://www.access-diva.com/tips.html

Answer 7

Same issue :(

Okay, how about something like so:

SELECT * FROM (SELECT...'your original first query here)
UNION
SELECT * FROM (SELECT...'your original second query here)

Hope it helps...

Answer 8

Hi,

Sorry to hear that. It's hard to troubleshoot something we can't see. Does a regular UNION query work at all? For instance, does this work?

SELECT * FROM MSysObjects
UNION
SELECT * FROM MSysObjects

Just curious...

Answer 9

Oh wow I feel silly!  I needed a UNION ALL instead of just UNION.

Thank you all for your help!!

Hi,

Glad to hear you got it sorted out but it doesn't explain why you would get a "cannot find table or query" error as you said earlier.

Good luck with your project.