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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#10
|
|||
|
|||
you have to enter credit card info to create a membership.
id rather not have it on their servers. I wonder why it is happening to me all the time and why now. |
Thread Tools | |
Display Modes | |
|
|