Total values for all records within previous month

[biopot]

Hi

I've been building a database with a number of tables which I've managed to get working fine for simple queries. However one of the users has requested that there should be a function whereby users can obtain the total number of items within the last month.

I have figured out how to get the 'previous month' using

>Between Date() And DateAdd("m",-1,Date())

My columns are:
NoWeb
NoBroWeb
WebProc
broWebProc
DateSub
Brand

Without DateSub I get totals by settin the Total row to SUM for all of the above except Brand. When I add DateSub and set the filter, I get multiple records returned for each brand where I actually want just a total for each brand (but all brands displayed). I hope this is clear!

My SQL is as follows:

___________________
SELECT Sum(Main.NoWeb) AS SumOfNoWeb, Sum(Main.NoBroWeb) AS SumOfNoBroWeb, Sum(Main.WebProc) AS SumOfWebProc, Sum(Main.BroWebProc) AS SumOfBroWebProc, Main.Brand, Main.DateSub
FROM Main
GROUP BY Main.Brand, Main.DateSub
HAVING (((Main.DateSub) Between Date() And DateAdd("m",-1,Date())));
___________________

THANKS!

 

[PHV]

You may try this:
SELECT Sum(NoWeb) AS SumOfNoWeb, Sum(NoBroWeb) AS SumOfNoBroWeb, Sum(WebProc) AS SumOfWebProc, Sum(BroWebProc) AS SumOfBroWebProc, Brand
FROM Main
WHERE DateSub Between DateAdd('m',-1,Date()) And Date()
GROUP BY Brand;

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

 

[biopot]

OMG!

Not only a superfast reply, but a solution as well! Brilliant!

Thanks a million )