Searching for a more efficient way to get latest payment

[Katerine]

Hi,
I'm just curious to see if there's a more efficient way of doing things than what I've currently got.

I have three tables, which I will call "Members," "Payments," and "MemberPayments." Payments can be made by one member, or they can be a joint payment divvied up between many members, hence the need for a joining table. I need to get the most recent payment for each member, according to the value in the "PaymentDate" field (in "Payments").

Sometimes, members make more than one payment on the same date. This is considered normal. There is no time data, so these are considered the same payment date.

I did try correlated subqueries (as described in the FAQ), but because members sometimes make more than one payment on the same date, this returns an error.

So this is what I've got. It works... it's just really, really slow.
Here are the relevant fields:
Members:
MemberID (PK)

Payments:
PaymentID (PK)
PaymentDate

MemberPayments:
MemberPaymentID (PK)
MemberID (FK)
PaymentID (FK)
Amount

And here's what I've got:
qryLastPaymentForMembers_sub:

SELECT [MemberPayments].MemberID, Max([Payments].[PaymentDate]) AS [MaxOfPaymentDate]
FROM [Payments] INNER JOIN [MemberPayments] ON [Payments].[PaymentID] = [MemberPayments].[PaymentID]
GROUP BY [MemberPayments].MemberID;

qryLastPaymentForMembers:

SELECT [MemberPayments].[MemberPaymentID], [MemberPayments].MemberID, [MemberPayments].[Amount], [Payments].[PaymentDate]
FROM ([Payments] INNER JOIN [MemberPayments] ON [Payments].[PaymentID] = [MemberPayments].[PaymentsID]) INNER JOIN qryLastPaymentForMembers_sub ON ([Payments].[PaymentDate] = qryLastPaymentForMembers_sub.[MaxOfPaymentDate]) AND ([MemberPayments].MemberID = qryLastPaymentForMembers_sub.MemberID);

Again, this works... I was just wondering if there was a better way (that would work even if there are multiple payments on the same day).

Thanks!

Katie

 

[Katerine]

(forgot to mention
In this example, the data is stored in an Access 2003 database.

I know there's a more efficient way to do it in SQL Server (using partitioning), but I'm specifically looking for an Access-based solution. I'm really asking about a general type of problem, not a specific database or client, and most of my clients use Access databases and aren't able to migrate or aren't interested in migrating.

Katie

 

[MazeWorX]

I'm assuming your clients are connecting remotely and running queries against the db? You may want to look at replication if the later is true

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>