Results 1 to 2 of 2
2008-02-14, 00:19 #1
- Join Date
- Feb 2003
- 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!
2008-02-14, 00:44 #2
- Join Date
- Mar 2002
- 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.