Month and Year Problem

[fatbloater]

Hoping someone can help, i have a table named TXNS. It has 3 fields; they are ID, Date_of_TXN and qty_on_txn.

There are records from 5 years in the table, example of data held:

ID | Date_of_TXN | qty_on_txn
1 | 01/04/09 | 20
2 | 10/04/09 | 3
3 | 20/04/09 | 100

I want a query to sort them into month and years, counting the transactions and items.

e.g.

ID | Date_of_TXN | qty_on_txn

3 | Apr 09 | 123

Can anyone help?

 

[Brianwarnock]

Select Count(ID) as countofID, Format(Date_of_TXN ,"mmm-yy"),sum(qty_on_txn) as sumofitems
from tablename
group by Format(Date_of_TXN ,"mmm-yy")

This is untested air code.

Brian

 

[raskew]

Hi Brian -

Your aircode works as written, however, it sorts month names alphabetically rather than in proper month/year sequence. Here's a working example that presents month/year in the correct sequence:

SELECT
    Count(PaymentID) AS countofID
  , Format(DteLastPaid,"mmm-yy") AS Expr1
  , Sum(AmtPaid) AS sumofitems
FROM
   tblPayments
GROUP BY
   Format(DteLastPaid,"mmm-yy")
  , Year([dteLastPaid])
  , Month([dteLastPaid])
HAVING
   ((Not (Format([DteLastPaid],"mmm-yy")) Is Null))
ORDER BY
   Year([dteLastPaid])
  , Month([dteLastPaid]);

Best wishes - Bob

P.S.: Fatbloater - Eagles may fly high, but weasels dont get sucked into jet engines. - I love it!

 

[Brianwarnock]

Ha ha thanks Bob, I initially had a numeric month and then saw that the poster wanted alpha and made the change forgetting about the sort order.

Brian

 

[fatbloater]

Thanks for the help - worked a treat.

 

[raskew]

Brian -

Did you have a better solution for sorting by month/year? If so, please post it.

Best wishes (and look out for the weasels).

Bob

 

[Brianwarnock]

No Bob, I just forgot to do it.

Brian