Sorting Union Queries

[raskew]

Right off the bat, union queries are not my 'cup of tea'.

SELECT
    tblCat.Category
  , Sum(tblTransfer.PayAmt) AS SumOfPayAmt
FROM
   tblCat 
RIGHT JOIN
   tblTransfer 
ON
   tblCat.CatID = tblTransfer.CatID
WHERE
   (((tblTransfer.ExpDte) Between DateValue([enter mm/yyyy]) 
AND
   DateAdd("m",1,DateValue([enter mm/yyyy]))-1))
GROUP BY
   tblCat.Category
UNION SELECT
    "Grand Total"
  , Sum(tblTransfer.PayAmt) AS MonthlyTotal
FROM
   tblTransfer
WHERE
   (((tblTransfer.ExpDte) Between DateValue([enter mm/yyyy]) 
AND
   DateAdd("m",1,DateValue([enter mm/yyyy]))-1));

My intent is to provide a monthly total for each category. This works as advertised.

The union query is intended to show a overall monthly total for all categories. This too works as advertised.

My problem is to show "Grand Total" as the last item. As written, "Grand Total" is displayed based on the overall alphabetic order.

Not even sure it's possible to do what I'm after. I'm wide-open for suggestions.

Thanks, Bob

 

[pbaldy]

No problemo Bob. Add a "sort" field to both clauses. Make it 1 for the first section and 2 for the second. Put a primary sort on that field.

 

[raskew]

Paul -

You're a lifesaver! I would never in a million years have come up with that.

Here's the outcome for anyone interested.

SELECT
    tblCat.Category
  , Sum(tblTransfer.PayAmt) AS SumOfPayAmt
  , [COLOR="Red"]1 as SortMe[/COLOR]
FROM
   tblCat 
RIGHT JOIN
   tblTransfer 
ON
   tblCat.CatID = tblTransfer.CatID
WHERE
   (((tblTransfer.ExpDte) Between DateValue([enter mm/yyyy]) 
AND
   DateAdd("m",1,DateValue([enter mm/yyyy]))-1))
GROUP BY
   tblCat.Category
UNION SELECT
    "Grand Total"
  , Sum(tblTransfer.PayAmt) AS MonthlyTotal
  , [COLOR="red"]2 as SortMe[/COLOR]
FROM
   tblTransfer
WHERE
   (((tblTransfer.ExpDte) Between DateValue([enter mm/yyyy]) 
AND
   DateAdd("m",1,DateValue([enter mm/yyyy]))-1))
[COLOR="red"]ORDER BY
   SortMe[/COLOR];

Thanks again - Bob

 

[pbaldy]

No problemo, Bob. You probably know this, but for the benefit of future searchers, you can still sort alpha:

ORDER BY SortMe, Category

which should put your categories in order with the grand total at the bottom.

 

[raskew]

Paul -

Thanks again. That's a keeper and I've already filed it away in
my code library.

Bob