Most recent payment date

[Judy]

I want to request annual payments for membership. I have a query that pulls
payments by month, but I need only the most recent payments.
For example: January 2008, I need to know who has not made any payments for
at least 12 months, and the most recent payment being in January, any year.
What is the best way to do this?

 

[Allen Browne]

Use a subquery to get the most recent date.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

Another example of how to get a related field from a GroupBy:
http://www.mvps.org/access/queries/qry0020.htm

 

[NetworkTrade]

make a query that finds January payments...with a wildcard for the year
field...

make another query of who made payments Feb-Dec 07

then make a 3rd query that is an unmatch no duplicates query; this is
offered as an option in your new query design....follow the wizard and use
query 1 and 2 as the record sources for this query...

 

[Judy]

I need the most recent payment for each member. When I ran this, I only got
the one most recent payment in the test data. I there a way to get all most
recent payments, one for each member?

 

[Judy]

This gives me one record out of all records. Is there a way to get the
most recent payment/date for each member, then pull by month?

 

[jon]

Hi Judy

Just had a similar problem and it was sorted out with a sub query
mine was

AND Baan.DueDate = (SELECT Min(DueDate] FROM Baan AS Early WHERE Early.Job =
Baan.Job)
you would need to use "max" where I needed the min on the date field.
This is because I wanted the oldest field where you want the newest.

my original post was on the 21/12/07 if you want to look at the whole
thread.

HTH

Jon

 

[Allen Browne]

Create a query that groups by MemberID.

In that query, use the subquery.

 

[Judy]

Where does this code go? I don't know how do build a sub-query in 2007,
apparently.
Thanks,
Judy

Hi Judy

Just had a similar problem and it was sorted out with a sub query
mine was

AND Baan.DueDate = (SELECT Min(DueDate] FROM Baan AS Early WHERE Early.Job =
Baan.Job)
you would need to use "max" where I needed the min on the date field.
This is because I wanted the oldest field where you want the newest.

my original post was on the 21/12/07 if you want to look at the whole
thread.

HTH

Jon

I need the most recent payment for each member. When I ran this, I only
got
the one most recent payment in the test data. I there a way to get all
most
recent payments, one for each member?

 

[jon]

Hi Judy
I dont have 2007 but if it is the same as 97 and 2003 when you are in the
design view of your query you enter the sub query in the criteria feild.
What I find helps getting help out of this group is if you paste in the SQL
into the post, as the experts on here can see what is happening (I am not
one of them though, still leaning SQL). You get to the the SQL in the
versions I use by right clicking in the tables area or where you switch
between the disign view and the data sheet view on the drop down arrow you
can select the SQL view there.

Regards

Jon

 

[Judy]

Thanks Jon,
I found SQL view in the pull down menu of the design ribbon in 2007. I'm
still having trouble with syntax, but I'm learning.
Judy