A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating total days minus overlapp



 
 
Thread Tools Display Modes
  #1  
Old February 16th, 2007, 05:44 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 2
Default Calculating total days minus overlapp

Hello,
I have to create a query which will return the total # days a
file has been checked out. The table is set up for two "checkouts"
that can occur in succession, concurrently, or be on-going. For
example:

Checkoutdate1: 1 Jan 06
ReturnDate1: 30 Mar 06

Checkoutdate2: 15 Jan 06
ReturnDate2: 30 Mar 06

I cannot simply subtract the dates and add them together, becasue I
only need the non-overlapping time. In this case it would be 89 days.

Complicating the matter is when I leave a return date blank, or there
is no 2nd Checkout. For example:

Checkoutdate1: 1Jan 06
ReturnDate1: (Null)

Checkoutdate2: 5 Jan 06
Checkoutdate2: 30 Mar 06

Of Course, there are numerous other variables with this situation.


Any suggestions would be greatly appreciated! Thank you.

  #2  
Old February 16th, 2007, 07:00 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Calculating total days minus overlapp

I did not follow your information - how can a second person checkout a file
on 15 Jan 06 if the first person did not return the file until 30 Mar 06?

" wrote:

Hello,
I have to create a query which will return the total # days a
file has been checked out. The table is set up for two "checkouts"
that can occur in succession, concurrently, or be on-going. For
example:

Checkoutdate1: 1 Jan 06
ReturnDate1: 30 Mar 06

Checkoutdate2: 15 Jan 06
ReturnDate2: 30 Mar 06

I cannot simply subtract the dates and add them together, becasue I
only need the non-overlapping time. In this case it would be 89 days.

Complicating the matter is when I leave a return date blank, or there
is no 2nd Checkout. For example:

Checkoutdate1: 1Jan 06
ReturnDate1: (Null)

Checkoutdate2: 5 Jan 06
Checkoutdate2: 30 Mar 06

Of Course, there are numerous other variables with this situation.


Any suggestions would be greatly appreciated! Thank you.


  #3  
Old February 16th, 2007, 07:21 PM posted to microsoft.public.access.queries
[email protected]
external usenet poster
 
Posts: 2
Default Calculating total days minus overlapp

Thanks for the reply. The file checked out is always a copy, so you
can have two checked out at the same time. To better explain, think
of each file as a "Problem". To solve the problem, we ask other
departments for their inputs. While they are working on the
"problem", we are not. My boss wants to calculate how long it takes
us to solve the "problem". Thus, we must subtract the total time in
days that the file or "problem" has been out of our hands. Simply
adding up the the two total checkout times does not solve the issue
due to overlapping days. Hopefully this explains it better. Thanks
again!

  #4  
Old February 16th, 2007, 08:16 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Calculating total days minus overlapp

Try this, replacing Jason as table name with your table name. and CaseID with
your field name.

SELECT Jason.CaseID, Min(Jason.Checkoutdate) AS Out, Max(IIf([ReturnDate] Is
Null,Date(),[ReturnDate])) AS Return,
DateDiff("d",Min([Checkoutdate]),Max(IIf([ReturnDate] Is
Null,Date(),[ReturnDate]))) AS Days
FROM Jason
GROUP BY Jason.CaseID;


" wrote:

Thanks for the reply. The file checked out is always a copy, so you
can have two checked out at the same time. To better explain, think
of each file as a "Problem". To solve the problem, we ask other
departments for their inputs. While they are working on the
"problem", we are not. My boss wants to calculate how long it takes
us to solve the "problem". Thus, we must subtract the total time in
days that the file or "problem" has been out of our hands. Simply
adding up the the two total checkout times does not solve the issue
due to overlapping days. Hopefully this explains it better. Thanks
again!


  #5  
Old February 16th, 2007, 09:39 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Calculating total days minus overlapp

If I understand correctly your table has four fields CheckOutDate1 (A),
CheckoutDate2 (C), ReturnDate1 (B), and ReturnDate2 (D)

You have mutilple scenarios - dates are filled as follows
A, B, C, and D are all blank
A is filled, B, C, D are blank
A and B are filled, C and D are blank
A, B, and C are filled, D is blank.
A, B, C, and D are filled

A to B can overlap C to D
A to B does not overlap C to D

Nicely complicated and I probably would write a VBA function to handle it.
Air code for that might look something like

Function GetDayCount (A, B, C, D) as integer
Dim dtA as Date, dtB as Date, dtC as Date, dtD as Date

If IsDate(A) = False and IsDate(B) = False and IsDate(C) = False and
IsDate(D) = False then
GetDayCount = 0
Else
dtA = NZ(A,Date())
dtB =NZ(B,Date())
dtC=Nz(C,Date())
dtD=Nz(D,Date())

'Fix C and D if they overlap A to B
If dtB dtC then dtC = dtB
If dtB dtD then dtD = dtB

GetDayCount = DateDiff("d",dtA,dtB) + DateDiff("d",dtC,dtD)
End if

If that gives you correct results you could do that with a complicated
expression in a query, the start of which should look something like

DateDiff("d",[A], Nz([b],Date())) +
DateDiff("d",IIF(Nz([b],Date())Nz([C],Date()),
Nz([b],Date()),[C]),IIF(Nz([b],Date()) ...))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

wrote in message
oups.com...
Hello,
I have to create a query which will return the total # days a
file has been checked out. The table is set up for two "checkouts"
that can occur in succession, concurrently, or be on-going. For
example:

Checkoutdate1: 1 Jan 06
ReturnDate1: 30 Mar 06

Checkoutdate2: 15 Jan 06
ReturnDate2: 30 Mar 06

I cannot simply subtract the dates and add them together, becasue I
only need the non-overlapping time. In this case it would be 89 days.

Complicating the matter is when I leave a return date blank, or there
is no 2nd Checkout. For example:

Checkoutdate1: 1Jan 06
ReturnDate1: (Null)

Checkoutdate2: 5 Jan 06
Checkoutdate2: 30 Mar 06

Of Course, there are numerous other variables with this situation.


Any suggestions would be greatly appreciated! Thank you.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.