How to query using multiple months?

[n2jesus]

Hi all,
I am generating reports based on a sales history table. I need to get monthly totals for several things. Up till now, I've been creating 12 subqueries (one for each month), and then include them in another query to get all the information with one query.

But, I feel deep in my bones that there has to be a way to do this just with one query. Maybe using a
WHERE [date] IN(select.....) type of query.

I want the query to give me one total for all sales in Jan, Feb, etc.

Thanks for all input on this,

Jim

 

[willir]

Play with...

WHERE Month([YourDate]) IN(select Month([YourDate]).....)

Might have to use Select DISTINCT ...

 

[n2jesus]

Thanks,
Let me be more specific. I want the query to go find every record with a date in Jan, and sum all of it's totals. Then do the same for Feb, Mar, Apr, etc.

Doing programmatically would be easy. I'd have it loop changing the month each time, but is there a way to do it in one query.

I want the output from the query to be something like:
Jan $50000
Feb $47000
Mar $56500
etc.

Basically, it needs to find everything in Jan, and do stuff to it, then find everything in Feb, and do the same stuff to it.

Thanks for any help,

Jim

 

[PHV]

Are you talking about an aggregate query ?
SELECT Monthname(Month([name of date field])) As [Month], Sum([name of sales field]) As Total
FROM [name of your table]
GROUP BY Monthname(Month([name of date field]))
;

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

 

[n2jesus]

More like this:

SELECT Sum([sales field]) as Total
FROM [saleshistory]
WHERE Month([sale_date]) = 1 AND Year([sale_date]) = 2004

that part is easy enough. I want it to then do the same for month 2, 3, 4,... all in the same query.
hope that makes it clearer.

 

[PHV]

Have you tried this ?
SELECT Monthname(Month(sale_date)) As [Month], Sum([sales field]) As Total
FROM saleshistory
WHERE Year(sale_date)=2004
GROUP BY Monthname(Month(sale_date))
;

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

 

[n2jesus]

Thanks PHV,

I'm not certain yet how your suggestion works, but it does.

Jim