Hi,
have table "Kontakts", and a table "Payments".In Payments are Pay_Date and Pay_amount.
How would i retrieve the last payment for all kontaks??
now i use in one query last from Pay_Date and last from Pay_amount, but i have the impression this is not accurate.
Annyone?
THANKS!!!


SELECT Tbl_Kontakten.Lidnr, Tbl_Kontakten.Naam, Tbl_Kontakten.Voornaam, Last(Tbl_Betalingen.Amount) AS LaatsteVanBedrag, Last(Tbl_Betalingen.Date ) AS LaatsteVanDatum
FROM (Tbl_Postcode INNER JOIN Tbl_Kontakten ON Tbl_Postcode.Postcode_Id = Tbl_Kontakten.Postcode_Id) INNER JOIN Tbl_Betalingen ON Tbl_Kontakten.Kontakten_Id = Tbl_Betalingen.Kontakt_Id
GROUP BY Tbl_Kontakten.Lidnr, Tbl_Kontakten.Naam, Tbl_Kontakten.Voornaam
ORDER BY Last(Tbl_Betalingen.Date );

I can't read your query, but the usual way to get what you want is to use Max instead of Last and Min instead of First. You would of course need to have a field that can be used for this purpose, in your case it sounds like you would want Max(Pay_Date).

A way to do this would be to develop a query that groups on Lidnr(?) and Max of Pay_Date. That will give you the latest pay date for each employee.

Then another query that joins this query back to the table on the EmployeeNumber and date fields in each. Put whatever fields you want in the grid.

Frank,
thx for your repy.
Can i explain further?
tabel contacts (=members): contact_id and all other contact info
tabel payments: contact_id, payment_id, pay_date, amount

ex:
member A has payed :
2€ on 01/05/04 (payment_id=1)
5€ on 01/05/05 (payment_id=3)
3€ on 01/05/06 (payment_id=5)

member B has payed:
12€ on 01/08/04 (payment_id=2)
15€ on 01/08/05 (payment_id=4)
13€ on 01/08/06 (payment_id=6)

I only want to view the last payment of each member:
member A payment_5
member B payment_6

I translated my Query so you may have better insight in relations and tables:

SELECT Tbl_Members.Member_Id, Tbl_Members.Name, Tbl_Members.SurName, Last(Tbl_Payments.Amount) AS Amount, Last(Tbl_Payments.Date ) AS LastDate
FROM (Tbl_Postalcodes INNER JOIN Tbl_Members ON Tbl_Postalcodes.Postalcode_Id = Tbl_Members.Postalcode_Id) INNER JOIN Tbl_Payments ON Tbl_Members.Member_Id = Tbl_Payments.Member_Id
GROUP BY Tbl_Members.Member_Id, Tbl_Members.Name, Tbl_Members.SurName
ORDER BY Last(Tbl_Payments.Date );

( the postalcodes tabel is not relevant i guess)

I tried last, max in any kind of combination, querys on querys, always somthings goes wrong.Especially the max function on Amount is tricky because you easily get the highest amount mixed with the highest date
ex:
memberA 5€ on 01/05/06!!!!

I really don't get it...
thanks
Jo

Develop a brand new query that uses the tbl_Payments table. Put the Member_Id and Date fields in the grid. Click View--->Totals. Set the Totals: cell of Member_Id to Group By, set the Totals: cell of Date to Max. When you run this query you should see one row for each different Member_Id value together with the maximum Date for each different Member_Id value.

Then to get the rest of the data that you need, develop another query that uses this query and the tbl_Payments and/or tbl_Members table as its "tables". Join the query to the tbl_Payments table by the Member_Id fields in each and the Date and MaxOfDate fields.