Formula to identify consecutive transactions

[vcujackson]

I have an access table with 15 totals based on a year (ie 94-95, 95-96 and so on). I need to flag those records in which a person gave at least 100.00 for 5 consecutive years within that 15 year period. I have 4000 records to check. Is there a way to do this in access programatically without analysis of each row manually? I am thinking this requires some type of randomizer function but not sure

 

[lespaul]

can you post your table structure? how do you know how much a person has contributed?

Leslie

Come join me at New Mexico Linux Fest!

 

[alvechurchdata]

I think you're going to have to write eleven expressions to sum the eleven possible groups of five adjacent fields.

If your field names are predictable then you could build the names in code inside a loop but that scarcely seems worth the effort for eleven lines like:

if(total94 + total95 + total96 + total97 + total98 > 100) or _
  (...) or _
  (...) or _
  (...) or _
  (total04 + total05 + total06 + total07 + total08 > 100)

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MajP]

Public Function getConsecutive(ParamArray varValues() As Variant) As Boolean
  Const intConsecutive = 5
  Const intAmount = 100
  Dim varVal As Variant
  Dim intCount As Integer
  
  For Each varVal In varValues
    If Nz(varVal, 0) >= 100 Then
      intCount = intCount + 1
    Else
      intCount = 0
    End If
    If intCount = 5 Then Exit For
  Next varVal
  If intCount >= 5 Then getConsecutive = True
End Function
[code]

to use in a query you pass the field names in
getConsecutive([field1],[field2],[field3]...[field15])

 

[PWise]

How about

Select "94-98" As range total94 , total95 , total96 , total97 , total98
From Tablenamw
total94 >=100 and total95  >=100 and total96  >=100 and total97  >=100 and total98  >=100 
Union
Select "95-99" as range, total95 , total96 , total97 , total98,total99
From Tablename
where total95 >=100 and total96 >=100 and total97 >=100 and total98 >=100 and total99>=100
....