finding a gap and counting the months after the gap

[ftrujill0]

Hi,

I am struggling a lot with a task that I've been recently
assigned...basically I need to find if a member has had a gap in their
membership. If there is a gap in there membership I can only count the number
of months of membership after the gap. If no gap occurred, I count all months.

Here is an example of the data that I'm playing with (I put the data in a
pivot view to help explain):
user_id 072006 082006 092006 102006 112006 122006
99999263 1 1 1 1 1 1
99999264 1 1 1 1
99999265 1 1
99999266 1 1 1
99999267 1 1 1 1 1

So user_id 99999263 would have 6 months of membership without a gap. But
99999264, 99999265, 99999266, 99999267 all have a gap and we should only
count the months after the gap. So 99999264 and 99999266 would have 3 months
and user_id 99999267 would have 4 months of membership. User_id 9999925 would
have zero months (since we are only counting after the gap).

I honestly don't know where to start and have very limited (aka newbie) vba
experience...any help would be grand.

THANKS IN ADVANCE.

 

[Allen Browne]

Hmm: not the simplest thing in the world to calculate.

I'm going to assume that your table is correctly normalized, so it has
fields like this:
User_id Number relates to your table of users
TheMonth Date/Time first of each month
such that each combination of user and month is a different record.

Now to find what's *not* there, you will need a query that gives you each
combination of user and month. To do this, create a counting table: a table
with just one field like this:
CountID Number (Long) primary key
Enter records from 0 to the maximum number of months you will need to
handle.

Create a query using your user table and the counting table, with no join
between them. This gives you every possible combination. You can then create
a calculated field
TheMonth: DateAdd("m", [CountID], #7/1/2006#)

Now use the Unmatched Query Wizard (first dialog when you create a query),
to find the records in this query that are not in your table.

You can now use that as the source for a totals query that groups by the
User_id, and gives you the Max of the month (i.e. the most recent missing
month for each user.)

Having done that, you can use DateDiff() to get the number of months since,
or do a count if you prefer.

Well, that's the kind of technique to follow. Once you get that working, you
may be able to collapse a couple of the steps with a subquery or two:
http://allenbrowne.com/subquery-01.html