A database of fish

[jeremiaheb]

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

 

[Jeff Boyce]

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]

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]

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

 

[aaron.kempf]

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.

 

[jeremiaheb via AccessMonster.com]

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

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]

 

[Jeff Boyce]

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

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

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

 

[Aaron Kempf knows nothing]

I agree.

best for you to be more normalized.