Results 1 to 8 of 8

Thread: query problem

  1. #1
    Join Date
    Sep 2004
    Posts
    24

    Unanswered: query problem

    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. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What are the individual fields in each table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    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. #4
    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. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try again. What are the individual fields in each table?

    ie:

    Table1:
    field_1
    field_2

    Table2:
    field_1
    field_2
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    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. #7
    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!
    Attached Thumbnails Attached Thumbnails accessproblem.JPG  

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm guessing n your class you recently covered various select clauses.

    have a look at the 'select top' predicate (clause) and set the order to be year of payment descending

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •