Basic query design question

[megabrown]

I'm fairly new to access and I'm creating a database to assess and analyze invertebrate (bug) biodiversity in freshwater lakes located within a particular region.

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.

I currently have tables setup as follows:

tblSpecies
-pkSpecies_ID
-txtSpecies



tblLakeSamples
-pkLake_Sample_ID (autonumber, primary)
-fkLake_ID (foreign key to tblLake)
-fkProcessing_ID (foreign key to tblProcessing)
-fkHabitat_ID (foreign key to tblHabitat)
-dteSample (date field)

tblLakeSampleDetail
-pkLake_Sample_Detail_ID (autonumber, primary)
-fkLake_Sample_ID (foreign key to tblLakeSamples)
-fkSpecies_ID (foreign key to tblSpecies)
-Species_Count (number field, double data type)

I am currently trying to design a query to calculate the percentage of each inverterbrate species (eg. [# of spiders]/[total # invertebrates]) per sample (each sample is distinguished by a unique pkLake_Sample_ID value). My biggest challenge is designing the query on a 'per sample basis'.

How should I approach this?

[weekend00]

you may use Dsum() to get total of counts.

SELECT tblLakeSampleDetail.fkLake_Sample_ID, tblLakeSampleDetail.fkSpecies_ID, tblLakeSampleDetail.Species_Count / dSum("species_count","tblLakeSampleDetail","fkLake _Sample_ID=" & tblLakeSampleDetail.fkLake_Sample_ID)
FROM tblLakeSampleDetail;




or, if you care about performance, use join subquery:


SELECT a.fkLake_Sample_ID, a.fkSpecies_ID, a.Species_Count, a.Species_Count/b.ttotal
FROM tblLakeSampleDetail AS a INNER JOIN (select tblLakeSampleDetail.fkLake_Sample_ID, sum(tblLakeSampleDetail.Species_Count) as tTotal from tblLakeSampleDetail group by tblLakeSampleDetail.fkLake_Sample_ID) AS b ON a.fkLake_Sample_ID = b.fkLake_Sample_ID;