Prev: My database loads but disabled?
Next: Making 2 colums into one
From: KenSheridan via AccessMonster.com on 7 Jun 2010 17:37
Liz:

First you need to establish a way of determining the shared time where two
birds are concurrently present. I suspect this could be done with a
mathematical expression, but I can't put my finger on just what this would be,
but it's pretty easy to write a function to do it and return the shared time
in seconds:

Public Function TimeShared(ArrivalA As Date, _
DepartureA As Date, _
ArrivalB As Date, _
DepartureB As Date) As Long

Const SECONDSINDAY = 86400

Dim dtmOuterRangeStart As Date
Dim dtmOuterRangeEnd As Date
Dim dtmInnerRangeStart As Date
Dim dtmInnerRangeEnd As Date
Dim dblTimeShared As Double

' determine limits of inner and outer ranges
If ArrivalA < ArrivalB Then
dtmOuterRangeStart = ArrivalA
dtmInnerRangeStart = ArrivalB
Else
dtmOuterRangeStart = ArrivalB
dtmInnerRangeStart = ArrivalA
End If

If DepartureA > DepartureB Then
dtmOuterRangeEnd = DepartureA
dtmInnerRangeEnd = DepartureB
Else
dtmOuterRangeEnd = DepartureB
dtmInnerRangeEnd = DepartureA
End If

' shared time = length of outer range less times from
' end of inner range and end of outer range and
' start of outer range and start of inner range
dblTimeShared = (dtmOuterRangeEnd - dtmOuterRangeStart) - _
(dtmOuterRangeEnd - dtmInnerRangeEnd) - _
(dtmInnerRangeStart - dtmOuterRangeStart)

' covert value to seconds
TimeShared = dblTimeShared * SECONDSINDAY

End Function

Add the function to a standard module, then you should be able to call it in
query. Off the top of my head and untested try this:

SELECT A1.BirdID As Bird1, A2.BirdID AS Bird2,
SUM(TIMESHARED(A1.ArrivalTime, A1.DepartureTime,
A2.ArrivalTime, A2.DepartureTime)) AS TotalSharedTime
FROM Arrivals As A1, Arrivals AS A2
WHERE A1.ArrivalTime <= A2.DepartureTime
AND A1.DepartureTime >= A2.ArrivalTime
GROUP BY A1.BirdID, A2.BirdID;

and for the second:

PARAMETERS [Enter time:] DATETIME;
SELECT *
FROM Arrivals
WHERE [Enter time:] BETWEEN
ArrivalTime AND DepartureTime;

The first should return the total length of time in seconds for all pairs of
birds who are concurrently present. Note that it will return each pair twice
with the birds transposed in each case. The second should return all birds
who are present at the time entered at the parameter prompt. You could if
you wish include parameters in the first query on A1.BirdID and A2.BirdID to
restrict the result to one particular pair of birds. If your table includes
data over more than one day you must of course include both the date and time
in the ArrivalTime and DepartureTime values.

As regards the design of the database as a whole your table should reference
another table of Birds, with one row per bird and a primary key column of
BirdID. A relationship between the tables should be created and referential
integrity enforced to ensure that only valid BirdID values can be entered in
the Arrivals table. Should you need to extend the database to cover more
than one location then you'd need to introduce a Locations table and include
a LocationID foreign key in the Arrivals table. This could then be included
in the queries to differentiate between locations.

Ken Sheridan
Stafford, England

EHobs wrote:
>Hi Steve & Jeff,
>
>Thanks for your comments. I was actually planning something much simpler than
>the several tables Steve posted - I am working with only one type of bird at
>one specific location. I was thinking of having just a single table that I
>can query. I could put the data in the following format
>
>Date BirdID ArrivalTime DepartureTime
>
>Any ideas on how to structure a query that will allow me to:
>
>1) determine the total shared amount of time present for any 2 individuals
>2) determine all of the individuals present at any one time point
>
>Thanks again,
>
>Liz
>
>>Hi Liz,
>>
>[quoted text clipped - 39 lines]
>>>>>
>>>>> Liz

--
Message posted via http://www.accessmonster.com

First  |  Prev  | 
Pages: 1 2 3
Prev: My database loads but disabled?
Next: Making 2 colums into one