10 consecutive years

[jhansiblu]

I have atable the is storing ID, attendance_year and meeting code. Iw ould like to query the table and identify the IDs that have 10 consecutive attendance years no matter when it may have occured. So IDs have more than 10 row of data but not necassrily 10 years in a row.

How can I do this?

table looks like

123|2003|WIN03
123|2001|SUM02
456|2001|WIN01
456|2000|WIN00
456|2002|SPR02
456|2006|SUM06

 

[CraigDolphin]

I think you're going to have to write some VBA code to do this task since you want to know if this 10-consecutive year condition has ever been met (would be simpler if the question was something like 'has the most recent 10 year span been attended consecutively for each id".)

First thing you'll need to do would be to create a public function to determine if this condition is true for each ID.

<Air code>

Public Function TenYearSpan(person as integer) as boolean
'place this code in a public module
'change field and table names to match those in your table.
'Assumes your year field is an integer, not text. 
'Also, this code uses DAO so you'll need to ensure that the DAO reference is checked from the VBA editor
'menu Tools>References.

Dim db as DAO.database
Dim rs as DAO.recordset
Dim strSQL as String
Dim consecutive_count as integer
Dim prev_year as integer
Dim myflag  as boolean
myflag = false

set db = CurrentDb
strSQL = "Select [[COLOR="Red"]YearFieldName[/COLOR]] From [[COLOR="red"]TableName[/COLOR]] Where (([[COLOR="red"]IDFieldName[/COLOR]]=" & person & ") AND ([[COLOR="red"]YearFieldName[/COLOR]] & "" <> "")) Order By [[COLOR="red"]YearFieldName[/COLOR]] ASC"

set rs = db.OpenRecordset(strSQL,dbOpenDynaset)

If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF
   if rs![COLOR="red"]YearFieldName[/COLOR] -1 = prev_year then
      consecutive_count = consecutive_count+1
      if consecutive_count >= 10 then myflag = true
   else
      consecutive_count = 1
   end if
   prev_year = rs![COLOR="red"]YearFieldName[/COLOR]
   rs.MoveNext
Loop

TenYearSpan = myflag
End Function

Then in a query, or control source for a form control, call the function for each ID using something like:
ConditionMet: iif(TenYearSpan([IdFieldName]),"Yes","No")

 

[jhansiblu]

Recent 10 years

How would I query the table if I wanted the easier option of the 10 most recent years?

 

[CraigDolphin]

Create a query that lists all records where the year field is >= DatePArt("yyyy",Date())-9. Group by Year and PersonID and don't include the meeting code.

Then do a dcount of the query where the personid = (whatever). If the result is less than 10, then they haven't attended a meeting in each of the years during that time span (includes the current year).

 

[jhansiblu]

Worked Great

Thanks, that worked well for me.

 

[CraigDolphin]

Glad to help