Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   General (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=6)
-   -   Calculating gaps between memberships (https://www.access-programmers.co.uk/forums/showthread.php?t=234574)

JobieJo 10-05-2012 09:13 AM

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

pbaldy 10-05-2012 09:20 AM

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


All times are GMT -8. The time now is 06:57 PM.

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