Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 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
JobieJo is on a distinguished road
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

JobieJo is offline   Reply With Quote
Old 10-05-2012, 09:20 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,096
Thanks: 13
Thanked 4,075 Times in 4,010 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
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.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
JobieJo (10-05-2012)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Gaps in data KenHigg General 4 05-29-2009 12:15 AM
finding gaps lala Forms 10 04-23-2008 06:34 AM
Find gaps between activities John S Queries 2 12-18-2007 01:25 AM
Big ugly gaps in my reports?? PaulT Reports 5 03-29-2005 06:43 AM
Memberships benc General 3 04-21-2004 09:28 AM




All times are GMT -8. The time now is 12:15 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


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World