self-join with Max thrown in causing problems

[gusset]

i have two tables in Access; tblFiles and tblPayments. there can be many payments for each file; they are linked via tblFiles LEFT JOIN tblPayments ON tblFiles.fldFile_id = tblPayments.fldFileId.

in addition to fldFileId, tblPayments has fldPaymentDate and fldPaymentAmount

What i need (in the same recordset) is 1) the payment made most recently and 2) the sum of payments for each file. i have been dabbling with subqueries, MaxOf_fldPaymentDate and various permutations of self-joins for a couple of days, but no joy so far.

has anyone done anything similar in the past?


result sought, (eg file 19 has two payments - £400 in April this year and £500 previously:
[tt]
qryAllData

fldFile_id MaxOfDate LatestPaymentAmount SumOfPayments
019 04/04/03 £400.00 £900.00
020
021 01/01/01 £100.00 £100.00
022
[tt]
thanks

g

 

[Steve101]

Yes, this is an interesting problem, and is more difficult than would appear at face value. The trick here is to be able to retrieve the actual amount associated with the "maximum date" for each fileid.

There are several ways to approach it. The one I'll show here uses whats called a "correlated subquery". Here's the SQL:

SELECT P.fldFileId, P.fldPaymentDate,
P.fldPaymentAmount
FROM tblPayments AS P
WHERE P.fldPaymentDate=(select max(fldPaymentDate)
from tblPayments
where fldFileId = P.fldFileId);

Note its "correlated" because the nested subquery is logically "joined" (or correlated) to the parent query.

If you save the above query, as say qryLastPaymentDetails, then you can inner join it on another subquery which group totals the payment amounts for each FileId, and collectively the result should give you the column outputs that you desire.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[gusset]

thanks. can't wait to try it out.

g