Home Forum Contact

 Access World Forums Calculating gaps between memberships
 Register FAQ Members List Social Groups Top Posters Search Today's Posts

 10-05-2012, 09:13 AM #1 JobieJo Newly Registered User   Join Date: Oct 2012 Posts: 1 Thanks: 2 Thanked 0 Times in 0 Posts Calculating gaps between memberships Hi I am hoping someone can help me with a calculation I am finding incredibly tricky. I am looking at peoples memberships, so have data structured like this: Person ID Membership ID Membership Start Date Membership End Date 112233 98765 01/01/11 31/01/11 112233 12345 01/03/11 31/05/11 112233 67890 01/05/11 31/05/11 So, people can have had more than one membership. What I am trying to find is the difference between the start date and end date of the memberships, and see if any were overlapping. So in the example above I would like to start at the first membership and compare this to the second, so the answer is 1 month difference between end date and next start date, then compare the second to the third, so the answer is -1 month between end date and start date. I am completely stuck as to how I can do this. Does anyone have any ideas? Any help would be greatly appreciated. Thanks Jobie
 10-05-2012, 09:20 AM #2 pbaldy Wino Moderator     Join Date: Aug 2003 Location: Nevada, USA Posts: 33,371 Thanks: 13 Thanked 4,120 Times in 4,052 Posts Re: Calculating gaps between memberships You can use this to get the relevant values on the same record, from which it should be a simple calculation: http://allenbrowne.com/subquery-01.html#AnotherRecord __________________ Paul Microsoft Access MVP 2007-2019 To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
 The Following User Says Thank You to pbaldy For This Useful Post: JobieJo (10-05-2012)

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post KenHigg General 4 05-29-2009 12:15 AM lala Forms 10 04-23-2008 06:34 AM John S Queries 2 12-18-2007 01:25 AM PaulT Reports 5 03-29-2005 06:43 AM benc General 3 04-21-2004 09:28 AM

All times are GMT -8. The time now is 02:50 PM.

 Microsoft Access Help General Tables Queries Forms Reports Macros Modules & VBA Theory & Practice Access FAQs Code Repository Sample Databases Video Tutorials Featured Forum post Sponsored Links