Counting Consecutive Months in Access

[CoolDude2000]

Please look at the example table below. I need to get the total counts of the consecutive months for each Fname. Please help.

Results should be:
Fname Total Consecutive Count
John doe 4
Test 2
Fname Fdate
john doe 10/1/2008
john doe 9/1/2008
john doe 8/1/2008
john doe 7/1/2008
john doe 5/1/2008
john doe 4/1/2008
john doe 3/1/2008
Test 4/1/2008
Test 3/1/2008
Test 12/1/2007

 

[CoolDude2000]

Results should be:
Fname Total Consecutive Count
John doe 4
Test 2
Fname Fdate
john doe 10/1/2008
john doe 9/1/2008
john doe 8/1/2008
john doe 7/1/2008
john doe 5/1/2008
john doe 4/1/2008
john doe 3/1/2008
Test 4/1/2008
Test 3/1/2008
Test 12/1/2007

 

[CoolDude2000]

I tried this query below but it is running long in Access database not retrieving any results.

SELECT T1.fName, COUNT(T1.fDate) AS CountOffDate
FROM (SELECT DISTINCT T.fName,
T.fDate-Day(T.fDate)+1 AS fDate
FROM MyTable T) AS T1
WHERE T1.fDate>=
(SELECT MAX(T2.fDate)
FROM (SELECT DISTINCT T.fName,
T.fDate-Day(T.fDate)+1 AS fDate
FROM MyTable T) T2
WHERE T2.fName=T1.fName
AND T2.fDate-31>
(SELECT MAX(T3.fDate-Day(T3.fDate)+1)
FROM MyTable T3
WHERE T3.fName=T1.fName
AND T3.fDate-Day(T3.fDate)+1<T2.fDate
)
)
GROUP BY T1.fName;

 

[Galaxiom]

Do a self join on FName. This will give you one record for every combination of FDate pairs for each FName.

Use DateDiff to get the number of months between dates pairs.

Compare this to the number of records between the two months. Where the count of records is the same as the difference in months then a record exists for every month between the two dates.

Hope you can make sense of this concept.

 

[CoolDude2000]

<Done>Do a self join on FName. This will give you one record for every combination of FDate pairs for each FName.

Use DateDiff to get the number of months between dates pairs.<Done>

Compare this to the number of records between the two months. Where the count of records is the same as the difference in months then a record exists for every month between the two dates. <Confused>

Can you please explain more in details?

Thanks for all your help Mr.Galaxiom.

 

[MarkK]

But for larger data sets won't this practically grind to a halt using SQL? I would do this in VBA using a recordset.