Counting events that occure at the same time! 1

[traffas]

I have data listing the [event], [start] and [end] times.

ie:
[event] [start] [end] [autonumber]
20-1 08:00 09:00 1
20-1r 08:30 09:15 2
21-rt 09:10 10:10 3
22-b 10:00 12:30 4
24-5 10:05 11:00 5
25-a 11:00 11:45 6

event 25-a occures between the start and end times of events 24-5 and 22-b. It could be called a "3rd out event". Event 24-5 occures between the start and end times of events 22-b and 21-rt and could also be called a "3rd out event". Event 20-1r's start time occures between event 20-1 start and end time and would there for be a "2nd out event. Can anyone help me with calculating the sum of "2nd out events" and "3rd out events" and "4th out events"....Etc.....

I would like to generate a report monthly during which there will probably be 400 to 450 events.......

Many Many thanks in advance.....

Shawn
traffas7@prairieinet.net

 

[HowieG]

Sounds like you'll need to call a custom vba function from within your query.

Let me know if this is close:

QUERY:

SELECT EventID, StartTime, EndTime, EventName, calcxoutevents([eventid],[starttime],[endtime]) AS Result
FROM tblEvents;

FUNCTION IN A MODULE:

Function CalcXOutEvents(intMyID As Integer, datStartTime As Date, datEndTime As Date) As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim intOtherEvents As Integer
    
    strSQL = "SELECT EventID, StartTime, EndTime, EventName " & _
                "FROM tblEvents " & _
                &quot;WHERE (((EventID)<&quot; & intMyID & &quot;) AND &quot; & _
                    &quot;((([StartTime]-#&quot; & Format(datEndTime, &quot;mm/dd/yy hh:nn&quot;) & &quot;#))<=0) AND &quot; & _
                    &quot;((([EndTime]-#&quot; & Format(datStartTime, &quot;mm/dd/yy hh:nn&quot;) & &quot;#))>=0));&quot;

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    
    If rst.EOF And rst.BOF Then
        'no records
        intOtherEvents = 0
    Else
        rst.MoveLast
        intOtherEvents = rst.RecordCount
    End If
    
    rst.Close
       
    Set rst = Nothing
    Set db = Nothing
    
    Select Case intOtherEvents
    Case 0
        CalcXOutEvents = &quot;&quot;
    Case 1
        CalcXOutEvents = &quot;2nd out events&quot;
    Case 2
        CalcXOutEvents = &quot;3rd out events&quot;
    Case 3
        CalcXOutEvents = &quot;4th out events&quot;
    Case Else
        CalcXOutEvents = &quot;> 4th out events&quot;
    End Select
    
End Function

 

[HowieG]

The result I get is:

EventID	StartTime	EndTime	EventName	Result
1	03/19/02 8:00	03/19/02 9:00	20-1	
2	03/19/02 8:30	03/19/02 9:15	20-1r	2nd out events
3	03/19/02 9:10	03/19/02 10:10	21-rt	2nd out events
4	03/19/02 10:00	03/19/02 12:30	22-b	2nd out events
5	03/19/02 10:05	03/19/02 11:00	24-5	3rd out events
6	03/19/02 11:00	03/19/02 11:45	25-a	3rd out events

 

[traffas]

Many thanks..... will print this out and attempt to impliment it tomorrow evening..... have to go to work early in the morning...... once again, many thanks for your effort and consideration..... I will be sure and let you know how it works.

Shawn
traffas7@prairieinet.net

 

[traffas]

HowieG

I am having trouble using your code. Acess 2000 doesn't use the DAO.Database...... I do not have the 3.6 version of DAO. I have not downloaded the latest version of 'DAO'. I would like to use the ADODB(?) because it will likely be the standard. I have read (without much understanding) in my reference books.... I will continue to read and experiment. I followed the logic of your code and found it facinating. ( I am just starting to learn access ) Could you possible recommend some resources that might help with 'coding' and creating record sets and selecting records? Any help would be appriciated. Also will the code you sent me work if the records are not entered in a particular sequence. Data entry might not be from first to last in time sequence...
I once again thank you for your time and conciderable effort in trying to aid me. And please do not underestimate how much I appriciate your time!

Shawn
traffas7@prairieinet.net

 

[HowieG]

Shawn,

Sorry I haven't had time to get back to this. Have you solved your problem?

If not, you might want to check out:

http://users.bigpond.net.au/abrowne1/appevent.html

It is a SQL-only solution to the problem of conflicting events, as opposed to my VBA solution. I stumbled across this while looking for something else, and thought you might want to give it a try.

 

[traffas]

many thanks, am going there now..... once again, thanks for your time....
shawn

 

[traffas]

HowieG,
A quick question.... I have two fields, one for time and one for date. Is the date stored with the time and just formated to display the time? Or, do I have to join the date and time. If so, could you tell me how?

does the following (pasted from your code) do the join?

&quot;((([StartTime]-#&quot; & Format(datEndTime, &quot;mm/dd/yy hh:nn&quot & &quot;#))<=0) AND &quot; & _
&quot;((([EndTime]-#&quot; & Format(datStartTime, &quot;mm/dd/yy hh:nn&quot & &quot;#))>=0));&quot;

 

[HowieG]

In my test database I was assuming one field called StartTime to hold both time and date. Access can store them together without a problem. Internally it stores the date as whole numbers of days past a particular date, and the fraction is the fraction of a day. So 10.25 would be whatever day in the past '10' represents (too lazy to check), at 6:00am (.25 of a whole day).

No that snippet of code does not 'join' a date and time together. It is subtracting datEndTime (date+time) from StartTime (date+time). All the #&quot;&Format(...,&quot;mm/dd/yy hh:nn&quot&&quot;# stuff creates a string (plain text) version of the date surrounded by #'s (tell access this is a date). So it would evaluate to, say:

&quot;((([StartTime-#03/26/02 16:00#))<=0 AND ((([EndTime]-#03/26/02 16:30#))>=0));&quot;

My suggestion is to use one field to hold both time and date. If you choose to use two fields, the method to join them depends on how you structure the two fields. I would think that if they were both Date/Time fields, you could just add them together (10 + 0.25 = 10.25).

How did you make out with the SQL solution that I gave the link for?

 

[traffas]

I did not know how to either join the date/time fields or create one field that held both date and time. I will go to work and try both methods tonight...... Got cought up reading other articles from the link that you gave me and haven't tried the solutions yet. I work for 24 hours tomorrow. I will post a reply on thursday. Many Many thanks. This looks like it will work great..........

 

[traffas]

HowieG,
initial fields in my table were as follows;
tblEvents - table name
date - formated to date/time
starttime - formated to date/time
endtime - formated to date/time
EventName - text field
EventID - autonumber key field

added two fields both formated to Date/time
dstarttime and dendtime........ used a update query to set dstarttime to [date]+[starttime]
dendtime to [date]+[endtime]
update query appeared to work as fields were filled with the dates and the times in one field.

used the link you gave me to create the following query sql

SELECT tblEvents.EventID, tblEvents.EventName, tblEvents.dStartTime, tblEvents.dEndTime, tblEvents_1.EventID, tblEvents_1.EventName, tblEvents_1.dStartTime, tblEvents_1.dEndTime, ([tblEvents_1].[dStartTime]>=[tblEvents].[dEndTime]) Or ([tblEvents_1].[dEndTime]<=[tlbEvents].[dStartTime]) Or ([tblEvents].[EventName]=[tblEvents_1].[EventName]) AS NoClash
FROM tblEvents, tblEvents AS tblEvents_1
WHERE (((([tblEvents_1].[dStartTime]>=[tblEvents].[dEndTime]) Or ([tblEvents_1].[dEndTime]<=[tlbEvents].[dStartTime]) Or ([tblEvents].[EventID]=[tblEvents_1].[EventId]))=False))
ORDER BY tblEvents.EventID, tblEvents_1.EventID;


( the references to 'Location' did not seem essential to my query and references were deleted.)

when I run the query, it asks me to enter parameter value for 'tblEvents.dstarttime'...... which it should have read from the table?

When i copy and paste the first 'tblEvents.dstarttime' in the parameter window, i get over 180 records? There were only 20 records and the number of calls that overlapped was a lot lower than 180.

NEXT I don't know how to create one field inthe table to hold both date and time. I need remediation.....

Shawn....

 

[HowieG]

Spelling error:

tlbEvents
vs.
tblEvents

Not sure what you meant by &quot;copy and paste the first 'tblEvents...&quot;?

I didn't get a chance to try the SQL code from that link myself. I'll try to do that and see what I get on my end.

 

[traffas]

HowieG

spelling corrections worked and the query worked.... it defined the clashing events just as it said it would.

however, example:
eventid 7 clashes with 10, 9, and 8
eventid 8 clashes with 10, 9, and 7
eventid 9 clashes with 10, 8, and 7
eventid 10 clashes with 9, 8, and 7

the list goes on....

any ideas on how to list the above data in the folowing form:
eventid 7 is a solo event or eventid 7 is first out
eventid 8 is a 2nd event or eventid 8 is 2nd out
eventid 9 is a 3rd event or eventid 9 is 3rd out
eventid 10 is a 4th event or eventid 10 is 4th out

and then total how many first out events, 2nd out events, 3rd out events, 4th out events......etc.....???????

if you would like, I can send you the db. It is a 'expermintal' db where i try different approaches to data. It is not very big and only contains the table with the data, the update query and the query to test for &quot;clashes&quot;?

Shawn
traffas7@prairieinet.net

 

[HowieG]

Sure, send it to galemail@shaw.ca

One question...Assuming:
Event A 9:00-10:00
Event B 9:30-10:30
Event C 10:15-11:00
Event D 10:45-11:30
Evnet E 11:35-11:45

Then would you want:
Event A = 2nd event (conflicts with B)
Event B = 3rd event (conflicts with A and C)
Event C = 3rd event (B and D)
Event D = 2nd event (C)
Event E = solo event (none)
???

Or, do you want to call D a 4th event because it confilcts with C, which conflicts with B, which conflicts with A? Maybe I'm making this too complicated.

 

[traffas]

HowieG

Each event is an ambulance response. In this area, there are 3 ambulance stations. A '2nd out call' is a response made by an ambulance when one of the other two stations is busy. This would mean that there was only one ambulance to cover the whole county. A '3rd out call' would mean that all available ambulances where on assignment and that there was no ambulance available for covering the next 911 emergency call. 4th out calls and 5th out calls can occure when extra crews are added to cover sporting events or patient transfers to other medical facilities....
Tracking this information would be very valualble when considering adding personnel, new ambulance stations and staffing conciderations

many thanks...... will send you the database with the trial data.........

Shawn
traffas7@prairieinet.net

 

[HowieG]

It helps to understand the use. Based on your description, I would say that what you want to know is this:
At the starting point of each event (when a 911 call comes in), how many other calls are in progress (how many other ambulances are busy)?

So, taking some clues from that link I mentioned, I think the SQL solution is:

SELECT tblEvents.EventID, 
   tblEvents.EventName, 
   tblEvents.dStartTime, 
   tblEvents.dEndTime, 
   Sum(-(([tblEvents].[dStartTime]>=[tblEvents_1].
[dStartTime]) And ([tblEvents].[dStartTime]<=[tblEvents_1].
[dEndTime]))) AS Conflicts, 
   [Conflicts]+1 AS EventOut
FROM tblEvents, tblEvents AS tblEvents_1
GROUP BY tblEvents.EventID, 
   tblEvents.EventName, 
   tblEvents.dStartTime, 
   tblEvents.dEndTime, 
   [tblEvents].[EventID]=[tblEvents_1].[EventID]
HAVING (((Sum(-(([tblEvents].[dStartTime]>=[tblEvents_1].
[dStartTime]) And ([tblEvents].[dStartTime]<=[tblEvents_1].
[dEndTime]))))>0) AND (([tblEvents].[EventID]=[tblEvents_1].
[EventID])=False));

I'm going to email back your database (with another couple queries I did up for you ).

 

[traffas]

Many thanks..... it works wonderful

I appriciate your help more then you will ever know....

I am amazed at the time and effort you have put into my thread..... I am amazed at the wonderful help of all the people involved in this forum......

I would like to thank you and everyone for not only helping me, but all the others too.......... I hope your help and patientce will come back to you a hundred fold!

I will try to emulate your helpful spirit......

Shawn

 

[HowieG]

You're welcome. Sometimes the best way to really learn something is to explain it to someone else, so I'm getting something out of this also...

Got any use for the second query I did?

 

[traffas]

HowieG,
Yes... I plan to use your queries for reports for supervisors. I believe that those queries will be very useful.

Shawn