grand total

[msnews.microsoft.com]

Is there anyone who know how can I set my total in the bottom of a query.
This is how I set up codes:

SELECT STU_METHODS.FIELD1 AS NAME, Count(*) AS [COUNT],
CCur(Sum(STUPAYMENTS.AMOUNT)) AS RECEVIED

FROM STU_METHODS RIGHT JOIN PATSTUMENTS ON STU_METHODS.STU_METHODSKEY =
STUPAYMENTS.STU_METHODSKEY

WHERE (((STUPAYMENTS.DATE) Between #1/1/2005# And #7/11/2005#))


thank you in advance...

GROUP BY STU_METHODS.FIELD1, STU_METHODS.STU_METHODSKEY, STU_METHODS.TYPE

HAVING (((STU_METHODS.TYPE)<>0))

UNION

SELECT "TOTAL", NULL, SUM(STUPAYMENTS.AMOUNT)

FROM STUPAYMENTS

WHERE (((STUSTUMENTS.DATE) Between #01/01/05# And #07/12/05#))

 

[Guest]

Hi,

I think you just had a typo on your query.
Try this:

SELECT STU_METHODS.FIELD1 AS NAME, Count(*) AS [COUNT],
CCur(Sum(STUPAYMENTS.AMOUNT)) AS RECEVIED
FROM STU_METHODS RIGHT JOIN STUPAYMENTS ON STU_METHODS.STU_METHODSKEY =
STUPAYMENTS.STU_METHODSKEY
WHERE (((STUPAYMENTS.DATE) Between #1/1/2005# And #7/11/2005#))
GROUP BY STU_METHODS.FIELD1, STU_METHODS.STU_METHODSKEY, STU_METHODS.TYPE
HAVING (((STU_METHODS.TYPE)<>0))
UNION
SELECT "TOTAL", NULL, SUM(STUPAYMENTS.AMOUNT)
FROM STUPAYMENTS
WHERE (((STUPAYMENTS.DATE) Between #01/01/05# And #07/12/05#));

 

[[MVP] S.Clark]

If you must do it at the bottom of the query, then a UNION would do it.

Reports work pretty well, too.