To what extent that is correct

[Frank Dulk]

in general terms, a query with this

SELECT [CodCartao],max([DataPgto]), [ValorPgto]
FROM TABELA_PAGAMENTOS
WHERE MAX([DataPgto]) < DATEADD["M",-1,[DataPgto])
GROUP BY [CodCartao],[ValorPgto]

should solve your problem.

In consultations, we should have A LOT OF ATTENTION with the use of the
functions First, Last, DFirst, DLAst, because, unless the researched field
is THE PRIMARY KEY, there won't be ANY WARRANTY that the come back result is
in fact the correct information. In fact, rule road, the result can be had
as randon.

Therefore the employment of the function Max () in the example above!

 

[Michel Walsh]

Hi,


We can't have a MAX, or any aggregate, in a WHERE clause. Should try to
change it for a HAVING. Indeed, a WHERE is "operated" before the
aggregation. SQL just don't know yet what is the MAX when the WHERE is to be
"solved". HAVING occurs after the aggregation. So, HAVING may indeed use an
aggregate in its condition. In fact, the (logical) order is (assuming only
real tables are involved):

Perform the JOIN (ON clauses)
Perform the WHERE filtering
Perform the aggregate and the grouping
Perform the HAVING filtering
Perform the ORDER BY

The strategy of solution may differ, but the result must be the same as if
it was perform the way it is mentioned. As example, ordering at the end may
not be required if the strategy already provided an ordered presentation.

Note that FIRST, and LAST, are to mean the first and the latest record
seen by the database engine, for each group, dependant of the execution
plan, they do not mean EARLIEST or LASTEST, as in the oldest record or
newest record in the table... and indeed, FIRST and LAST should be seen as:
get the data from a (random) record of the group. Their advantage is in the
following case:

f1 f2 ' fields
100 50
50 10
10 100



SELECT MAX(f1), MAX(f2) FROM mytable

returns

100 100


but clearly, that is not from ONE record.

SELECT FIRST(f1), FIRST(f2) FROM mytable

may return any of the three records, but for f1 and f2 would be from the
SAME record.



Hoping it may help,
Vanderghast, Access MVP