Last function in query not always fetching last record.

  • Thread starter Thread starter Peter Blackett
  • Start date Start date
P

Peter Blackett

I have this query...
SELECT [Sales Ledger Transactions].STSLMN, Last([Sales
Ledger Transactions].STDate) AS LastDate, Last([Sales
Ledger Transactions].STMN) AS LastOfSTMN, Last([Sales
Ledger Transactions].STPaymentAmount) AS
LastOfSTPaymentAmount, Last([Sales Ledger
Transactions].STForeignPayment) AS LastOfSTForeignPayment
FROM [Sales Ledger Transactions]
WHERE ((([Sales Ledger Transactions].STPaymentAmount)<>0))
GROUP BY [Sales Ledger Transactions].STSLMN;

And on 2 PCs it doesnt fetch the last Sales Transaction
from the table. (in this case they are chqs)
These 2 machines are runnning
Win 2k server ACCESS xp (10.2627.4219) SP2
Win 2k Prof. ACCESS xp (10.2616.2625)

2 Machines that it does work on...
Win 2k server access xp (10.2627.2625) SP2
win 2k pro access xp (10.4302.4219)

for a STSLMN of 13169304 and date of 14/05/04 and 14/06/04
it brings up the older date instead of the newer record.
the STMN is also higher on the later record too so its not
picking up a lower value from anywhere else....

Any help?
 
I believe this is something to do with sorting. Have you tried using max
instead of last ?

HTH
 
And on 2 PCs it doesnt fetch the last Sales Transaction
from the table. (in this case they are chqs)
Click to expand...

The Last aggregate function is *very* misleading. It retrieves the
last record *IN DISK STORAGE ORDER*, regardless of any sorting that's
done on the recordset; and Access will store new records wherever
there is room, so (although it often will be) the most recently
entered record might not be the last - it might have been stuck into a
gap left by a deleted record elsewhere in the table.

As a result, Last is all but useless.

If you want *the most recent* record you will need to use a Subquery
using the maximum date (or transaction number) as a criterion.
 
Thanks... i'm sure this will help me get to the bottom of
this problem.
-----Original Message-----
I believe this is something to do with sorting. Have you tried using max
instead of last ?

HTH
Peter Blackett said:
I have this query...
SELECT [Sales Ledger Transactions].STSLMN, Last([Sales
Ledger Transactions].STDate) AS LastDate, Last([Sales
Ledger Transactions].STMN) AS LastOfSTMN, Last([Sales
Ledger Transactions].STPaymentAmount) AS
LastOfSTPaymentAmount, Last([Sales Ledger
Transactions].STForeignPayment) AS LastOfSTForeignPayment
FROM [Sales Ledger Transactions]
WHERE ((([Sales Ledger Transactions].STPaymentAmount)
Click to expand...
GROUP BY [Sales Ledger Transactions].STSLMN;

And on 2 PCs it doesnt fetch the last Sales Transaction
from the table. (in this case they are chqs)
These 2 machines are runnning
Win 2k server ACCESS xp (10.2627.4219) SP2
Win 2k Prof. ACCESS xp (10.2616.2625)

2 Machines that it does work on...
Win 2k server access xp (10.2627.2625) SP2
win 2k pro access xp (10.4302.4219)

for a STSLMN of 13169304 and date of 14/05/04 and 14/06/04
it brings up the older date instead of the newer record.
the STMN is also higher on the later record too so its not
picking up a lower value from anywhere else....

Any help?
Click to expand...


.
Click to expand...
 
Back
Top