1. Registered User
Join Date
Sep 2004
Posts
24

Hello

I've got two tables, one with personal information, and one with financial information. My problem is probably most clear with an example:

personal table financial 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

Any ideas on how to do this?
Thanks!

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
What are the individual fields in each table?

3. Registered User
Join Date
Sep 2004
Posts
24
Hi

I want the field [Name] from the table [T_personal details] ,
and [Year paid for] from [T_financial] , but I want it to show the last year the person has paid for...

thanks.

4. Registered User
Join Date
Nov 2002
Posts
272
I guess you'll have to tell us how to determine that. Mr Black has financial records for 2001 and 2002. How can we tell he paid for 2004? From the amount he paid maybe?

5. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
Try again. What are the individual fields in each table?

ie:

Table1:
field_1
field_2

Table2:
field_1
field_2

6. Registered User
Join Date
Sep 2004
Posts
24
hi

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:
person1
--> 2004 - 05/02/2004 - €30
person2
--> 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...

I hope it's clearer now!! Any help ??

7. Registered User
Join Date
Sep 2004
Posts
24
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

thanks!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692