Totaling for each month 1

[melaniecarr23]

I have an orders table, and would like to run a query that will show the total of all orders approved broken down by month.

I have the following fields to work with:

order_date
approved

So I need to pull all records where approved = true (Since it's a checkbox field) but getting things broken down further is something I never got in access.

Can someone please help me?

Thanks

 

[ZmrAbdulla]

Did you try creating a query? In the wizard at the second screen you can have summary of the data to be selected. And the third screen you will specify the period.

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)

 

[melaniecarr23]

I know I could do it if I do it using the wizard for one month, but is there a way to get all 12 totals on one query?

 

[PHV]

Something like this ?
SELECT Format(order_date,'yyyy-mm') AS [Month], Sum(amount) AS Total
FROM tblOrders
WHERE approved = True
GROUP BY Format(order_date,'yyyy-mm')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[melaniecarr23]

You totally rock! Thank you PH. This is exactly what I was trying to do.

Melanie

 

[ZmrAbdulla]

melaniehayes, You are totally wrong.. If you create a query by wizard also will give you result of all the months in a query.

________________________________________
Zameer Abdulla
Visit Me
Minds are like parachutes. They only function when they are open. -Sir James Dewar (1877-1925)