Count Consecutive months

[jpaokx]

Hi All,

I was wondering if there is someone who can help me out with something here.

Ok...We have a retail shop and we want to see for each customer if he had bought at least once a month a specific product consecutively. To see the concept: If he had bought the product let say 2 two times in January-2014, 1 time in February-2014 and 3 times in March-2014 and nothing else after that, I want simply to show 3 (times). Of course, a customer may have bought the product at a later date, let say, 5 months after, but in this case, I will need another count again for him.

Now, the example:

What I have is:
CustomID | date of purchase

44444 | 01/01/2013
44444 | 05/02/2013
44444 | 16/02/2013
44444 | 26/03/2013
44444 | 18/05/2014
44444 | 29/06/2014
55555 | 05/09/2013
55555 | 01/10/2013
55555 | 10/10/2014
66666 | 11/11/2012


What I need to show:

CustomID | 1st_time_of_consecutive_purchases | 2nd_time_of_consecutive_purchases |...the list may go up to 8 times, but for simplicity purposes let see for up to 2 times.
4444 | 3 | 2 |
5555 | 2 | - |
6666 | - | - |


I hope that the example is clear. If someone give me some directions about how to make these counts for consecutive months in Access, that would be great!

 

[MarkK]

What I would do first is write a query that Groups By CustomerID, Month, and Year . . .

SELECT CustomerID, Month(PurchaseDate) As Month, Year(PurchaseDate) As Year
FROM YourTable
GROUP BY CustomerID, Month(PurchaseDate), Year(PurchaseDate)
ORDER BY PurchaseDate

Then I would open that query in a recordset, and loop thru each record, and check from loop to loop if the months are consecutive. Of course you need to check if customer changes too, and year, and if month = 12, but in broad strokes, that will work.

Makes sense?

 

[plog]

we want to see for each customer if he had bought at least once a month a specific product consecutively

You didn't provide good sample data then. Somewhere you would need to take into account the specific product.

I like Markk's method, but I think you can do this without using VBA (it may not be as easy, but it is possible). If you need a pure SQL solution, post better sample data. Include table and field names, as well as more examples and your product field.

 

[jpaokx]

Hi Mark,

Thanks for the post. Using SQL, what I am getting is this one:

ID Month YEAR
44444 1 2013
44444 2 2013
44444 3 2013
44444 5 2014
44444 6 2014
55555 9 2013
55555 10 2013
55555 10 2014
66666 11 2012

How am I supposed to count the consecutive months using distinct ids (like the example that gave earlier)?


@Plog: I didn't specify which product, because I don't care about the type of the product. The purpose of this task is to see which customers are buying something at least once a month and for how long? So, I see all products overall (not distinguish the type of products). I need to explore their buying habits/frequencies. Does it make sense? Sorry if I didn't make this clear earlier.

 

[MarkK]

Here's a very simple code sample. See how you can detect a consecutive month?

With Recordset
    Do While Not .eof
        If !Month - lastMonth = 1 Then
            [COLOR="Green"]'This is a consecutive month[/COLOR]
        End If
        lastMonth = !Month
        .MoveNext
    Loop
    .Close
End With

You have some challenges, like when month = 12, and if ID changes, and so on, but do you get the idea?