quantifying overlapping time spans

Hi all,

I am trying to write a query to calculate the amount of time that any two
individuals overlap in their use of a location. My table currently looks like
this:

Date   Time   IndividualID   ArrivalTime   DepartureTime

Individuals can arrive and depart at different times, and stay for different
amounts of time. Individuals remain at the location for as little as a few
seconds or up to 20 minutes. I am trying to determine the following:

1) for each pair of individuals, how much time were they both at the location?
(shared presence)

I need the results to appear something like this:
 
Individual1   Individual2    SharedPresence (in minutes)
A                     B             0.2
A                     C             1.4
A                     D             10.0
B                     C              4.2
B                     D              0.9
....etc.
      

2) at any one time point, I need to be able to determine all of the
individuals present

Any ideas? I have some experience with Access and writing queries, but
haven't gotten into writing macros or anything like that. Any comments or
suggestions would be very appreciated.

Thanks for your time,

Liz

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
EHobs
6/4/2010 11:46:56 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
886 Views

Similar Articles

[PageSpeed] 37

EHobs via AccessMonster.com wrote:
>I am trying to write a query to calculate the amount of time that any two
>individuals overlap in their use of a location. My table currently looks like
>this:
>
>Date   Time   IndividualID   ArrivalTime   DepartureTime
>
>Individuals can arrive and depart at different times, and stay for different
>amounts of time. Individuals remain at the location for as little as a few
>seconds or up to 20 minutes. I am trying to determine the following:
>
>1) for each pair of individuals, how much time were they both at the location?
>(shared presence)
>
>I need the results to appear something like this:
> 
>Individual1   Individual2    SharedPresence (in minutes)
>A                     B             0.2
>A                     C             1.4
>A                     D             10.0
>B                     C              4.2
>B                     D              0.9
>...etc.
>      
>
>2) at any one time point, I need to be able to determine all of the
>individuals present


To find all overlaps you can use comparisons like:
	start1 < end2 And start2 < end1
and the duration of the overlap can be calculated with an
expression like:
	((end1 - start1) + (end2 - start2) - Abs(start1 - start2)
- Abs(end1 - end2)) / 2

This can all be done with a query:

SELECT table.IndividualID As Individual1,
		X.IndividualID As Individual2,
		(DateDiff("n", table.ArrivalTime, table.DepartureTime)
			+ DateDiff("n", X.ArrivalTime, X.DepartureTime)
			- Abs(DateDiff("n", table.ArrivalTime, X.ArrivalTime)
			- AbsDateDiff("n", table.DepartureTime,
X.DepartureTime)
			 ) / 2 As SharedPresence
FROM table, table As X
WHERE table.IndividualID <> X.IndividualID
		And table.ArrivalTime < X.DepartureTime
		And X.ArrivalTime < table.DepartureTime

To find all the individuals at any point in time, the query
is simpler:

SELECT table.IndividualID
FROM table
WHERE [Enter Time] Between table.ArrivalTime
												And table.DepartureTime
-- 
Marsh
MVP [MS Access]
0
Marshall
6/5/2010 1:02:22 PM
Reply:

Similar Artilces:

<span> and filters
Hi, I have a lot of junk mail coming in and my filters take care of most of it. The problem I have is with people using the <span> function to disguise junk for instance: VIA<span fontcolor="#black">GRA</span> I had hoped that an outlook rule would recognise <span and send it to my junk e-mail box but it doesn't. Any advise would be greatly appreciated. "CGR" wrote in message news:9BA07BE1-7ACA-4179-882B-FAFF71FDE25D@microsoft.com... > > I have a lot of junk mail coming in and my filters take care of most > of it. > The proble...

Displaying Time on the Calendar
In 2003, you could schedule an appt. for 11:15-11:45 and those times would appear on the calendar. In 2007, the times don’t appear. Is there a way to make them appear? What time scale do you use? Which view? They should show if they are out of the scale - if the appointment length is not a multiple of the time scale you are viewing, the times will show. http://www.outlook-tips.net/archives/2009/20090610.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dai...

first time connection after booting
Hi I would be grateful for any help with this. I embedded WMP in MSAccess 2003. The first time I open WMP set to a url avi file on my local drive it is super slow. BUT THIS IS ONLY AFTER I BOOT THE COMPUTER. If I close and reopen the db when the system is on it is fine until I restart the computer and again have the same issue. I am using vista 64. Your help is greatly appreciated. Alan set to autostart strURL = "c:\mylocation" Me.WindowsMediaPlayer0.URL = strURL DoEvents Never heard of WMP in Access 2003,only in visual basic & visual studio.. Youre best bet ...

Run time error 2465
I'm trying to open an access db and upon loading or opening I get Run Time Error 2465 Can not find field 'cmdLock' referred to in your expression. When I go to Debug I see it highlight the line in question: frm!cmdLock.Caption = IIf(bLock, "Un&lock", "&Lock") It seems OK, what am I missing? One other strange thing, (well strange to me) is that this db is located on a network drive. No one else has this problem, but me? Any ideas? Hi Rock, When an application works on other PCs, but fails on one PC, this is usually a sign of a MISSING reference...

Missing printers causing long boot times?
A Vista Business 64-bit was booting fine until an Epson printer decided that the ink was low, put a two page document into the queue, and never operated properly since. We first deleted the printer object, connected to another printer on another workstation, and were able to print. But that failed on the second attempt to print, so we waited for the refurb Lexmark to arrive. The Lexmark works fine, prints like a champ, but the boot still takes forever. Is there a log that I can look at to determine if time is being lost while the boot process looks for the old Epson printer...

Calculating sum of two given times.
Is there a way to calculte the sum of two given times? For example, A1 says 08:00 and B1 says 17:00 then C1 will auto calculate "9". Also, if it can be done, because my time is always based on quarter hour, will the end result show a fraction? Example, A1is 07:45, B1 is 17:00, then C1 is 9 1/4. << Is there a way to calculte the sum of two given times? For example A1 says 08:00 and B1 says 17:00 then C1 will auto calculate "9".>> =(B1-A1)*24 and format Cell C1 as *_general_* << Also, if it can be done, because my time is always based on quarte hour...

Time Values
I have Time values in column A i.e 2:30, :45. I want to add a column showing these times as values. I want column B to show 2:30 as 2.5 hours and :45 as ..75 hours. Is there a formula I can use? Thank You!! Try... =A1*24 ....and format as 'General'. Hope this helps! In article <AEF5E34F-CD93-4E49-AD37-7B1785876D8E@microsoft.com>, "tojo107" <tojo107@discussions.microsoft.com> wrote: > I have Time values in column A i.e 2:30, :45. I want to add a column showing > these times as values. I want column B to show 2:30 as 2.5 hours and :45 as > .75 ...

Convert Text Time to Time
I have text in the following format: 800 830 900 930 How can I convert these text to 8:00 AM, 8:30 AM etc... Thanks, M -- mdalb ----------------------------------------------------------------------- mdalby's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=705 View this thread: http://www.excelforum.com/showthread.php?threadid=26248 Hi try: =INT(A1/100)/24 + MOD(A1,100)/(24*60) >-----Original Message----- > >I have text in the following format: > >800 >830 >900 >930 > >How can I convert these text to 8:00 AM, 8:30 AM etc... >...

SQL Time on Worksheet 250/260
We are creating a rather large worksheet 250/260(> 33,000 items). It works fine on one of our stores (running SQL 2000 SP4 same location as HQ) without any issues. When we run the worksheets for the other store (SQL 2005 Express), it runs for an indetminiate time (about an hour) and fails. The worksheets run if we break the number down to less than 5000 items. Ref. No.: 52610 4/15/2007 10:58:56 AM Updated 3 record(s) in table 'QuantityDiscount'. 4/15/2007 10:58:57 AM Updated 3 record(s) in table 'ItemClass'. 4/15/2007 11:47:01 AM <<ExecuteCommand>...

overlapping textboxes
Is there a way to save textboxes that are in a layout overlapping one another as a pic, the way text can be saved as a pic? What prevents you from overlapping? -- Don - Publisher 2000� Vancouver, USA "mazzi99@aol.com" <mazzi99@aol.com@discussions.microsoft.com> wrote in message news:8166126F-33C2-4CE7-B308-5D063F0330F6@microsoft.com... > Is there a way to save textboxes that are in a layout overlapping one > another > as a pic, the way text can be saved as a pic? Which version of Pub are you using? -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Express...

Superscripts & Overlapping Text
I wish to place a trademark symbol "TM" close to my text and change its font size to make it smaller. When I make it a smaller font size and make it a superscript its vertical position changes to be too low. So I've tried to put the TM in it's own frame box, but when I move it close to the other text, that text disappears. I've tried sending the objects forward and back and making them transparent to no avail so far. Any ideas? Thanks After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Lefty Smith <leftyssmith@netscape.net>... > I...

Workflow Time out
I am trying to write a workflow with a timeout so an email can be sent 2 months prior to a specific date field. The workflow fire but just keeps waiting even past the 2 month time frame. I am not sure what I am doing wrong The workflow states: workflow -> Timeout -> Equals months:2 Before Client License Information: Maintenance Exp. Date. I believe this is correct. Can some please let me know what I am doing wrong. Thank you I've seen issues sometimes with workflow timeouts or wait states which fail to trigger if the condition is already met when the workflow runs. ...

Oulook logging the wrong time
When I send a message from Outlook, the recipient within the same office gets it as being sent an hour later. The client sending the mail has the clock set for the right time and the time zone is also correct. No other computers in the office are having this issue. Thanks Sorry - forgot to check the savings time "boe" <boe_d@hotmail.com> wrote in message news:Of20toraEHA.3988@tk2msftngp13.phx.gbl... > When I send a message from Outlook, the recipient within the same office > gets it as being sent an hour later. The client sending the mail has the > clock set...

entering time into a cell
Hi All i have a friend who says that she used to have a workbook where she would type 8.3 into a cell and it would automatically change to 8:30 etc .. .after her system was upgraded (don't know the specifics) this no longer works ... any ideas? Cheers JulieD Hi maybe she had applied a custom format such as hh.mm -- Regards Frank Kabel Frankfurt, Germany "JulieD" <JulieD@hctsReMoVeThIs.net.au> schrieb im Newsbeitrag news:eZntRPshEHA.592@TK2MSFTNGP11.phx.gbl... > Hi All > > i have a friend who says that she used to have a workbook where she would > type 8...

Overlapping time intervals
Hi All I have three jobs that a piece of equipment can perform: one to put away, and two to pull out. Each job has one start time and one end time and the three jobs may overlap either or both of the other two. What I would like to determine are discrete answers for: How many hours is only one job active; How many hours are two jobs active; How many hours are three jobs active? The first answer is the total hours minus the sum of the other two calculations and I can get the hours for two jobs with: =IF(OR(Finish1<Start2,Start1>Finish2),0,MIN(Finish1,Finish2)-MAX (Start1,Start2)) (...

Time Comparisons
I am having some problem comparing 2 time values. When I compare 28:0 (mm:ss)(H9) to 28:12 (H10) (formula: if(H10>H9, "Pass", 'Fail"), I ge "Fail". But when I do the same thing using lookup, I get "Pass" if(H2>(Lookup(Sheet3!A2,Minimums!A2:A35,Minimums!D2:D35)),"Fail","Pass") Sheet3!A2 is age of the person Minimums!D2:D35 is time based on age, A2:A35 I also have a formula that displays"Fail" if any one event display "Fail" if(or(c2="Fail",F2="Fail,I2="Fail"),"Fail","...

the PowerShell ?? regular expression quantifier
The ?? PowerShell regular expression quantifier is defined "Zero or one matches, matching as little as possible." Wouldn't that always be zero matches? Can someone provide an example of how to fruitfully use ?? as a regular expression in PowerShell? - Larry On Apr 3, 3:08=A0pm, Larry__Weiss <l...@airmail.net> wrote: > The ?? PowerShell regular expression quantifier is defined > > =A0 "Zero or one matches, matching as little as possible." > > Wouldn't that always be zero matches? > > Can someone provide an example...

Times New Roman Bold in Word for Windows not showing up correctly in Word for Mac
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I created a Word document in Word 2007 on Windows XP. The document has certain text formatted as &quot;Times New Roman&quot; with bold (I'll refer to this text as the &quot;Test Text&quot;). The font dialog box shows the font type for the Test Text as &quot;Times New Roman&quot; and the &quot;bold&quot; option is selected. <br><br>I emailed that document to my Mac OS X and opened it with Word 2008 on my Mac. The document formatting for the document as a whole look...

Question on Time zone settings on mailboxes
We have a 2-node Exchange cluster that houses mailboxes for everyone in the company. The servers are in Eastern time zone, but our users are all over the country. We applied all the DST patches to the OS and Exchange 2003 itself, and ran the Exchange Calendar Update tool. When we ran the tool, it came up with about a dozen "conflicts" and a slew of "nonexistents" (users whose timezone couldn't be determined). This leads me to a couple of questions: 1) should I be setting the time zone for my users to the location they are in? Or should they be set to what time zone th...

Calculating Clock start time in Excel
Hi, I am trying to calculate when an inquiry comes in to our helpdesk outside working hours and when it was answered based on the follwing example. Example: Working hours are 08:00 - 17:00 Monday to Friday. If inquiry comes after 17:00, then the inquiry is attended the next working day and an answer is guaranteed by the close of following working day. If the inquiry comes during working hours, again the garantee is for the answer by close of next working day. I need to be able to measure how long an inquiry has missed the promised deadline by. Can anyone help please? -- Hani Muhtadi -...

Time formulation?
I am trying to figure time worked on a daily basis. I have done thi formula before, but have forgotten how to complete it. My times look like (2nd shift hours) 3:30 PM 12:30 AM The formula needs to calculate hours worked greater than 5.5 to tak out 1/2 hour lunch, and hours worked less than 5.5 to calculate tota hours with no lunch. We do not clock in and out for lunch breaks, i is an automatic deduction, but somebody has earased my formula. Please help! -- LGrob ----------------------------------------------------------------------- LGrobe's Profile: http://www.excelforum.com/memb...

overlapping elements in tabs
hello, how can i manage to avoid overlapping elements in tabs. for example: if i have a group box and want to use a check box in the group box header sometimes it works, but most of the times the check box is hidden under the group box. is there an option to 'order' the elements like we know it from powerpoint? thanks for the help - happy easter manuel "baeman" <baeman@gmx.net> wrote in message news:1635d65f-03ec-480a-a8db-ff3d12543281@s37g2000prg.googlegroups.com... > hello, > > how can i manage to avoid overlapping elements in tabs. > > for examp...

Access 2007 Run-time Error 2467 when accessing a listbox value on a sub form
I have a database that has been in production on Access 2003 for quite some time and has run without errors. A user's machine was recently upgraded to Access 2007 and now the database throughs a Run-time Error 2467 "The Expression you entered referes to an object that is closed or does not exist" on the following line that references a listbox value on a sub form. If Nz(Me.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = "" Then .. .. .. I changed the code to If Nz(Forms! frm_Main.sfrm_Consultant_Contract_Request.Form.Cmb_Consultant.Value) = ""...

How can I Sum time duration?
I have a spreadsheet that is output from an in house system. One of the columns is "Duration", and is of the form "+0 hh:mm:ss.xxx", e.g. "+0 00:09:11.983" where I'm guessing +0 is the number of days and is always 0 in my examples, and hh:mm:ss is hours:minutes:seconds and xxx is milliseconds. I want to be able to add all of the Durations to arrive at total time, preferably as a largish number of minutes, typical totals being up to millions of minutes. I've tried using Sum, but this returns 00:00:00 I'm sure there must be an easy way to do thi...

Opening a word file opens "New" every time
Every time I open a file from a folder by double-clicking it opens as "New" and I'm unable to save it over the same file. In order to open it I have to right-click and click "open". The "New" opention from the drop down menu is also bolded. How do I change the default to "open" not "open new" ? -- Danish.inc First try the simple fix: Click the Windows Start menu and choose Run, type in winword /r (note the space before the slash), and click OK. That will rewrite Word's registry entries and may fix the pr...