Query for total sales by month 1

[cloverdog]

Hello Could anyone help with what is probably a simple query showing totals by month. I have a table tblPayments which contains years of payments and includes the following fields: PaymentDue, PaymentCleared, Amount.

I would like a query that when run shows a list of PaymentsCleared totalled by month so Jan06 = £Xthousand, Feb06 = £Ythousand etc.

Many thanks

 

[PHV]

If PaymentCleared is a Datetime field:
SELECT Format(PaymentCleared,'mmmyy') AS theMonth, Sum(Amount) AS thePayment
FROM tblPayments
GROUP BY Format(PaymentCleared,'mmmyy')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cloverdog]

That was really helpful but is there any way to list them in order in 'mmmyy' format.

I changed the 'mmmyy' to 'yymm' and sorted on it ascending and this listed the months in order from first month of trading to last. I then tried adding another field using 'mmmyy' next to it so it would list from first month Mar06, Apr06 . . .Apr09, May09 but the query failed.

Many thanks

 

[PHV]

SELECT Format(PaymentCleared,'mmmyy') AS theMonth, Sum(Amount) AS thePayment
FROM tblPayments
GROUP BY Format(PaymentCleared,'mmmyy'),Year(PaymentCleared),Month(PaymentCleared)
ORDER BY Year(PaymentCleared),Month(PaymentCleared)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cloverdog]

Thank you for that PHV it did exactly what I wanted.