View Single Post
  #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