misc said:
Ideally, i wish to run a daily report on attendances.
For the following data....
index membername timeattended
1 Tom 08.55
2 Tom 09.54
3 Tom 14.00
4 Tom 14.02
5 Tom 15.08
6 John 15.09
Attendances would equal 4, as index 1 and 2 are within 1 hour for Tom, index
3 and 4 are within 1 hour for Tom
I think you missed the point of my earlier question. What I
don't think you've completely explained is what's supposed
to happen when two or more times fall within an hour, but
**some** of them fall into a clump with another time that is
not within the first clump.
However, I took a guess that you want to start another clump
at least another hour after the previous clump started. If
so, then I think you're goiint to have to add a field to the
table (I can't figure a way to calculate it in a query) to
save the calculated clump time. Then, before running the
report execute this procedure to set the clump time for each
record so you can use it to group on.
Sub Clumps()
Dim db As Database
Dim rsClumps As Recordset
Dim dtmGrp As Date
Dim strPerson As String
Set db = CurrentDb()
db.Execute "UPDATE table SET ClumpTime = Null WHERE
ClumpTime Is Not Null"
Set rsClumps = db.OpenRecordset("SELECT membername,
timeattended, ClumpTime FROM table ORDER BY Person, Start",
dbOpenDynaset)
With rsClumps
dtmGrp = !timeattended
strPerson = !membername
Do Until .EOF
If strPerson <> !membername Then
dtmGrp = !timeattended
strPerson = !membername
End If
If DateDiff("n", dtmGrp, !Start) >= 60 Then
dtmGrp = !timeattended
End If
.Edit
!ClumpTime = dtmGrp
.Update
.MoveNext
Loop
.Close: Set rsClumps = Nothing
End With
Set db = Nothing
End Sub
I'm not proud of this (and you will need to add error
handling), but it's the best I could come up with. Maybe
someone in the queries newsgroup can come up with a way to
use SQL instead of this funky recordset approach.
--
Marsh
MVP [MS Access]
anon said:
3 fields, [index], [membername] and [timeAttended]
Ideally, each member would only attend once per day. Then i could get a
select distinct query to produce an exact number of attendances.
However, member attend more than once per day.
I would like all member attendances within 60 minutes to count as 1
attendance.
"Marshall Barton" wrote
Maybe clearer, but much more complicated. What do you want
to do with times like
1:02pm
2:00pm
2:05pm
3:00pm
Think about it carefully, because I'm not at all sure there
is a consistent way to determine clumps from that kind of
data.
misc wrote:
I have a large table with over 1000 entries.
eg
member1 1:00pm
member2 1:01pm
member1 1:02pm
member3 2:00pm
member1 1:03pm
member1 5:00pm
If i count the number of entries, then i get 6.
I wish to count the entries at 1:00pm, 1:02pm and 1:03pm as 1 entry, not
2
how do i get a list of entries, but only count those that are a few
minutes
apart as 1
eg attendances = 4, as member 1 has attended twice, not 4 times?
"Marshall Barton" wrote
This is an ill defined problem. What separates one entry
from another? The hour?? Is it a text field or is it a
date/time field?
If it's a date/time field you want to "clump" by the hour,
then you can use a query likeL
SELECT DISTINCT member, Hour(timefield) As Attended
FROM thetable
