Subtotal charges by month

  • Thread starter Thread starter CW
  • Start date Start date
C

CW

I have a query based on my invoices table.
It includes the date of the invoice [InvDate] and the amount [Charge].
I want to summarise the charges by month.
I have tried using Group By in various ways but cannot get the result I need.
BTW I know I could do this in a report, but I want to do it in a query (so
that I can base a form upon it).
Many thanks
CW
 
Try this ---
SELECT Format([InvDate], "mmm yyyy") AS Monthly, Sum([Charge]) AS Charges
FROM tblInvoices
GROUP BY Format([InvDate], "yyyymm")
ORDER BY Format([InvDate], "yyyymm");
 
Thanks Karl, but unfortunately I get this error message:
"You tried to execute a query that does not include the specified expression
Format([InvDate], "mmm yyyy") as part of an aggregate function.
Looking forward to your further advice!
Many thanks
CW

KARL DEWEY said:
Try this ---
SELECT Format([InvDate], "mmm yyyy") AS Monthly, Sum([Charge]) AS Charges
FROM tblInvoices
GROUP BY Format([InvDate], "yyyymm")
ORDER BY Format([InvDate], "yyyymm");
--
KARL DEWEY
Build a little - Test a little


CW said:
I have a query based on my invoices table.
It includes the date of the invoice [InvDate] and the amount [Charge].
I want to summarise the charges by month.
I have tried using Group By in various ways but cannot get the result I need.
BTW I know I could do this in a report, but I want to do it in a query (so
that I can base a form upon it).
Many thanks
CW
Click to expand...
Click to expand...
 
Try it this way ---
SELECT Format([InvDate], "mmm yyyy") AS Monthly, Sum([Charge]) AS Charges
FROM tblInvoices
GROUP BY Format([InvDate], "mmm yyyy")
ORDER BY Format([InvDate], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


CW said:
Thanks Karl, but unfortunately I get this error message:
"You tried to execute a query that does not include the specified expression
Format([InvDate], "mmm yyyy") as part of an aggregate function.
Looking forward to your further advice!
Many thanks
CW

KARL DEWEY said:
Try this ---
SELECT Format([InvDate], "mmm yyyy") AS Monthly, Sum([Charge]) AS Charges
FROM tblInvoices
GROUP BY Format([InvDate], "yyyymm")
ORDER BY Format([InvDate], "yyyymm");
--
KARL DEWEY
Build a little - Test a little


CW said:
I have a query based on my invoices table.
It includes the date of the invoice [InvDate] and the amount [Charge].
I want to summarise the charges by month.
I have tried using Group By in various ways but cannot get the result I need.
BTW I know I could do this in a report, but I want to do it in a query (so
that I can base a form upon it).
Many thanks
CW
Click to expand...
Click to expand...
Click to expand...
 
Thanks Karl, but unfortunately I get this error message:
"You tried to execute a query that does not include the specified expression
Format([InvDate], "mmm yyyy") as part of an aggregate function.
Looking forward to your further advice!
Click to expand...

Group by it too:

SELECT Format([InvDate], "mmm yyyy") AS Monthly, Sum([Charge]) AS Charges
FROM tblInvoices
GROUP BY Format([InvDate], "yyyymm"), Format([InvDate], "mmm yyyy")
ORDER BY Format([InvDate], "yyyymm");
 
Back
Top