View Single Post
  #5  
Old June 4th, 2010, 05:54 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default db design to calculate overlap in group membership in time

The example you provided may not have quite all the information it sounds
like you need.

Steve asked how you "group" the members/birds. In your example, are all of
those birds part of the same group? If so, how do you know/tell Access that
fact?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"EHobs via AccessMonster.com" u60556@uwe wrote in message
news:a907d7f13e81e@uwe...
Hi Steve,

Thanks for your response. I can identify individual birds by a unique
color
combination. The field is a 3 letter code corresponding to the 3 colors
used
to id the bird (ex: bbb is the bird with the blue-blue-blue color combo).
I
have observations of individuals at one specific location, so any birds
observed at that location at the same time are defined as associated. I
need
to be able to query the db for both group composition at any one time as
well
as overall shared association time among any 2 individuals. I have not yet
entered the data -- I am trying to figure out the best format first to
avoid
headaches later. I am currently envisioning a table like this (below), but
am
very open to suggestions!

Date Time ID Type (a=arrival, d=departure)
12-Jun 9:01 bbb a
12-Jun 9:02 bbb d
12-Jun 9:02 rrr a
12-Jun 9:02 brr a
12-Jun 9:03 rrr d

I would like to run a query that will calculate the amount of time that
every
bird shared with every other bird (ex from above: bbb shared 0 with rrr,
brr;
rrr shared 1 with brr; etc). I can have the time of arrival coded in time
of
day or in number of seconds from the start of observation, which may make
calculations simpler.

In response to Jeff Boyce's response, I have experience constructing
relational databases and queries. I am self-taught, so they usually aren't
pretty, but I can usually get things to work. This question with this db
that
I have is beyond what I have tried to do before. Again, any suggestions
would
be very appreciated.

Thanks,

Liz

Steve wrote:
Hi Liz,

I think that the first thing is to answer two questions ...
1. "....one bird is in the same group as another bird" implies that you
can identify individual birds. Can you do that and what are the
characteristics (fields) that can be used to uniquely identify a bird?

2. What factors (fields) distinguishes one group from another. For
example, Bird A and Bird B are together; is that a group? At another spot,
Bird C and Bird D are together; is that a group? After a few moments, Bird
C
and Bird D join Bird A and Bird B. There are now four birds together, is
that group AB, Group CD or a new group.

Steve


Hi all,

[quoted text clipped - 35 lines]

Liz


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