Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts

    How to ge the 'last payment' made by a student? (2000 SP3)

    I'm feeling a little stupid tonight, because I think that I have solved this problem in the past, but cannot do so again!

    I have a table, each record has these fields: student name, dollar amount paid, date of payment. There may be one or many payments made by each student. I want to create a Query that gives me only the last payment made by each student, no matter when it was made.

    I'm sure this is possible, but I'm having a brain cramp tonight!
    Thanks
    Bob Chapman

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to ge the 'last payment' made by a student? (2000 SP3)

    Start by creating a query in design view based on your query.
    Add the student name and date of payment fields.
    Select View | Totals to change the query to a totals query.
    Set the Total option for the date of payment field to Max.
    This query will return the date of the most recent payment for each student.
    Save this query as (for example) qryLastDate.

    Create a new query in design view; add the table and the query that you just created.
    Join them on the date of payment vs max of date of payment field, i.e. drag a line from the field in the table to the field in the query.
    Add the student name, dollar amount, and if desired, date of payment fields from the table to the query grid.
    This query will return the most recent payment for each student.

Posting Permissions

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