Aggregate query issue

[TheShabz]

Hi all,

I have a query to the tune of:
SELECT stuff
FROM tablesWithJoins
GROUP BY EverythingInSelect

This works fine. Now I add:
HAVING table1.DateField Between [table2].[DateField] And DateAdd("d",60,[table2].[DateField])

I'm getting the "You tried to execute a query that does not included the specified expression (my HAVING clause) as part of an aggregate function" error. This query worked fine in 2003 but we recently upgraded to 2007 and now it's not working. What's changed between 2003 and 2007?

[Robeen]

Try creating a query that does the Date filter.
Then create a query on the above query and do your grouping in the second query.

I'm not sure this is the most elegant solution - but it should work.

[TheShabz]

That should work as a patchjob but this query usually takes about 5 mins to run. it's hitting 2 linked tables with 10m+ records in each. Getting just the dates then querying again will just about double the process. I need to get this in one go.

I just don't see why my Having clause is causing that issue.

[Robeen]

I've had the same issue in Access 2010 - but I don't have near as many rows of data to worry about.

BUT - I don't think doing it my way will increase or decrease the time the query takes to run. I could be wrong - but either way Access is going to have to Filter AND Group . . .

I'd like to know if you find a more efficient way. I could use it myself.

[pbaldy]

This is a shot in the dark, but does this work (adding the expression to both the SELECT and GROUP BY clauses)?

SELECT stuff, DateAdd("d",60,[table2].[DateField]) AS WhoCares
FROM tablesWithJoins
GROUP BY EverythingInSelect, DateAdd("d",60,[table2].[DateField])
HAVING table1.DateField Between [table2].[DateField] And DateAdd("d",60,[table2].[DateField])

[TheShabz]

Does not work.

[pbaldy]

Swing-and-a-miss = strike one.

If all the fields are in the GROUP BY clause, does taking that away and making the HAVING clause a WHERE clause work? Perhaps with the DISTINCT keyword if appropriate?

[TheShabz]

SELECT DISTINCT Stuff
FROM tablesWIthJoins
WHERE table1.DateField Between [table2].[DateField] And DateAdd("d",60,[table2].[DateField])
is running. I'll know soon whether Distinct gets all the dupes out.

[TheShabz]

scrapped the whole thing. Gonna do dupe checking afterwards. Maybe one day we can have non-aggregate criteria in an aggregate query. =/