TOTALS in Queries

[eriel3]

okay i have a this query that is grouping the information by month and summing up the field AOG

I do not want it to group by month but to group by a date range that i put in each time, how would i change this?

SELECT DISTINCTROW Format$([Daily Activity-Millennium Query1].[DATE],"mmmm yyyy") AS [DATE By Month], Sum([Daily Activity-Millennium Query1].[Sum Of AOG_MEOH]) AS [Sum Of Sum Of AOG_MEOH]
FROM [Daily Activity-Millennium Query1]
GROUP BY Format$([Daily Activity-Millennium Query1].[DATE],"mmmm yyyy"), Year([Daily Activity-Millennium Query1].[DATE])*12+DatePart("m",[Daily Activity-Millennium Query1].[DATE])-1;

 

[Blorf]

The date range you put in, you want it to include only those dates, and give you one total per item?

If so, you can use an iif, like

Filter:iif ([date] >= #1/1/01# and [date] <= #2/15/01#, "Include", "Exclude")

and put "Include as your filter"

Hope that helps,
ChaZ

Ascii dumb question, get a dumb Ansi

 

[eriel3]

okay, how would i do that, would i just add it to the end of the sql statement, could you show me how the sql statement should read?

 

[Blorf]

In your sql statement you have the following:

select field, field, field from blah

so just add like this:

select field, field, field, iif ([date] >= #1/1/01# and [date] <= #2/15/01#, "Include", "Exclude")
as filter from blah


Ascii dumb question, get a dumb Ansi