UNION Query syntax Issue

[Buxtona]

Hi

Im trying to combine 3 tables into 1 using a union query. the 3 tables have the same fields/Data types but i keep getting this

Any ideas?

 

[arnelgp]

is serialDate string?

 

[Buxtona]

No its a numeric field

 

[Gasman]

Try using brackets?

I *think* the A is from the start of the AND ?

 

[Buxtona]

Tried it with these brackets:

SELECT wj_attendance.ShopID
FROM wj_attendance
WHERE (wj_attendance.EmployeeID=3372) AND (wj_attendance.SerialDate Between 20160101 And 20191016)
UNION
SELECT wj_attendance2017.ShopiD
FROM wj_attendance2017
WHERE (wj_attendance2017.EmployeeID=3372) AND (wj_attendance2017.SerialDate Between 20160101 And 20191016)
UNION
SELECT wj_attendance2016.ShopID
FROM wj_attendance2016
WHERE (wj_attendance2016.EmployeeID=3372) AND (wj_attendance2016.SerialDate Between 20160101 And 20191016)

Same error message.

 

[Buxtona]

and silly thing is:

SELECT wj_attendance.ShopID
FROM wj_attendance
WHERE (wj_attendance.EmployeeID=3372) AND (wj_attendance.SerialDate Between 20160101 And 20191016)
UNION
SELECT wj_attendance2017.ShopiD
FROM wj_attendance2017
WHERE (wj_attendance2017.EmployeeID=3372) AND (wj_attendance2017.SerialDate Between 20160101 And 20191016)

If i only combine 2 tables it works

 

[Minty]

Simple debug experiment; cut and paste the individual selects into fresh queries to make sure they work?

 

[isladogs]

Are there any results for the 2016 table using the WHERE filter if its run on its own?

What happens if you do this in 2 stages?
1. Create qryUnion with no filters
2. Create another query filtering qryUnion

Also, as possibly implied in post #1 table is the plan to combine all 3 tables into one?

 

[arnelgp]

if it works on 2, check the last table.
does it have the fields specified.

 

[Buxtona]

Simple debug experiment; cut and paste the individual selects into fresh queries to make sure they work?

All 3 queries run ok on their own

 

[Gasman]

I'm confused now as your initial error message was on the 2017 file.?

and silly thing is:

SELECT wj_attendance.ShopID
FROM wj_attendance
WHERE (wj_attendance.EmployeeID=3372) AND (wj_attendance.SerialDate Between 20160101 And 20191016)
UNION
SELECT wj_attendance2017.ShopiD
FROM wj_attendance2017
WHERE (wj_attendance2017.EmployeeID=3372) AND (wj_attendance2017.SerialDate Between 20160101 And 20191016)

If i only combine 2 tables it works

 

[Buxtona]

Are there any results for the 2016 table using the WHERE filter if its run on its own? yes a years worth of data as expected

What happens if you do this in 2 stages?
1. Create qryUnion with no filters - same error message
2. Create another query filtering qryUnion

Also, as possibly implied in post #1 table is the plan to combine all 3 tables into one?

yes i want to join all three tables to get results from all 3 years

 

[Buxtona]

I am using MS Access 2010 and MYSQL Tables