Conditional Summation

[megabrown]

One of the purpose of the database I am building is to organize the organisms found in each sample. Every time a new sample comes in it gets analyzed for several components: chemistry, organism species, etc. There are 56 organism species that are looked for in each sample. At the moment there are 294 samples and I am in the process of trying to construct a query to perform the following calculation for each sample:


S = the number of species in a sample
ni = the number of individuals in species i of a sample
n = total number of individuals in a sample (I already have a query calculating the total individuals per sample, 'Total_Sample')



Not all species are found in every sample, thus having a value of 0 on the original table.

My original table is a larger version of something like this:

Sample Beetle Fly Moth Spiders 
1 4.20 2.30 0.01 1.25 
 
2 2.30 2.00 0.00 3.00 
3 3.10 1.60 1.03 2.73

How should I approach this?

[jzwp11]

Just based on the example table you posted, I don't think that your tables are constructed correctly.

First, I think you need a table that holds all of the species

tblSpecies
-pkSpeciesID primary key, autonumber field
-txtSpeciesName (a text field holding the name of the species)

Then you need a table to hold the info about the sample

tblSamples
-pkSampleID primary key, autonumber field
-SampleNo (a field to hold the sample ID, I'll leave this to you to determine whether it should be a numeric or text field based on your application)
-dteSample (date the sample was collected)

Now, since a sample can contain many species and a species may occur in many samples that describes a many-to-many relationship which requires another table to bring samples and species together

tblSampleSpecies
-pkSampleSpeciesID primary key, autonumber
-fkSampleID foreign key field to tblSamples
-fkSpeciesID foreign key field to tblSpecies
-longIndividualCount (count of individuals of the species in the sample)

You will need to create a few queries to get the counts for the various terms of your equation.

[megabrown]

Thanks for the reply!
Is there any way I can do this without changing the format (my employer requested I not change the format because there will be a few hundred users entering in data many of which will have little to no computer experience, let alone access)?

[jzwp11]

Setting up the table structure as I indicated follows the rules of normalization which is critical to the success of any relational database application. For more about normalization, please check out this site.

As the designer of the database, your job is to make data entry forms that allow the user to easily enter data into the correct table structure. With the correct form setup & instructions, the user need not know a whole lot about the underlying software i.e. Access. Your users should only interact with the database through the forms and menus you create. They should never see or have access to the underlying tables.

[megabrown]

Thanks! I am new to designing databases and you have saved me lots of trouble

[jzwp11]

You're welcome. Please post back if you have any questions.

[megabrown]

This might be in the wrong section but I made the changes to the table. I created a form based on the new table, containing the sample data but I would like the subform to consist of textboxes corresponding to each species. Thus users can enter in the sample data and individual counts of each species on a single form. How would I get each textbox in the subform to correspond to a particular species and have the names displayed rather than the ID numbers?

[jzwp11]

One option is to change the subform to datasheet view and use a combo box based on the species table. You can set up the combo box to hide the key field and display the species name. If you use the combo box wizard, it will step you through the process. Your users could then select the applicable species from the list. If you allow your users to type in the species names, I guarantee that you will have spelling errors which will cause problems when you try to do counts later on (if you try to match records by species names alone). If you have a set list of species for which your users must always have to put in a value (even if it is zero), you can use an append query to append the applicable species when the sample record is created, however, if you do not need to enter zero values then you are entering records needlessly.

[megabrown]

I restructured the other tables in my databases. I read the articles on normalization and I think I get it but just in case before I go any further I was wondering if you could take a quick look at my tables and let me know what you think?

The tables in my database are as follows:

tblCatchment (Each sample is from one of seven catchment sites):
-Catchment_ID, autonumber field, pk
-CatchmentName, text

tblHabitat (all samples are from one of two habitats):
-Habitat_ID, autonumber field, pk
-Habitat, text

tblLake (source of the sample):
-Lake_ID, autonumber field, pk
-Lake, text

tblPark (all current samples are from lakes in one of two parks but there will be more parks in the future):
-Park_ID, autonumber field, pk
-Park, text

tblYear:
-Year_ID, autonumber field, pk
-Year, number field

tblPhysicalDevelopment (each lake gets analyzed once for developmental data):
-Lake_ID, pk, fk from tblLake
-Pct_Development, number field
-No_cottage, number field
-Km_roads, number field
-Park_ID, fk from tblPark
-Area, number field
-Perimeter, number field
-Catchment_ID, fk from tblCatchment

tblWaterChemistry (water chemistry analyzed once a year per lake):
-Chem_ID, pk, autonumber field
-Lake_ID, fk from tblLake
-Year_ID, fk from tblYear
-PH, number field
-Cl, number field
-Calcium, number field
-Conductivity, number field

tblProcessing (each sample is processed in one of two ways):
-Processing_ID, autonumberfield, pk
-Processing, text

tblSample is:
-Sample_ID, autonumber field, pk
-Lake_ID, fk from tblLake
-Habitat_ID, fk from tblHabitat
-Site_No, number field
-Year_ID, fk from tblYear
-Processing_ID, fk from tblProcessing

tblSpecies:
-Species_ID, autonumber field, pk
-Species, text

tblBugData:
-Bug_ID, primary key, autonumber
-Sample_ID, fk from tblSample
-Species_ID, fk from tblSpecies
-IndividualCount, number field (double)

With regards to the last post, I will likely be appending a query for entry of individual counts.

Thank you for your patience.

[jzwp11]

You have made some good progress on the table structure, but it still needs some more refinements.

First, I don't think the year table is necessary, if you have date fields you can always extract the year portion using the year function year(datefield).

Since there are many lakes in each park, you have to relate the lakes to the parks

tblParkLakes
-pkParkLakeID primary key, autonumber
-Park_ID foreign key to tblParks
-Lake_ID foreign key to tblLakes

Since a park can have many physical data/developmental items, it would be best to have a table of the items and then tie those to the park rather than having separate fields for each item


tblDevelopmentItems (percent developed, # of cottages etc.)
-pkDevItemID primary key, autonumber
-txtItemName

You say that the development items are only evaluated once...ever? I assume that increased development will impact water quality?

If the development info is only capture once then the following structure would apply:

Now relate each item to the park

tblParkDevelopment
-pkParkDevID primary key, autonumber
-Park_ID foreign key to tblParks
-fkDevItemID foreign key to tblDevelopmentItems
-ItemValue (number field)

If you will evaluate the development on multiple occasions, then you need an intermediate table to capture the details of the event

tblParkDevelopmentEvaluation
-pkParkDevEvalID primary key autonumber
-Park_ID foreign key to tblParks
-dteEval date of the evaluation

Then you would relate the items to the evaluation table

tblParkDevelpomentEvaluationDetails
-pkParkDevEvalDetailID primary key, autonumber
-fkParkDevEvalID foreign key to tblParkDevelopmentEvaluation
-fkDevItemID foreign key to tblDevelopmentItems
-ItemValue (number field)


I'm not sure how the catchments fit into the picture. Are they part of the park or part of the lake within a park? Does a habitat relate to a catchment? You say that each sample is from either of two habitats and each sample is from one of 7 catchments, so it implies that a catchment is a subdivision of a habitat, correct?

For the water chemistry, it sounds like it is done on the lake as a whole an not on a habitat or one or more of the catchments, correct? Also, the water chemistry tests pH, Cl, Ca, Conductivity are just that, tests. They should not be fields but rather records in a table. That way if you add new tests, you just add a new record and do not have to add a new field. Adding a new record does not impact your forms and reports whereas a field will since it changes the table structure.

tblChemistryTests
-pkChemTestID primary key, autonumber
-txtChemTestName

Now since the chemistry tests are conducted many times for each lake we have a one-to-many relationship between the lake and the chemistry taking event.

tblLakeChemistryEvent
-pkLakeChemEventID primary key, autonumber
-Lake_ID foreign key to tblLakes
-dteChemEvent (date of the event)

Now tie the tests to the event

tblLakeChemistyEventTests
-pkLakeChemEventTestID primary key, autonumber
-fkLakeChemEventID foreign key to tblLakeChemistryEvent
-fkChemTestID foreign key to tblChemistryTests
-datavalue
-unitsofmeasurefield? (you may want to create a table of units of measure and then reference a foreign key here).


I'm not quite sure if the sample, species and bug data table are structured correctly until you tell us how the habitats and catchments fit in.

[megabrown]

Appreciate the prompt reply. I'll update my ER diagram.

Sorry, I should have been more clear.

Catchment
The catchments refer to water drainage basins. Each lake empties into one of seven water drainage basins (ie. catchments).

Habitat
The samples were taken from either mud or cobble (the habitats of the bugs) at each lake and counts of each of the bug species were taken. Multiple samples from the same lake may be taken from the same habitat.

Development Evaluation
The significance of the development of the lake is that it might be a factor in the bug counts which will be in tblBugData. I had the development (ie.Percent development, number of cottages) and physical characteristics (Area, perimeter, shoreline) is per lake (eg. No_Cottages = cottages on the lake) in a single table but it looks like I should separate them. I double-checked and the development may be re-evaluated in the future so I'll have to go with the intermediate table modus.

Water Chemistry
Yes, it is done on the lake as a whole.

[jzwp11]

I'm still a little unclear as to how catchments and habitats are related. If I assume that you define a sample as to the catchment from which it was taken and then the habitat within the catchment, then I would structure it as follows:

A lake has many catchments:

tblLakeCatchments
-pkLakeCatchID primary key, autonumber
-Lake_ID foreign key to tblLakes
-Catchment_ID foreign key to tblCatchment

Each lake's catchments can have either of two habitats

tblLakeCatchmentHabitats
-pkLakeCatchHabID primary key, autonumber
-fkLakeCatchID foreign key to tblLakeCatchments
-Habitat_ID foreign key to tblHabitats

Now for the samples...

tblSample
-Sample_ID, autonumber field, pk
-fkLakeCatchHabID foreign key to tblLakeCatchmentHabitat
-Site_No, number field
-dteSample (sample date)

The you said that a sample can be processed in either of two ways

tblSampleProcess
-pkSampleProcessID primary key autonumber
-Sample_ID foreign key to tblSample
-Processing_ID foreign key to tblProcessing

tblSpecies
-Species_ID, autonumber field, pk
-Species, text

I assume that the processing will have some impact on your species count

tblBugData
-Bug_ID, primary key, autonumber
-fkSampleProcessID foreign key to tblSampleProcess
-Species_ID, foreign key to tblSpecies
-IndividualCount, number field (double)

I am taking a guess at this since I don't know the details of youe application, so feel free to change it as necessary.

[megabrown]

Thank you! I appreciate the time you have taken to assist me.

I should have given you the premise earlier:
The main goal of the database is to assess and analyze invertebrate (bug) biodiversity in freshwater lakes located within a particular region. The region has two large park zones within it thus each lake we have currently sampled is within one of the zones. In the future, lakes lying outside the park zones (thus do not belong to any park) may also be sampled. In turn there are a total of seven watersheds/catchments in the region of interest thus each lake sampled will empty into one of the catchments. In other words, a lake can empty into only one catchment but several lakes can empty into a single catchment. Eventually we will be comparing the overall biodiversity of the catchments to one another (so, the bug data corresponding to all the lakes emptying into a particular catchment will be totalled and compared to the bug data for another catchment).

Factors that may influence the bug biodiversity (Ie. Water chemistry, development, physical characteristics) were measured and recorded on a lake-by-lake basis. Physical characteristics of the lake are measured only once (Area, Perimeter). Development around the lakes (Roads, cottages, percent development) were originally intended to be assessed once but the possibility of future reassesments is highly anticipated. Water chemistry is conducted every year for each lake.

To get representative individual bug counts for each lake, we sampled the two substrata (ie. Habitats) in which most freshwater bugs reside in. In other words we took at least one sample from the mud and at least one sample from cobble for each lake. In some cases samples were taken at multiple locations on each lake. Each sample was then processed in one of two ways and counts of the 115 species we are interested in were taken.

All the samples we have so far were collected in either 2007 or 2008 but samples will be taken in the future.

[jzwp11]

Based on your explanation, a few things will change.

tblCatchment
-Catchment_ID, autonumber field, pk
-CatchmentName, text

tblLake (source of the sample):
-Lake_ID, autonumber field, pk
-Lake, text
-Catchment_ID foreign key to tblCatchment

Since a lake can have many dimensional characteristics (perimeter and area), it technically describes a one-to-many relationship. Setting it up as follows gives you the most long-term flexibility if you add other characteristics at some other point in time

tblLakeCharacteristics
-pkLakeCharID primary key, autonumber
-Lake_ID foreign key to tblLake
-fkCharacteristicID foreign key to tblCharacteristics
-CharacteristicValue



tblCharacteristics (area, perimeter etc.)
-pkCharacteristicID primary key, autonumber
-txtCharacteristic

tblParks
-pkParkID primary key, autonumber
-txtParkName

In terms of the parks, if you still want to include that you can. If a park can contain more than one lake and if a lake can reside in more than 1 park (lake spans a park boundary) then you can use this structure.

tblParkLakes
-pkParkLakeID primary key, autonumber
-fkParkID foreign key to tblParks
-Lake_ID foreign key to tblLake

For lakes that do not reside in a park, you would not have a corresponding record in tblParkLakes

tblChemistryTests
-pkChemTestID primary key, autonumber
-txtChemTestName

tblLakeChemistryEvent
-pkLakeChemEventID primary key, autonumber
-Lake_ID foreign key to tblLakes
-dteChemEvent (date of the event)

Now tie the tests to the event

tblLakeChemistyEventTests
-pkLakeChemEventTestID primary key, autonumber
-fkLakeChemEventID foreign key to tblLakeChemistryEvent
-fkChemTestID foreign key to tblChemistryTests
-datavalue
-unitsofmeasurefield? (you may want to create a table of units of measure and then reference a foreign key here).

tblDevelopmentItems (percent developed, # of cottages etc.)
-pkDevItemID primary key, autonumber
-txtItemName

Previously we related the development items to the park; based on your discussion, they should be related to the lake

tblLakeDevelopmentEvaluation
-pkLakeDevEvalID primary key, autonumber
-Lake_ID foreign key to tblLakes
-dteEvaluation date of the development evaluation

tblLakeDevelopmentEvaluationDetail
-pkLakeDevEvalItemID primary key, autonumber
-fkLakeDevEvalID foreign key to tblLakeDevelopmentEvaluation
-fkDevItemID foreign key to tblDevelopmentItems
-ItemValue

In terms of the samples, the simplest approach would be something like this (I'm not sure if this is sufficient for your needs):

tblLakeSamples
-pkLakeSampleID primary key, autonumber
-Lake_ID foreign key to tblLake
-dteSample (sample date)
-fkHabitatID foreign key to tblHabitat

tblLakeSampleDetail
-pkLakeSampleDetailID primary key, autonumber
-fkLakeSampleID foreign key to tblLakeSamples
-fkSpeciesID foreign key to species table
-SpeciesCountValue

[megabrown]

Awesome! It works great but I was trying to wrap my head around this, how could I restructure the tables so that users can only enter in data for each chemistry event once per date for each lake? As it stands right now, people can enter in several results for the same chemistry test (or event) for each sample. For example, someone can enter in the PH for a particular sample twice.

This is how its structured right now:

tblChemistryTests
-pkChemTestID primary key, autonumber
-txtChemTestName

tblLakeChemistryEvent
-pkLakeChemEventID primary key, autonumber
-Lake_ID foreign key to tblLakes
-dteChemEvent (date of the event)

tblLakeChemistyEventTests
-pkLakeChemEventTestID primary key, autonumber
-fkLakeChemEventID foreign key to tblLakeChemistryEvent
-fkChemTestID foreign key to tblChemistryTests
-datavalue
-fkUnitsofmeasure_ID