On Nov 7, 9:32 pm, "scott" <scott.nos...@maneyacts.comwrote:
Quote:
Hi Everyone,
>
I have a table which has many fields in it but I need to pull some specific
info via a query and I don't know if it's possible.
>
I want to run a query which includes two tables. Participants and Payments.
They are linked so that a participant can make many payments and a report
can then be printed on all payments that participant has made.
>
What I want to do is have a field in the payments table that has a "PaidTo"
field which is a date. If it goes past the date, I want to be able to run a
query that shows participants that are overdue. I have query that does this.
That was no issue. Where the problem ocures is that as there may be many
payments by the one participants, unless I remove the past payment enquiry
date, is shows the old payments as well yet I only want to show the last
one.
>
What I have is;
<=Date()-7 And Is Not Null
>
for the criteria of the PaidTo field. When I run the query, it shows a list
that may have the participant 10 times as they've made 10 payments. I want
it to only show the Last Payment "PaidTo" Date.
>
I hope that all makes sense! If anyone can help me i'd appreciate it.
>
Regards, Scott :)
|
It doesn't make sense to store "PaidTo" (a volatile value) in Payments
(a transaction table). You would have to update this field every time
a payment is posted. What you can do is create a new query showing the
max PaidTo grouped by Participant. Use the latter query to drive the
overdue report.You can join this to the Participants table to get
details for the participants.
Hope this helps!
