Dates in a Query

[mreference]

I want to create a simple query from a list of orders dating back over 12 months.

The fields I have is Ord_Date, Qty

I need to show the orders by month for the last 12 months.

The problem I am getting is that the orders for the month of April (as we are in April now) contain orders from 1st - 22nd April 2013 and orders from 23rd - 30th April 2012, therefore confusing the figures.

I would just like orders grouped by month with a total qty dating back 12 months, but without any old orders for the current month.

Please help, it must be simple but I can't figure this out

 

[pr2-eugin]

What is the current Query you have used? The criteria should be

WHERE theDateField > DateAdd("yyyy", -1, Date())

 

[mreference]

I used something similar,

 >=DateAdd("m",-12,Date())

But both include the records from last April that I want to omit

 

[pr2-eugin]

Without the code, you have it will be a stab in the dark.. So is it possible..

What is the current Query you have used?

 

[mreference]

Sorry, full code of query so far

SELECT SOP_History.Ord_Date, SOP_History.Qty
FROM SOP_History INNER JOIN items ON SOP_History.Stk_Code = items.ItemCode
WHERE (((SOP_History.Ord_Date)>=DateAdd("m",-12,Date())))
ORDER BY SOP_History.Ord_Date DESC;

 

[pr2-eugin]

A bit bizarre.. Can you show some sample data? and the expected result? I have created some sample..

autoID    Ord_Date    Qty    Stk_Code
1        19/04/2012    5     111
2        19/05/2012    10    111
3        19/06/2012    10    111
4        19/12/2012    10    111
5        19/02/2013    10    111
6        19/03/2013    10    111
7        19/04/2013    10    111
8        24/04/2012    10    111
9        25/04/2013    5     111

Result data..

SumOfQty    ByMonth
10        Apr-2012
15        Apr-2013
10        Dec-2012
10        Feb-2013
10        Jun-2012
10        Mar-2013
10        May-2012

The query I used..

SELECT Sum(SOP_History.Qty) AS SumOfQty, Format([Ord_Date],"mmm-yyyy") AS ByMonth
FROM SOP_History INNER JOIN items ON SOP_History.Stk_Code = items.ItemCode
WHERE (((SOP_History.Ord_Date)>=DateAdd("m",-12,Date())))
GROUP BY Format([Ord_Date],"mmm-yyyy");

Is this the result you expect to see?

 

[mreference]

That's perfect, is there a way to have the dates in order, April 2013, March 2013....April 2012?