View Single Post
  #1  
Old June 3rd, 2010, 04:32 PM posted to microsoft.public.access.tablesdbdesign
EHobs
external usenet poster
 
Posts: 1
Default db design to calculate overlap in group membership in time

Hi all,

I am a biologist studying the social structure of birds. I am currently
brainstorming ideas for constructing a database that will allow me to easily
calculate the number of seconds that one bird is in the same group as another
bird. Because group membership changes frequently with birds arriving and
departing at different times, and often arriving, departing, and re-arriving
on the scale of seconds, I will have many thousands of records. I am trying
to determine how to best set up this database before entering all the data. I
have 2 goals with this database:

1) I need to find a way that I can streamline data entry. I was thinking 3
columns: ID, time of movement, and whether it is an arrival or departure.
Because of the frequent movements and the large number of potential
individuals, I need to be able to enter arrivals and departures separately.

2) I need to determine how I am going to calculate the amount of time that
any 2 birds at present at the same time. This needs to be able to account for
individuals that arrive and depart at different times, and calculate just
"shared" time.

I think this problem may be somewhat similar to company records of employees
hired and fired at different dates, where the amount of time of overlap in
employment is calculated.

Any help or advice would be greatly appreciated!

Thanks,

Liz