Union query

[Fev]

Hi
I have just started using Union queries, and while I have been able to
get the bulk of the data in the result, I would like to include 2
further fields that both appear in the original select queries, but I
cannot get them to be accepted as part of the Union query. My query
reads:
SELECT [Reviewer Name], [Peer to Review]
FROM qryNFIRandomisedReport
Where [forms]![frmEMailReviewers]![cboCycle]=[myCycle]
UNION ALL SELECT [Reviewer Name], [Peer to Review]
FROM qryFIRandomisedReport
Group by [Reviewer Name], [Peer to Review]
ORDER BY [Reviewer Name], [Peer to Review];

[Peer to Review] and [Reviewer Name] in both queries are the result of
concatenating [FirstName] and [LastName]. The two other fields that i
would like to use are [Cycle] and [IDStaff] - these also appear in the
dynsets of both of the original queries. When I include them in the
select line, I get the following message:
The SELECT statement includes a reserved word or an argument name that
is misspelled or missing, or the punctuation is incorrect. I have
placed these fields at various places in the SELECT line, with a comma
and in [ ], without any luck.

I would greatly appreciate some help in this.
Thanks
Heather

 

[Bob Barrows]

I'm confused. You seem to imply that you had a union query that was working
but that now fails when you include [Reviewer Name] and [Peer to Review].
But the sql you show us only has those two fields being selected ... Is the
sql you are showing us the sql that works? If that's the case, we need to
see the sql that is failing.

I think you are also going to have to show us the sql of both
qryNFIRandomisedReport and qryFIRandomisedReport.

PS. You do realize, I hope, that the GROUP BY in your statement is only
applying to the second query in your union statement. In essence, your
statement is combining the result from

SELECT [Reviewer Name], [Peer to Review]
FROM qryNFIRandomisedReport
Where [forms]![frmEMailReviewers]![cboCycle]=[myCycle]

with the result from

SELECT [Reviewer Name], [Peer to Review]
FROM qryFIRandomisedReport
Group by [Reviewer Name], [Peer to Review]

and then ordering by

ORDER BY [Reviewer Name], [Peer to Review];

If you want to group the unioned result, you have to select and group from a
saved union query or a subquery. Like this:

select [Reviewer Name], [Peer to Review]
FROM SavedUnionQuery
Group by [Reviewer Name], [Peer to Review]
ORDER BY [Reviewer Name], [Peer to Review];

or, using a subquery:

select [Reviewer Name], [Peer to Review]
FROM (
SELECT [Reviewer Name], [Peer to Review]
FROM qryNFIRandomisedReport
Where [forms]![frmEMailReviewers]![cboCycle]=[myCycle]
UNION ALL
SELECT [Reviewer Name], [Peer to Review]
FROM qryFIRandomisedReport
) as q
Group by [Reviewer Name], [Peer to Review]
ORDER BY [Reviewer Name], [Peer to Review];

Hi
I have just started using Union queries, and while I have been able to
get the bulk of the data in the result, I would like to include 2
further fields that both appear in the original select queries, but I
cannot get them to be accepted as part of the Union query. My query
reads:
SELECT [Reviewer Name], [Peer to Review]
FROM qryNFIRandomisedReport
Where [forms]![frmEMailReviewers]![cboCycle]=[myCycle]
UNION ALL SELECT [Reviewer Name], [Peer to Review]
FROM qryFIRandomisedReport
Group by [Reviewer Name], [Peer to Review]
ORDER BY [Reviewer Name], [Peer to Review];

[Peer to Review] and [Reviewer Name] in both queries are the result of
concatenating [FirstName] and [LastName]. The two other fields that i
would like to use are [Cycle] and [IDStaff] - these also appear in the
dynsets of both of the original queries. When I include them in the
select line, I get the following message:
The SELECT statement includes a reserved word or an argument name that
is misspelled or missing, or the punctuation is incorrect. I have
placed these fields at various places in the SELECT line, with a comma
and in [ ], without any luck.

I would greatly appreciate some help in this.
Thanks
Heather

 

[DawnTreader]

I am no expert, but I have done some huge Union queries and it seems to me that maybe you need to have the group by in both sections and the same might go for the order by...

SELECT [Reviewer Name], [Peer to Review]
FROM qryNFIRandomisedReport
Where [forms]![frmEMailReviewers]![cboCycle]=[myCycle]
Group by [Reviewer Name], [Peer to Review]
ORDER BY [Reviewer Name], [Peer to Review]
UNION ALL SELECT [Reviewer Name], [Peer to Review]
FROM qryFIRandomisedReport
Group by [Reviewer Name], [Peer to Review]
ORDER BY [Reviewer Name], [Peer to Review];

and even further wouldnt you want only results from each in the same cycle? that would require the where in both.

i usually try to think of a Union query as 2 seperate queries that have to function and they need the same number of fields and names for those fields.

hope this helps.

 

[Bob Barrows]

I am no expert, but I have done some huge Union queries and it seems
to me that maybe you need to have the group by in both sections and
the same might go for the order by...

No, only a single Order By is allowed in a union, and it has to go at the
end.

 

[DawnTreader]

like i said, no expert.

i guess i better double check my queries to see if i have any errors!

DawnTreader

 

[Bob Barrows]

like i said, no expert.

i guess i better double check my queries to see if i have any errors!

No worries. If you had made that error, your queries would not have run
without raising an error in the first place. If your queries are running,
they're fine.