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
|
|||
|
|||
A database of fish
To start...I am new to access, very new.
I opened my mouth and said I thought that access would be the best application to house and retreive all of our marine monitoring data (fish, coral and oceanograghic info) and now I am charged with that task. I have already built a db for the fish monitoring part and my access research thus far has led me to believe that it is not very good. So I turn the the forums. A little about the fish work: Once to twice a year we conduct a fish census at certain reefs. The census consists of counting all the fish in binned size classes (0-5cm, 6-10cm...) within a certain area (transect). At each site we conduct 10 transects. I currently have three tables: tblFish_names: family genus scientific name common name primary trophic level secondary trophic level param_a param_b tblSite_description: site strata latitude longitude tblMaster: site month year transect# scientific name 0-5cm 6-10cm 11-15cm and so on tblMaster holds the meat of the information and gets very redundant when a single transect get 50 different species on it. Could somebody please provide me with some guidance and direction for this database? I hope to use what I learn from this fish section and apply it to the other marine monitoring sections. Thanks for the help in advance. Cheers Jeremiah |
#2
|
|||
|
|||
A database of fish
I might be missing something, but that tblMaster looks a lot like ... a
spreadsheet! When you find you have 'repeating categories' (your size 'buckets'), you probably can simplify your table design. And since you already have "scientific name" in your Fish table, why are you re-entering it in your tblMaster? I don't have a good enough grasp of your situation to understand what records you are entering into your tblMaster. More info, please... Regards Jeff Boyce Microsoft Office/Access MVP "jeremiaheb" u45164@uwe wrote in message news:87fb3619b2fb3@uwe... To start...I am new to access, very new. I opened my mouth and said I thought that access would be the best application to house and retreive all of our marine monitoring data (fish, coral and oceanograghic info) and now I am charged with that task. I have already built a db for the fish monitoring part and my access research thus far has led me to believe that it is not very good. So I turn the the forums. A little about the fish work: Once to twice a year we conduct a fish census at certain reefs. The census consists of counting all the fish in binned size classes (0-5cm, 6-10cm...) within a certain area (transect). At each site we conduct 10 transects. I currently have three tables: tblFish_names: family genus scientific name common name primary trophic level secondary trophic level param_a param_b tblSite_description: site strata latitude longitude tblMaster: site month year transect# scientific name 0-5cm 6-10cm 11-15cm and so on tblMaster holds the meat of the information and gets very redundant when a single transect get 50 different species on it. Could somebody please provide me with some guidance and direction for this database? I hope to use what I learn from this fish section and apply it to the other marine monitoring sections. Thanks for the help in advance. Cheers Jeremiah |
#3
|
|||
|
|||
A database of fish
Jeff
Thanks for the quick reply. It looks like a spread sheet because I imported the data from a spreadsheet and then added the fish_name and site_description tables in access...from your response it seems my problems may have began at that point. The records I want to to enter is the number of fish in each size bin. For example, for every site -month - year - transect there will be many fish species, with the number of that species we counted in each of the size bins. Our field data sheet looks something like this: Site date transect# species 0-5 6-10 11-15 16-20 fisha 2 3 1 0 fishb 1 1 2 1 and so on since we conduct 10 transects at each site, the redundancy begins, and like you said, it resembles a spreadsheet. Should I get the spreedsheet layout out of mind mind? And it sounds like more tables are needed to simplify. Any suggestions or more info needed? Cheers Jeff Boyce wrote: I might be missing something, but that tblMaster looks a lot like ... a spreadsheet! When you find you have 'repeating categories' (your size 'buckets'), you probably can simplify your table design. And since you already have "scientific name" in your Fish table, why are you re-entering it in your tblMaster? I don't have a good enough grasp of your situation to understand what records you are entering into your tblMaster. More info, please... Regards |
#4
|
|||
|
|||
A database of fish
Wow! With a leading question like that, how can I resist? I just love a
"straight man"!G Access is a relational database, not a "spreadsheet on steroids". If you "imported the data from a spreadsheet", you will find that both you and Access have to work extra hard to do the simple things Access can handle if you feed it relational, well-normalized data. Most spreadsheets, by their limitations/nature, are "flat". Relational databases expect and work best with data that is organized "narrow and deep". If the terms "normalized" and "relational" aren't familiar, plan to spend some time brushing up on them before revisiting your table structure. Actually, there's no reason you can't continue to import data directly from Excel ... just don't expect that "raw" data to be your "permanent" data. You can use queries to parse the raw data into its permanent arrangement. I'm sure I'm still missing some particulars, but it sounds like you have: Species SizeCategories Transects and Sites with information about all of these. I can imagine a table for each of these, plus one more table that holds something like: trelCount CountID SiteID (a "foreign key" pointing to a site in the tblSite) TransectID (a "foreign key" pointing to a transect in the tblTransect ... but I may be confused on this one) SpeciesID (a "foreign key" pointing to a species in the tblSpecies) SizeCategoryID (a "foreign key" pointing to a size category in the tlkpSizeCategory) NumberSighted DateSighted SightedBy (?a "foreign key" pointing to a personID in a tblPerson?) Good Luck! Regards Jeff Boyce Microsoft Office/Access MVP "jeremiaheb" u45164@uwe wrote in message news:87fbae88671c8@uwe... Jeff Thanks for the quick reply. It looks like a spread sheet because I imported the data from a spreadsheet and then added the fish_name and site_description tables in access...from your response it seems my problems may have began at that point. The records I want to to enter is the number of fish in each size bin. For example, for every site -month - year - transect there will be many fish species, with the number of that species we counted in each of the size bins. Our field data sheet looks something like this: Site date transect# species 0-5 6-10 11-15 16-20 fisha 2 3 1 0 fishb 1 1 2 1 and so on since we conduct 10 transects at each site, the redundancy begins, and like you said, it resembles a spreadsheet. Should I get the spreedsheet layout out of mind mind? And it sounds like more tables are needed to simplify. Any suggestions or more info needed? Cheers Jeff Boyce wrote: I might be missing something, but that tblMaster looks a lot like ... a spreadsheet! When you find you have 'repeating categories' (your size 'buckets'), you probably can simplify your table design. And since you already have "scientific name" in your Fish table, why are you re-entering it in your tblMaster? I don't have a good enough grasp of your situation to understand what records you are entering into your tblMaster. More info, please... Regards |
#5
|
|||
|
|||
A database of fish
I disagree.
Access -IS- a spreadsheet on steroids. People can use whatever design they need to. I've definitely had enterprise level databases that look more like a spreadsheet than anything-- but for some situations; it is necessary But it might work best for you to be more normalized. On Jul 31, 3:06*pm, "Jeff Boyce" wrote: Wow! *With a leading question like that, how can I resist? *I just love a "straight man"!G Access is a relational database, not a "spreadsheet on steroids". *If you "imported the data from a spreadsheet", you will find that both you and Access have to work extra hard to do the simple things Access can handle if you feed it relational, well-normalized data. Most spreadsheets, by their limitations/nature, are "flat". *Relational databases expect and work best with data that is organized "narrow and deep". If the terms "normalized" and "relational" aren't familiar, plan to spend some time brushing up on them before revisiting your table structure. Actually, there's no reason you can't continue to import data directly from Excel ... just don't expect that "raw" data to be your "permanent" data. You can use queries to parse the raw data into its permanent arrangement. I'm sure I'm still missing some particulars, but it sounds like you have: * * Species * * SizeCategories * * Transects and * * Sites with information about all of these. I can imagine a table for each of these, plus one more table that holds something like: * * trelCount * * * * CountID * * * * SiteID (a "foreign key" pointing to a site in the tblSite) * * * * TransectID (a "foreign key" pointing to a transect in the tblTransect ... but I may be confused on this one) * * * * SpeciesID (a "foreign key" pointing to a species in the tblSpecies) * * * * SizeCategoryID (a "foreign key" pointing to a size category in the tlkpSizeCategory) * * * * NumberSighted * * * * DateSighted * * * * SightedBy (?a "foreign key" pointing to a personID in a tblPerson?) Good Luck! Regards Jeff Boyce Microsoft Office/Access MVP "jeremiaheb" u45164@uwe wrote in messagenews:87fbae88671c8@uwe... Jeff Thanks for the quick reply. It looks like a spread sheet because I imported the data from a spreadsheet and then added the fish_name and site_description tables in access...from your response it seems my problems may have began at that point. The records I want to to enter is the number of fish in each size bin. For example, for every site -month - year - transect there will be many fish species, with the number of that species we counted in each of the size bins. Our field data sheet looks something like this: Site date transect# species * * 0-5 * *6-10 * * 11-15 * * 16-20 fisha * * * * * 2 * * * *3 * * * * *1 * * * * * *0 fishb * * * * * 1 * * * * 1 * * * * *2 * * * * * 1 and so on since we conduct 10 transects at each site, the redundancy begins, and like you said, it resembles a spreadsheet. *Should I get the spreedsheet layout out of mind mind? *And it sounds like more tables are needed to simplify. Any suggestions or more info needed? Cheers Jeff Boyce wrote: I might be missing something, but that tblMaster looks a lot like ... a spreadsheet! *When you find you have 'repeating categories' (your size 'buckets'), you probably can simplify your table design. *And since you already have "scientific name" in your Fish table, why are you re-entering it in your tblMaster? I don't have a good enough grasp of your situation to understand what records you are entering into your tblMaster. More info, please... Regards |
#6
|
|||
|
|||
A database of fish
Ok, I now have some direction to follow. Thank you.
Just to clarify though (and this question is likely coming from a I-feel-more- comforable-with-a-spreadsheet point of view), will the tblSizeCategory and tblTransect both be two column descriptive tables? For instance: SizeCategoryID BinSize 1 0-5cm 2 6-10cm 3 11-15cm I understand it will fit the "narrow and deep" format, but I am a little nervous about the ease of entering data. Thanks again for the help Jeff Boyce wrote: Wow! With a leading question like that, how can I resist? I just love a "straight man"!G Access is a relational database, not a "spreadsheet on steroids". If you "imported the data from a spreadsheet", you will find that both you and Access have to work extra hard to do the simple things Access can handle if you feed it relational, well-normalized data. Most spreadsheets, by their limitations/nature, are "flat". Relational databases expect and work best with data that is organized "narrow and deep". If the terms "normalized" and "relational" aren't familiar, plan to spend some time brushing up on them before revisiting your table structure. Actually, there's no reason you can't continue to import data directly from Excel ... just don't expect that "raw" data to be your "permanent" data. You can use queries to parse the raw data into its permanent arrangement. I'm sure I'm still missing some particulars, but it sounds like you have: Species SizeCategories Transects and Sites with information about all of these. I can imagine a table for each of these, plus one more table that holds something like: trelCount CountID SiteID (a "foreign key" pointing to a site in the tblSite) TransectID (a "foreign key" pointing to a transect in the tblTransect ... but I may be confused on this one) SpeciesID (a "foreign key" pointing to a species in the tblSpecies) SizeCategoryID (a "foreign key" pointing to a size category in the tlkpSizeCategory) NumberSighted DateSighted SightedBy (?a "foreign key" pointing to a personID in a tblPerson?) Good Luck! Regards Jeff Boyce Microsoft Office/Access MVP Jeff [quoted text clipped - 42 lines] Regards -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200808/1 |
#7
|
|||
|
|||
A database of fish
Since I don't 'grok' "Transect", I can't help there.
The SizeCategory looks right on the mark. If you are nervous about entering data IN THE TABLES, rest assured. Tables store data, Forms display it. Use Access Forms to do your data entry. That said, you are well-advised to be a little nervous. I generally point out at least three learning curves involved in using MS Access to build an application: First, you need to understand "normalization" and "relational" if you're to get the best use of the tools. Second, you need to understand how Access does things (and it is not the same way Word or Excel does things ... different things!) Finally, you need to understand how people relate to applications -- call it graphical user interface design. (oh yes, one more, if you haven't built applications before ... i.e., no 'developement' experience ... you have to learn some time!) Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "jeremiaheb via AccessMonster.com" u45164@uwe wrote in message news:88060f17a4e40@uwe... Ok, I now have some direction to follow. Thank you. Just to clarify though (and this question is likely coming from a I-feel-more- comforable-with-a-spreadsheet point of view), will the tblSizeCategory and tblTransect both be two column descriptive tables? For instance: SizeCategoryID BinSize 1 0-5cm 2 6-10cm 3 11-15cm I understand it will fit the "narrow and deep" format, but I am a little nervous about the ease of entering data. Thanks again for the help Jeff Boyce wrote: Wow! With a leading question like that, how can I resist? I just love a "straight man"!G Access is a relational database, not a "spreadsheet on steroids". If you "imported the data from a spreadsheet", you will find that both you and Access have to work extra hard to do the simple things Access can handle if you feed it relational, well-normalized data. Most spreadsheets, by their limitations/nature, are "flat". Relational databases expect and work best with data that is organized "narrow and deep". If the terms "normalized" and "relational" aren't familiar, plan to spend some time brushing up on them before revisiting your table structure. Actually, there's no reason you can't continue to import data directly from Excel ... just don't expect that "raw" data to be your "permanent" data. You can use queries to parse the raw data into its permanent arrangement. I'm sure I'm still missing some particulars, but it sounds like you have: Species SizeCategories Transects and Sites with information about all of these. I can imagine a table for each of these, plus one more table that holds something like: trelCount CountID SiteID (a "foreign key" pointing to a site in the tblSite) TransectID (a "foreign key" pointing to a transect in the tblTransect ... but I may be confused on this one) SpeciesID (a "foreign key" pointing to a species in the tblSpecies) SizeCategoryID (a "foreign key" pointing to a size category in the tlkpSizeCategory) NumberSighted DateSighted SightedBy (?a "foreign key" pointing to a personID in a tblPerson?) Good Luck! Regards Jeff Boyce Microsoft Office/Access MVP Jeff [quoted text clipped - 42 lines] Regards -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200808/1 |
#8
|
|||
|
|||
A database of fish
" wrote: I agree. best for you to be more normalized. |
Thread Tools | |
Display Modes | |
|
|