Count events

[Guest]

I'm beginning to worry that I might be overstaying me welcome in the
newsgroup at the moment. I've posted a few questions in the last days (all
with successful replies) and I'm worried of giving the impression I'm just
using the newsgroup to build my project for me ..... I hope this is not the
impression received and beg patience for another question.

My table contains large quantity of data (approx 6.5 k records).

Relevant fields for my query are

Timestart "datetime"
Timeend "datetime"

I am trying to establish how many events are currently "live" or "occuring"
at any given minute of the day. For example

Record 1 = Time start 13:16:13, Timeend 13:23:17
Record 2 = Time start 13:20:32, Timeend 13:30:47

Would result in something like

13:16 = 1
13:17 = 1
......up to 13:19 = 1 .... then
13:20 = 2
13:21 = 2
13:22 = 2
13:23 = 2
13:24 = 1

If I may blow my own trumpet for a moment I can tell you that I have easily
achieved this in Excel using an array formula. My organisation requires the
project that I have used for three years to be transferred from Excel to
Access. My question - is such a query achievable within Access and have I
provided enough info for those looking at this question to understand my
needs.

As always any help or pointers would be appreciated.

Regards

Michael Bond

 

[Wayne Morgan]

Would a statement similar to this do what you're wanting?

DCount("*", "MyTable", "TimeStart <= #" & Format(Time(), "hh:nn") & "# And
TimeEnd >= #" & Format(Time(), "hh:nn") & "#")

 

[Guest]

Hi Wayne

Thanks for the posting

I'm not sure that this will work for a couple of reasons, but that may be me
not understanding your suggestion. If I can expand on this....

1. I think, if I could get this to work, it will only record against the
minute of the start time of the event and not for each subsequent minute that
the event is in progress
2. Shouldn't I have some criteria set in the "TimeStart <=" section of the
statement.
3. Because I am not familiar with DCOUNT I tried the following simple test
as a learning exercise:
Table with one record StartTime = 03/10/2005 11:40:00 EndTime = 03/10/2005
11:50:00
I put the expression DCOUNT("*", "SampleTable", TimeStart => #11:40:00#")
and got a count of 1 as expected
I changed the expression to DCOUNT("*", "SampleTable", TimeStart =>
#11:41:00#") and got a count of 1 which I didn't expect.
I changed the expression to DCOUNT("*", "SampleTable", TimeStart =>
#11:39:00#") and got a count of 1 which I didn't expect.

Afraid I'm stumped on my undertsanding of this particular function cos what
I wanted to see was

11:39 0
11:40 1
11:41 1
11:42 1
11:43 1
11:44 1
11:45 1
11:46 1
11:47 1
11:48 1
11:49 1
11:50 0

Any further ideas?

Regards

Michael Bond

 

[Wayne Morgan]

You have "=>" not "<=". This is the reverse of what it should be. Also,
there are some functions that don't like the equal sign first, but I don't
remember which ones. You are also testing a time against a date and time.
Test it against just the time component unless you're going to include the
date in the test value also.

TimeValue(TimeStart)

 

[Guest]

Wayne

thanks for correcting my errors in the formula

I've made the changes in my test data but I still believe it does not get me
where I need to be

with

TimeStart = 03/10/2005 11:40:00
TimeEnd = 03/10/2005 11:50:00

I get a one line result of

03/10/2005 11:40:00 03/10/2005 11:50:00 1

where I need 10 lines for each minute in that period.

I tried linking the query to a table which lists every minute of the day and
used that field as the TimeStart value in the query but that resulted in a 1
in every line of the query (all 1440 of them), whereas I wanted a 0 up to
11:39, a 1 from 11:40 to 11:50 and a 0 from 11:51 onwards

I think I'm getting myself more befuddled with each attempt!

Regards

Michael Bond

 

[Guest]

Wayne

I've just had a thought .... should I be better doing this in a VBA module
rather than a query ..... I should loop through each of 1440 minutes records
in my Minutes table and use the dcount function to compare with each record
in the Events table setting the value of the Result field accordingly?

Michael Bond

 

[Wayne Morgan]

Yes, this would probably be better in VBA. The DCount is actually in lieu of
a query. However, you shouldn't need to "loop through" the records. The
DCount should tell you how many records for the given minute are "active"
for that minute. It will return a single value, the number of records
"active" for that minute.

 

[Wayne Morgan]

Are you wanting to return all of the records that are active for any
particular minute, not a count of them? If so, you would use the same
criteria as is in the DCount statement in the WHERE clause of the SELECT
query. The query will then return one row for each record "active" during
the specified minute.

 

[Guest]

At this stage of the project just the count is needed .... however I will use
your advice and sneakily "future proof" the app against the day the boss says
"and is it possible to say what those events were at 15:13 in the afternoon"

Thanks again

Michael Bond

 

[Guest]

Wayne

thanks for this, especially how to avoid the need to use the loop.

Apprciate the help

Michael Bond

 

[Guest]

Wayne

Extremely grateful for your help.

Eventually settled on the following

Sub CountOperators()

With CurrentDb.OpenRecordset("MinuteIntervals", dbOpenDynaset)
Do Until .EOF ' To step through all records
.Edit
!CountOfOps = DCount("*", "SampleLogIn", "TimeValue(TimeStart) <= #"
& !Time & "#" & _
"And TimeValue(TimeEnd) >= #" & !Time & "#")
.Update
.MoveNext
Loop
End With

End Sub

The MinutesInterval table contains each of the 1440 minutes of the day. The
SampleLogIn table contains the event start and end times. The result updates
the MinutesIntervals table with the CountOfOperators from the SampleLogIn for
each minute exactly as I needed.

Thanks again for steering me down the right road.

Regards

Michael Bond