SQL statement HAVING MAX(some+thing)=some+thing

Question

I'm having trouble with Microsoft Access 2003, it's complaining about this statement:

select cardnr
from change
where  year(date)<2009
group by cardnr
having max(time+date) = (time+date) and cardto='VIP'

What I want to do is, for every distinct cardnr in the table change, to find the row with the latest (time+date) that is before year 2009, and then just select the rows with cardto='VIP'.

This validator says it's OK, Access says it's not OK.

This is the message I get: "you tried to execute a query that does not include the specified expression 'max(time+date)=time+date and cardto='VIP' and cardnr=' as part of an aggregate function."

Could someone please explain what I'm doing wrong and the right way to do it? Thanks

Note: The field and table names are translated and do not collide with any reserved words, I have no trouble with the names.

Answer 1

Try to think of it like this - HAVING is applied after the aggregation is done. Therefore it can not compare to unaggregated expressions (neither for time+date, nor for cardto).

However, to get the last (principle is the same for getting rows related to other aggregated functions as weel) time and date you can do something like:

SELECT cardnr
FROM change main
WHERE time+date IN (SELECT MAX(time+date) 
                    FROM change sub
                    WHERE sub.cardnr = main.cardnr AND 
                          year(date)<2009 
                          AND cardto='VIP')

(assuming that date part on your time field is the same for all the records; having two fields for date/time is not in your best interest and also using reserved words for field names can backfire in certain cases)

It works because the subquery is filtered only on the records that you are interested in from the outer query.

Applying the same year(date)<200 and cardto='VIP' to the outer query can improve performance further.

Answer 2

Does this return the the latest date before 2009 for each cardnr?

SELECT
    cardnr,
    Max([time] + [date]) AS MaxTimeDate
FROM change
WHERE
    ([time] + [date]) < #2009/01/01#
GROUP BY
    cardnr;

If so, try it as a subquery.

SELECT
    c.cardnr,
    (c.[time] + c.[date]) AS TimeDate,
    c.cardto
FROM
    change AS c
    INNER JOIN (
        SELECT
            cardnr,
            Max([time] + [date]) AS MaxTimeDate
        FROM change
        WHERE
            ([time] + [date]) < #2009/01/01#
        GROUP BY
            cardnr
        ) AS s
    ON c.cardnr = s.cardnr
    AND (c.[time] + c.[date]) = s.MaxTimeDate
WHERE
    c.cardto='VIP';