query by month to month all the way to date (1 Viewer)

[genevx]

hi,

i was wondering if anyone knew how to create a query that sorts information by months, from the beginning of the current year all the way to the current day. so, for example, if i opened the query now, it would give me a total for January, a total for February, and so on, until it comes to September. I hope I made sense. Thanks in advance! =P

genevx

 

[Jon K]

Assuming you want to total an Amount field by month, you can create a Total Query, grouping by the date field as follows:-

SELECT Last(Month(DateField)) AS Num, Format(DateField,"mmmm") AS Month, Sum(Amount) AS TotalAmount
FROM TableName
WHERE DateField between CDate("1/1/" & Year(Date())) and Date()
GROUP BY Format(DateField,"mmmm")
ORDER BY Last(Month(DateField));


It will be easier to type the query in the SQL View than build it in the grid.

The Where Clause is needed if the table contains records from previous years or later than today's date. The Last(Month(DateField)) is to sort the query result in the order of the months.


Hope you can adapt it to suit your needs.

 

[genevx]

whoo hoo! has anyone told you, you're a genius? =)