last three payments

[Khalil Handal]

Hi to all,


I am building a database. Each month I am paying money to different people.
How can i find the last three payment for a specific person?

sincerely yours
Thank you

[jojowhite]

can you use a Query:

SELECT TOP 3 * FROM yourPaymentTableName WHERE payee = 'TheNameOfPayee' ORDER BY PaymentDate DESC;

or

SELECT TOP 3 * FROM yourPaymentTableName WHERE payeeID = 1234 ORDER BY PaymentDate DESC;

[Khalil Handal]

Thank you very much for the reply.
Is it possible to sum them or find the average?

Thanks

[Khalil Handal]

Hi
I was able to get the average.

SELECT TOP 3 Avg(FieldName) AS AvgOfPayment
FROM tblHistory
WHERE [FieldName2]='Value';

Thank you

[jojowhite]

you should add Sort order (Descending order) on your Payment date
to get the correct latest 3 payment records.

[Khalil Handal]

Hi

I tried to add the sort order as follows:

SELECT TOP 3 Avg(FieldName) AS AvgOfPaymentFROM tblHistory
WHERE [FieldName2]='Value'
ORDER BY FieldName3 DESC;

But has the following error:

Your query does not include the specified expression 'FieldName3' as part of an aggregation function

Khalil

[jojowhite]

to find the Average of the Last 3 Payments, you will need this:

SELECT AVG(FieldName) AS AvgOfPayment 
FROM 
(SELECT TOP 3 FieldName 
FROM tblHistory
WHERE [FieldName2]='Value' ORDER BY FieldName3 DESC);

[Khalil Handal]

Thank you very much.
Khalil