From: EHobs on
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

From: Jeff Boyce on
Liz

If you haven't built a database/application before, and particularly an
Access (relational) database/application, there are (at least) four separate
learning curves you'll want to consider. Maybe you have already worked your
way up some of them...

1. relational database design - if "normalization" isn't familiar, you
need to understand it first
2. Access tips/tricks - Access handles things differently than Excel or
....
3. graphical user interface design - you do NOT use the tables in Access
for data entry ... that's what the forms are for
4. application development - if you've never built a ... "house", where
do you start?!

My suggestion is that you put aside some of your "how" questions and focus
first on "what". What are the things about which you wish to store (and
retrieve) information? These are your "entities". Now, what pieces of
information do you want to store about each entity? For example, a person's
name and date of birth "belong" to an entity about persons, but do NOT
belong to an entity about jobs...

Here's a rough idea of how your tables (!entities!) might look if you were
building a student registration database (untested, simplistic, for example
purposes only):

tblPerson
PersonID
FName
LName
DOB

tblClass
ClassID
ClassTitle
ClassDescription

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate

What does your data look like?

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" <u60556(a)uwe> wrote in message news:a8fac2069ea04(a)uwe...
> 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
>


From: Steve on
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
santus(a)penn.com

"EHobs" <u60556(a)uwe> wrote in message news:a8fac2069ea04(a)uwe...
> 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
>


From: EHobs via AccessMonster.com on
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
>santus(a)penn.com
>
>> Hi all,
>>
>[quoted text clipped - 35 lines]
>>
>> Liz

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

From: Jeff Boyce on
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(a)uwe> wrote in message
news:a907d7f13e81e(a)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
>>santus(a)penn.com
>>
>>> Hi all,
>>>
>>[quoted text clipped - 35 lines]
>>>
>>> Liz
>
> --
> Message posted via http://www.accessmonster.com
>