By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 834 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

Last Date query

P: n/a
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 :)
Nov 8 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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

On Thu, 8 Nov 2007 11:32:52 +0900, "scott"
<sc**********@maneyacts.comwrote:
>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!
Nov 10 '07 #2

P: n/a
On Nov 7, 9:32 pm, "scott" <scott.nos...@maneyacts.comwrote:
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!

Nov 11 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.