Most recent payment date

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?
0
Utf
1/4/2008 12:35:03 AM
access.queries 6343 articles. 1 followers. Follow

9 Replies
925 Views

Similar Articles

[PageSpeed] 49

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Judy" <Judy@discussions.microsoft.com> wrote in message
news:A674B685-34BF-4219-95C6-80CE178DC233@microsoft.com...
>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? 

0
Allen
1/4/2008 1:09:24 AM
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...

-- 
NTC


"Judy" wrote:

> 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?
0
Utf
1/4/2008 1:46:00 AM
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?

"NetworkTrade" wrote:

> 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...
> 
> -- 
> NTC
> 
> 
> "Judy" wrote:
> 
> > 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?
0
Utf
1/6/2008 5:54:01 PM
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?

"Allen Browne" wrote:

> 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
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Judy" <Judy@discussions.microsoft.com> wrote in message
> news:A674B685-34BF-4219-95C6-80CE178DC233@microsoft.com...
> >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? 
> 
> 
0
Utf
1/6/2008 7:00:00 PM
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


"Judy" <Judy@discussions.microsoft.com> wrote in message 
news:2E1EB6D4-63B6-4DA6-ADF6-785D0926EE4C@microsoft.com...
>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?
>
> "NetworkTrade" wrote:
>
>> 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...
>>
>> -- 
>> NTC
>>
>>
>> "Judy" wrote:
>>
>> > 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? 


0
jon
1/6/2008 11:02:08 PM
Create a query that groups by MemberID.

In that query, use the subquery.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Judy" <Judy@discussions.microsoft.com> wrote in message
news:66D93B54-7324-48C1-9B2A-0423A0B9A804@microsoft.com...
> 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?
>
> "Allen Browne" wrote:
>
>> 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
>>
>> "Judy" <Judy@discussions.microsoft.com> wrote in message
>> news:A674B685-34BF-4219-95C6-80CE178DC233@microsoft.com...
>> >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? 

0
Allen
1/7/2008 8:55:20 AM
Where does this code go?  I don't know how do build a sub-query in 2007, 
apparently.  
Thanks,
Judy

"jon" wrote:

> 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
> 
> 
> "Judy" <Judy@discussions.microsoft.com> wrote in message 
> news:2E1EB6D4-63B6-4DA6-ADF6-785D0926EE4C@microsoft.com...
> >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?
> >
> > "NetworkTrade" wrote:
> >
> >> 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...
> >>
> >> -- 
> >> NTC
> >>
> >>
> >> "Judy" wrote:
> >>
> >> > 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? 
> 
> 
> 
0
Utf
1/7/2008 2:21:02 PM
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 


0
jon
1/7/2008 10:13:45 PM
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

"jon" wrote:

> 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 
> 
> 
> 
0
Utf
1/8/2008 1:43:13 AM
Reply:

Similar Artilces:

DATE #10
Feb 28,2008 ---> 2008-2-28 Feb 1,2008 ---> 2008-2-1 Format? "����" <cola@hz.cn> wrote in message news:OOQhTbRaKHA.5472@TK2MSFTNGP02.phx.gbl... > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > See your other post "ÎÞÃû" wrote: > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > > > . > Hi, Highlight the cell, right click in the mouse, cell format, custom format and enter yyyy-mm-dd "ÎÞÃû" wrote: > Fe...

Bank Rec void check date s/b same as GL void date
When voiding a check in the payables management module and the Void date and Posting date are changed from the original check date, Bank reconciliation should recognize that void date instead of the original check date as the voided date. Please refer to Knowledgebase Article 857704 to see how Ban Rec handles the date of a void check. If a check is voided in Payables Mgmt on a different date than the original date, and bank rec uses the original date trying to reconcile to the general ledger is a problem because the adjusted bank book balance is different than the GL balance becuase th...

Calendar Formatting Date Range problem
Hi, I am trying to make a monthly calendar that our administrator can update easily with new dates, and mail out. I've formatted the calendar and it looks great as it is right now for the month of April. However, when I choose "change date range" it reverts back to the original formatting and I have to start all over. I realize this is because I used a "design object". However, if I don't use a design object, then I can't change the date ranges. How do I change the date range on my calendar without losing all the formatting changes I made? Thanks T...

Can I set up a calendar lookup for date selection
Is it possible to lookup a calendar for date selection on a cell. I cannot find any reference to it in the help. try this url http://www.fontstuff.com/vba/vbatut07.htm ================= "Tangoxray" <Tangoxray@discussions.microsoft.com> wrote in message news:72D4DB63-1D7F-477A-93C7-2F767A207B26@microsoft.com... > Is it possible to lookup a calendar for date selection on a cell. I cannot > find any reference to it in the help. ...

Can you automatically date stamp emails that you send out? #2
My emails need a date stamp on them as I send them. It does not appear that you can do this..does someone have an idea to do this? Thanks ...

Help Please- How to offset Date to correct column for Ageing
Hi All, I have a stock ageing sheet for finished goods, which uses a vlookup table, on entry of the Product code (last 3 Digits), this populates the next three columns with description, FG cost and Quantity of product. the quantity of product is entered again manually into the appropriate column, there are five columns these are banded <3 Months 0%, 3 to 6 Months 25%, 6 to 9 months 50%, 9 to 12 Months 75% and > 1 Year 100%, the percentage is liability provision. What I would like to do is on entry of the product code as above the quantity is automatically inserted into the correc...

Copying dates between Excel documents
I have noticed that when I copy a date eg/ 23/02/04 (UK format) from on Excel document to another the date becomes 22/02/00. Has anyone comes across this, its really infuriating as I have t redate everything again Thank -- Message posted from http://www.ExcelForum.com XL has two date systems, the Windows default 1900 date system, in which the 0-base date is 31 December 1899, and the MacXL default 1904 date system, in which the 0-base date is 1 January 1904. The systems differ by 4 years and a day. To resolve your issue, make sure that both workbooks have the same date system selected...

Change Order date?
I have situation where we had to enter some orders into CRM like 3 weeks after the fact but there was no way to to back date them when entering so that they fall into the proper months reports, etc. Any help out there on how I can change the dates at the database level? Look in the SalesOrderBase table. Mike "SteveT" <drumguy61-google@yahoo.com> wrote in message news:4635de59.0402120638.1bd39e41@posting.google.com... > I have situation where we had to enter some orders into CRM like 3 > weeks after the fact but there was no way to to back date them when > entering ...

Can't get Date Range to work from Form to Report
Hi, I've followed Allen Browne's directions very closely for creating a form for a date range to limit results to that date range on a report and can not get it to work for some reason. I've read many threads on this board which all say the same thing and it seems to work for everyone else. :( Here's what I have: A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. The form is called "frmDateRange" and the report is called "Sales Activity Report". The two unbound text boxes are set to "Short Date" format which the user ...

Partial Payments
I have a payment setup for some tenants. Sometimes they only pay a partial payment on the rent. How do I adjust this so that when a partial payment is made the reoccurring payment will adjust it self. Ex: Rent is $900.00 A reoccurring payment for the 1st of the month is $900.00 from tenant x Tennant x pays $700.00 on the first. I get a message asking if this is the payment scheduled, if I choose yes it asks me if I want to adjust the amount, but I don't want to do that because the rent is not paid in full and it will look like tenant x has paid the full payment. Also I will get n...

Publisher 97 and date/time print
I am designing an office card using 1997 Publisher. No matter what I do it prints day, month,year and time on every page. I have checked background and there is no text box even there to delete to stop this problem. How can I find and delete? This will be an option of your printer driver. Disable that option and your trauma will be over. -- ...

Dollar and date format
I have 2 questions related to formatting information that I have imported: 1)If I have a column that contains amounts such as 0000000427.99 and I want to remove the decimal but keep the trailing cents, what is the best way to do that for a whole column? example: 000000042799 2) I also have a date that has been imported as 3/9/2005 and I need to remove the "/"'s and have the month and date format with zeros if needed such as 03092005, what is the best way to accomplish this on a column. Thank you, Rick For Question # 1 - Multiply by 100 :-)) Just kidding For Question # ...

Calculating dates & creating reports
I need to design a database that will calculate due dates for evaluations. It will have 5 dates. 4 are calculated. These dates need to be on a form along with the employee's name and other vital info. The user should be able to enter that Start Date and have all the other dates calculate automatically. There are approximately 55 employees! The first date (Start Date) is entered by the user. Example: User enters Start Date: 2/01/07. #2 Next Date is 91 days from 2/1/07, which calculates to 5/03/07. #3 Six Month Date is 182 days from Start Date, which calculates to 8/02...

Report to Filter on Next Date Per File
I have a docketing report which reports any action due for a particular file. Theses actions are future dates. A file can have multiple actions due. For example: File ABC12345 Action 6/1/2010 Letter to Client Action 6/9/2010 Payment Due Action 12/10/2010 Status Check File CYO4457 Action 3/1/2011 Letter re Exam Action 6/4/2011 Annuity Due I need to write a report which will only give me the next action due. Using my example the output would be like the following: File ABC12345 Action 6/1/2010 Letter to Client Fil...

Date sensitive cells
Hello there, I am trying to create a contract management system and would like to input all the relevant data into a spreadsheet. A month before a contract runs out I would like all the data relevant to it to be highlighted or in some way made obvious that the contract is coming to an end and needs the attention of the user. Can anyone help me with this? Thankyou very much Let's say that the expiration date of the contract is in cell B1, and you want all of row 1 to turn red when you are within 30 days of that date. Select row 1, the use format / Conditional formatting, select for...

Home Page Content Item: Important Dates (repost)
I asked this question 3 days ago and have received no replies so I'm repeating it. Perhaps the subject name I used: "Important Dates" was too short or sounded like spam. In any case my experience on this board is that even if no one has a solution, someone usually responds saying that he can't reproduce the problem, or that it doesn't work for him either, or that the poster misunderstands what this function is supposed to do. Not having received any response at all, I beg your indulgence with this repost: ***** Money 2006. No Passport. XP Home SP2. P4 3.0 GHz. 1G...

Getting local Time and date for the local computer(vista OS)
Hi I am working with Vista(one of a kind), and have a MFC application and would like to add small code that will get current time and date from the local compuetr. i meet an example in MSDN : http://msdn.microsoft.com/en-us/library/aa390423(VS.85).aspx but it looks too long for what i am looking, is there a 1 function API that returns the Date and the time ? thanks for your time Zack >Hi I am working with Vista(one of a kind), >and have a MFC application and would like to add small code that will get >current time and date from the local compuetr. >i meet an example in MSDN...

Formula For Monthly Date
Hello, I have a worksheet that has products listed that are on sale at different times of the year. My question is what formula to use to get all Jan products,codes,prices etc on to new worksheet, Feb on another worksheet etc. Thanks Mare Use Autofilter. See: http://www.contextures.com/xlautofilter01.html -- Gary's Student "Mare" wrote: > Hello, > I have a worksheet that has products listed that are on sale at different > times of the year. My question is what formula to use to get all Jan > products,codes,prices etc on to new worksheet, Feb on another works...

day of the week and date formula
Hello I am looking to write a formula that gives me the difference in hours between a logged date and time and a closed date and time eg -27/11/2009 09:23:26 and 30/11/2009 10:34:20. Once I have this I need to calculate the working hours used to resolve the issue. If the duration of the time includes a weekend, a saturday would equate to 4 hours working and a sunday would be 0 hours working time, a week day equates to 11.5 hours working. Any suggestions?? Thanks -- Sarah Hi, we need to know what hours during any day are considered working - ie, do all hours...

Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields, starting month, starting year, ending month, ending year. Is there anyway to calculate the range of months they have worked with this set up? So if someone started March 2004 and ended July 2007 is there a formula to calculate the 40 months they worked? DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending month] & "/" & "01" & "/" & [ending year]) "monkeycr84" wrote: > In our da...

problem with dates in csv file
Hi, I'm trying to save a .csv file at the moment, the person who I' sending it to needs the dates in 1 column as dd-mmm-yy, eg 18-Sep-05 eg 09-Sep-05. When I save the file it always reverts the dates 09 o below back to a single number, 9 etc. I've tried copying the column into word, paste special as text formatting the column in excel to text and pasting special back in a text, it works ok but when I save and open the file it reverts back! does anyone know how I could get around this? Many thanks, saybut -- saybu --------------------------------------------------------------...

Balloon Payments
I really need a formula for balloon payment as part of an amortization program. The problem calls for $4,000 loan at 4% for 4 years. I thought it was simple enough, but my answer is wrong. There is no date certain for the lone. Just Month 1. Can someone help I don't think you can use the CUMPRINC function, because it assumes that the loan will be paid off in the number of periods that you specify. That isn't the case in your situation. If the amount of the monthly payment is known, you can set up an amortization schedule with, say Balance due in column D, Payment in column A, column...

Deposits being entered as payment?
When money updates my checking account information automatically (or if i do a manual update to retrieve current checking account info) it enters ALL of the transactions as PAYMENTS. Even the deposits! This of course throws my balance way out of whack. I manually change the payments to deposits but I shouldn't have to do this. Does anyone know why my deposits are not being recognized as such by Money 2005 and are instead showing up as payments? Thanks. This appears to be a known issue. If you have not already done so I encourage you to go to https://webresponse.one.microsof...

Can't open this item. The end date you entered occurs before the start date.
Hi, Everytime I open Outlook 2000 I get a calender pop-up. When I Dismiss it I get a message such as the above and the next time I open Outlook it appears again. I can't see it in the Calender unless I change the view to "By category". I can't delete it or change the date in this view. I just keep getting the above message (see subject line). I tried /cleanreminders and the MS article about Daylight savings but neither work. How do I get rid of this message? I do have a Pocket PC and probably the error occured during a Sync (i have different time zones). ...

How do I delete the test documents in my recent documents?
I took the test for the excel spreadsheets and now they are in my recent documents and I want to know how to remove them, can you help me? Go into Options and set your MRU list to zero. Close Excel and restart. Reset the list to whatever you like. Gord Dibben MS Excel MVP On Sat, 13 Feb 2010 11:17:01 -0800, mrjack <mrjack@discussions.microsoft.com> wrote: >I took the test for the excel spreadsheets and now they are in my recent >documents and I want to know how to remove them, can you help me? ...