Adding consecutive records

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

I have a database which is completed at the end of every day. Without going
into every field, the main ones that are causing me a problem are:-

I wish to record a Yes/No (or it could be a 1 - 0) to denote whether a
target has been reached or not. What I then want to do is to make a count of
how many consecutive days Yes and how many consecutive days of No so that
the highest number of consecutive days of hitting or missing the target can
be seen and which dates they occurred on.

So the summary I would like to see is something like:-

Highest Consecutive Hits = 8 this occurred on 18th May 2005

or

Highest Consecutive Hits = 8 this occurred 3 times, on 18th May 2005, 21st
July 2005, 7th November 2005

and the same for conescutive None Hits.

Any ideas please?

Thanks

Keith
 
Try that

This Query will return how many Targets "True" and hoe
many hits "False" on every day (if that what you want)

SELECT HitDate, target, Count(target) AS CountOftarget
FROM MyTable
GROUP BY HitDate, target

You can sort it by the CountOftarget and when was most hits
you can also Filter between dates
 
Keith,

This is not really a job for a macro. I would use VBA to create a
User-Defined Function for this purpose, to cycle through the records and
tally the Hits and Misses. The following example may be a little
clunky, but hopefully you will follow the logic...

Public Function TallyHits() As String
Dim rst As DAO.Recordset
Dim i As Integer
Dim Tally As Integer
Dim DateHolder As String
Set rst = CurrentDb.OpenRecordset("SELECT TargetHit, MyDate FROM
YourTable ORDER BY MyDate")
With rst
Do Until .EOF
If !TargetHit Then
i = i + 1
If i > Target Then
Target = i
DateHolder = Format(!MyDate, "dd mmmm yyyy")
End If
Else
i = 0
End If
.MoveNext
Loop
.Close
End With
TallyHits = Tally & " consecutive hits on " & DateHolder
Set rst = Nothing
End Function

This is "air code", not tested, and also only goes part of the way, in
that it only identifies the most recent time the max hits was achieved.
But hopefully it will point you in the right direction. Then all you
need to do is put =TallyHits in the Control Source of an unbound textbox
on your form/report. Make a similar function to tally the Misses.
 
Back
Top