Union Query Error Msg (1 Viewer)

[WXIONG06]

the "The SELECT statement includes a reserved word or an argument name that is missppelled or missing, or the punctuation is incorrect." I'm trying to place three linked query into on query. The code works on until I added this language:

UNION ALL SELECT [Tony Chin].DATE, [Tony Chin].RESEARCHER, [Tony Chin].CREATOR, [Tony Chin].REVIEWER, [Tony Chin].[PASS/REJECT], [Tony Chin].[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1,
FROM [Tony Chin]
WHERE ((([Tony Chin].[PASS/REJECT])="Fail" Or ([Tony Chin].[PASS/REJECT])="Reject"))

The whole language is this:

SELECT Bill.Date, Bill.RESEARCHER, Bill.CREATOR, Bill.REVIEWER, Bill.[PASS/REJECT], Bill.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Bill
WHERE (((Bill.[PASS/REJECT])="Pass") AND ((Bill.[WHAT WAS MISSED?]) Is Null))

UNION ALL SELECT Bill.Date, Bill.RESEARCHER, Bill.CREATOR, Bill.REVIEWER, Bill.[PASS/REJECT], Bill.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Bill
WHERE (((Bill.[PASS/REJECT])="Reject"))
UNION ALL SELECT [Tony Chin].Date, [Tony Chin].RESEARCHER, [Tony Chin].CREATOR, [Tony Chin].REVIEWER, [Tony Chin].[PASS/REJECT], [Tony Chin].[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM [Tony Chin]
WHERE ((([Tony Chin].[PASS/REJECT])="Pass") AND (([Tony Chin].[WHAT WAS MISSED?]) Is Null));

UNION ALL SELECT [Tony Chin].DATE, [Tony Chin].RESEARCHER, [Tony Chin].CREATOR, [Tony Chin].REVIEWER, [Tony Chin].[PASS/REJECT], [Tony Chin].[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1,
FROM [Tony Chin]
WHERE ((([Tony Chin].[PASS/REJECT])="Fail" Or ([Tony Chin].[PASS/REJECT])="Reject"))
UNION ALLSELECT Wendy.DATE, Wendy.RESEARCHER, Wendy.CREATOR, Wendy.REVIEWER, Wendy.[PASS/REJECT], Wendy.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Wendy
WHERE (((Wendy.[PASS/REJECT])="PASS") AND ((Wendy.[WHAT WAS MISSED?]) Is Null));

UNION ALL SELECT Wendy.DATE, Wendy.RESEARCHER, Wendy.CREATOR, Wendy.REVIEWER, Wendy.[PASS/REJECT], Wendy.[WHAT WAS MISSED?], DateDiff("m",[DATE],Now()) AS Expr1
FROM Wendy
WHERE (((Wendy.[PASS/REJECT])="Reject"));


I've been looking at this and I don't know what's wrong. Please HELP!

 

[MarkK]

Create one table called 'Person' and then add a field called 'FirstName', and then dump all the data from your various tables into one. Then your SQL will look like ...

SELECT [Date], Resaercher, Creator, Reviewer, IsReject, MissedItems, DateDiff("m",[DATE],Now()) AS ElapsedMonths
FROM tblPerson
WHERE FirstName IN ("Wendy", "Bill", "Tony")
AND IsRejct = False
AND MissedItems Is Null

... spend a little time now, do it right, and make your life way, way simpler.
Cheers

 

[WXIONG06]

Create one table called 'Person' and then add a field called 'FirstName', and then dump all the data from your various tables into one. Then your SQL will look like ...
... spend a little time now, do it right, and make your life way, way simpler.
Cheers

These query is linked to a the the tables which are linked to an excel spreadsheet. And the spread gets updated everyday so I want the query to be updated as well so I can't just copy and paste.

 

[WXIONG06]

Okay, Found my answer.... I put a comma at the end of the third set of UNION ALL SELECT of "AS Expr1"

 

[MarkK]

But you have a series of linked tables that are then names of people, and they are all structured exactly the same, right?
So write a union query that you know works that collects all the data ...

SELECT t1.Date, "Wendy" as FirstName, t1.Researcher, t1.Creator, t1.Reviewer, t1.IsReject, t1.MissedItems, DateDiff("m", t1.Date, Now()) AS ElapsedMonths
FROM Wendy As t1
UNION
SELECT t2.Date, "Tony" As FirstName, t2.Researcher, t2.Creator, t2.Reviewer, t2.IsReject, t2.MissedItems, DateDiff("m", t2.Date, Now()) AS ElapsedMonths
FROM Tony As t2
UNION
SELECT t3.Date, "Bill" As FirstName, t3.Researcher, t3.Creator, t3.Reviewer, t3.IsReject, t3.MissedItems, DateDiff("m", t3.Date, Now()) AS ElapsedMonths
FROM Bill As t3

... and maybe you save that as qPersonUnion. Now you can leave that query alone, and if those are linked tables it will automatically requery it's sources. Now you can write a query like ....

SELECT *
FROM qPersonUnion
WHERE FirstName IN ("Wendy", "Bill", "Tony")
AND IsRejct = False
AND MissedItems Is Null

... but it doesn't make sense to me to have to write a custom query that is that complex as as business routine. It's just way too fragile, as you are finding out. Find ways to create incremental steps where some of those steps are known to work reliably.
Hope this helps,

 

[WXIONG06]

But you have a series of linked tables that are then names of people, and they are all structured exactly the same, right?
So write a union query that you know works that collects all the data ...

... and maybe you save that as qPersonUnion. Now you can leave that query alone, and if those are linked tables it will automatically requery it's sources. Now you can write a query like ....

SELECT *
FROM qPersonUnion
WHERE FirstName IN ("Wendy", "Bill", "Tony")
AND IsRejct = False
AND MissedItems Is Null

... but it doesn't make sense to me to have to write a custom query that is that complex as as business routine. It's just way too fragile, as you are finding out. Find ways to create incremental steps where some of those steps are known to work reliably.
Hope this helps,

Isn't UNION used to eliminate "Duplicate" entries? I have duplicate entries for tracking purposes because each team member put their activities in differently