Last Date query

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 :)

Re: Last Date query

Hint: It helps to post your query. Without that, we can only
guess....

On Thu, 8 Nov 2007 11:32:52 +0900, "scott"
wrote:

>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 :)
>

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Last Date query

On Nov 7, 9:32 pm, "scott" wrote:
> 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!