Query using sum and count 1

[MemphisVBA]

I am trying to get a count and sum on invoices for each month that were not paid (i.e. sent to AP) for that month. I have a query that works fine for 1 month at a time, but is there a way to combine the past 12 months (May '05 to May '06 in this case) into just one query?

Here is my (one month at a time) query:

SELECT Sum(IIf([InvDate] Between #3/1/2006# And #3/31/2006#,[totalinv],0)) AS [MAR-06 Amnt], Count(IIf([InvDate] Between #3/1/2006# And #3/31/2006#,[totalinv],0)) AS [MAR-06 Count]
FROM data
WHERE (([data]![DateSentToAP]>#3/31/2006#) AND (((([data].[InvDate]) Between #3/1/2006# And #3/31/2006#) And (([data].[DateSentToAP])>#3/31/2006#))));

Thanks in advance!

 

[Crowley16]

use the Month clause to extract the month, and use a group by clause the group your data into months...

e.g. select sum(fldNme), count(fldNme) from table where [some conditions] group by month(dateField)

--------------------
Procrastinate Now!

 

[Golom]

Like Crowley16 said ... along the lines of

SELECT Format([InvDate], "yyyymm") As [The Month],
       Sum([totalinv])   AS [Invoice Total], 
       Count([totalinv]) AS [Invoice Count]

FROM [data]

WHERE [DateSentToAP] > DateSerial ( Year([InvDate]), Month([InvDate]) + 1, 0) 

GROUP BY Format([InvDate], "yyyymm")

 

[MemphisVBA]

That works perfectly - thanks