I've got two tables, one with personal information, and one with financial information. My problem is probably most clear with an example:
personal tablefinancial table Mr. White 2001: 5 dollars
2002: 3 dollars
2003: 4 dollars
Mr. Black 2001: 6 dollars
2002: 2 dollars
I want to extract the following data: i want the names of all people in my personal table and i want the year they last paid in next to it, so in my example this would be: Mr. White 2003
Mr. Black 2004
ok thanks for trying to help, i'll try to explain it better this time.
- so i have table_personal and table_financial
- in table_personal i have one record for every person, with fields like name, address, telephonenumber, ...
- in table_financial i have one record for every year in which a person has paid, so the fields are year, dateofpayment, amount, ...
- the two tables are related to eachother in this way: one record in personal relates to many records in financial, every person has paid some for years, example:
--> 2004 - 05/02/2004 - €30
--> 2004 - 03/04/2004 - €50
2005 - 02/03/2005 - €40
so if a person hasn't paid for 2005, there is no record for 2005...
now i want for every person, the year they last paid in, for person1 this would be 2004, for person2 2005.
Basically it is very simple, i do a query for all the records of financial, linked with the person's name, and for every person i filter out all the records but the last...
maybe this picture in attach can help:
it's my personal details table, linked with the financial records
the id field and the name field are visible in the personal table, it has for 3934 and person1 in it, person2 is another record
and for every person there are a few records, each year is a record
now i want a query that can extract the last year a person has paid for, and only that year