db design to calculate overlap in group membership in time
in [Tables]
|
From: Steve on 4 Jun 2010 17:07 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 santus(a)penn.com "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 >
From: EHobs via AccessMonster.com on 4 Jun 2010 18:02 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 >santus(a)penn.com > >> Hi Steve, >> >[quoted text clipped - 66 lines] >>>> >>>> Liz -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201006/1
From: Jeff Boyce on 4 Jun 2010 19:05 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(a)uwe> wrote in message news:a90abc7ad3805(a)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 >>santus(a)penn.com >> >>> Hi Steve, >>> >>[quoted text clipped - 66 lines] >>>>> >>>>> Liz > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201006/1 >
From: EHobs via AccessMonster.com on 4 Jun 2010 19:33 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/Forums.aspx/access-tablesdbdesign/201006/1
From: Steve on 4 Jun 2010 19:41 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 santus(a)penn.com "EHobs via AccessMonster.com" <u60556(a)uwe> wrote in message news:a90abc7ad3805(a)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 >>santus(a)penn.com >> >>> Hi Steve, >>> >>[quoted text clipped - 66 lines] >>>>> >>>>> Liz > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201006/1 >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: My database loads but disabled? Next: Making 2 colums into one |