Group BY or ORDER BY in a UNION SELECT Query

[noodnutt]

Hi Team. First time poster here who is dreadful with SQL. Apologies in advance if I do not explain or lay this out correctly.

TIA for any help you can throw my way.

Although, I get a result! It's not the result I'm after.

SELECT PickDate, txHandle, Bulk, Primary, TotalLines, PickHours, PickRate
FROM qKPI_BB
UNION SELECT PickDate, txHandle, Bulk, Primary, TotalLines, PickHours, PickRate
FROM qKPI_BT;

This gives me two lines. (These results are purely for displaying on a Subform only)

What I really need is for the results to be grouped.

I tried this:

SELECT dtPickDate, txHandle, Bulk, Primary, TotalLines, PickHours, PickRate
FROM qKPI_BB
UNION
SELECT dtPickDate, txHandle, Bulk, Primary, TotalLines, PickHours, PickRate
FROM qKPI_BT
ORDER BY txHandle;

It gives me the same result (SQL is not my Forte).

As for the Select Queries, they're the same column structure and Criteria.

qKPI_BB: PickDate = Forms|fBoard!dtPickDate & (txRunNo = Like "BUL*" [Not Visible]). 
qKPI_BT: PickDate = Forms|fBoard!dtPickDate & (txRunNo = Like "MAS*" [Not Visible]).

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[arnelgp]

save your Union query..
create Total query from your union query.

SELECT dtPickDate, txHandle, 
   SUM(Bulk) As SumOfBullk, 
   SUM(TotalLines) As SumOfLines, 
   SUM(PickHours) As SumOfPickHours, 
   Avg(PickRate) As AvgPickRate 
FROM yourUnionQuery GROUP BY dtPickDate, txHandle;

 

[plog]

Why a UNION?

Can't your two queries be configured into one that groups your data appropriately so that you don't then have to patch them together?

Can you show us the SQL of your two queries?

 

[Pat Hartman]

Why a UNION?

A union is a type of join. Instead of matching rows from one source to another, it stacks them in a list because they are separate sets of data. Join types are not interchangeable. Unions are sometimes, but not always, used to overcome flaws in the schema design.

Based on the code shown, it looks like the two separate criteria can be combined:

PickDate = Forms!fBoard!dtPickDate AND (txRunNo Like "BUL*" OR txRunNo Like "MAS*")

That will allow the aggregation to be done in the same query.

PS, I moved the thread to a more appropriate forum.