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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

db design to calculate overlap in group membership in time



 
 
Thread Tools Display Modes
  #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

  #2  
Old June 3rd, 2010, 05:28 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

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@uwe wrote in message news:a8fac2069ea04@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



  #3  
Old June 3rd, 2010, 07:32 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default db design to calculate overlap in group membership in time

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


"EHobs" u60556@uwe wrote in message news:a8fac2069ea04@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



  #4  
Old June 4th, 2010, 05:30 PM posted to microsoft.public.access.tablesdbdesign
EHobs via AccessMonster.com
external usenet poster
 
Posts: 3
Default db design to calculate overlap in group membership in time

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

  #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



  #6  
Old June 4th, 2010, 10:07 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default db design to calculate overlap in group membership in time

Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
other fields to uniquely identify a bird

TblLocation
LocationID
LocationDescription
other fields to iniquely identify a location

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve



"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



  #7  
Old June 4th, 2010, 11:02 PM posted to microsoft.public.access.tablesdbdesign
EHobs via AccessMonster.com
external usenet poster
 
Posts: 3
Default db design to calculate overlap in group membership in time

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

Steve wrote:
Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
other fields to uniquely identify a bird

TblLocation
LocationID
LocationDescription
other fields to iniquely identify a location

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve


Hi Steve,

[quoted text clipped - 66 lines]

Liz


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201006/1

  #8  
Old June 5th, 2010, 12:05 AM 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

You posted your question in a newsgroup that supports the use of Microsoft
Access. MS Access is a relational database.

It all starts with the data.

It sounds like you want to use a spreadsheet instead (all the data in one
table). Have you looked into that option?

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:a90abc7ad3805@uwe...
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

Steve wrote:
Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
other fields to uniquely identify a bird

TblLocation
LocationID
LocationDescription
other fields to iniquely identify a location

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve


Hi Steve,

[quoted text clipped - 66 lines]

Liz


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201006/1



  #9  
Old June 5th, 2010, 12:33 AM posted to microsoft.public.access.tablesdbdesign
EHobs via AccessMonster.com
external usenet poster
 
Posts: 3
Default db design to calculate overlap in group membership in time

Hi Jeff,

Yes, I have looked into spread sheets, but I have settled on access because I
am more comfortable with writing queries in that program and am estimating
that I should have around 80,000 data points, which is more than Excel can
handle. Thank you for your comments and your time. I will try posting under
queries, as it is clear that my question really doesn't fit here.

Thanks again,

Liz


Jeff Boyce wrote:
You posted your question in a newsgroup that supports the use of Microsoft
Access. MS Access is a relational database.

It all starts with the data.

It sounds like you want to use a spreadsheet instead (all the data in one
table). Have you looked into that option?

Regards

Jeff Boyce
Microsoft Access MVP

Hi Steve & Jeff,

[quoted text clipped - 59 lines]

Liz


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201006/1

  #10  
Old June 5th, 2010, 12:41 AM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default db design to calculate overlap in group membership in time

Hi Liz,

You ought to reconsider the tables I suggested. Multiple tables DOES NOT
make querying harder, In fact, multiple tab;es make querying easier. Data
entry will be easier and more accurate too!

Steve



"EHobs via AccessMonster.com" u60556@uwe wrote in message
news:a90abc7ad3805@uwe...
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

Steve wrote:
Hi Liz,

Give me your comments please to the following tables ....

TblBirdType
BirdTypeID
BirdType (Robin, Sparrow, Dove, etc)

TblColor
ColorID
Color (Black, Red, B;ue, etc)

TblBird
BirdID
BirdTypeID
ColorID
other fields to uniquely identify a bird

TblLocation
LocationID
LocationDescription
other fields to iniquely identify a location

TblGroup
GroupID
LocationID
DateGroupObserved

TblGroupComposition
GroupCompositionID
GroupID
BirdID
TimeBirdObserved
ArrivalDeparture

Steve


Hi Steve,

[quoted text clipped - 66 lines]

Liz


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201006/1



 




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 05:53 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.