aggregate functions including sampls without data

[KS]

Hi there,

I am currently designing an ecological database. I have a table with all the
Samples which have been taken with their details (location, date, type etc),
and then another Abundance table which has the Sample ID, Species Code and
Count. There are lots of species which may or may not occur in each sample.
Only positive counts are stored in the Abundance table.

This design is fine if I want the average number of animals or total number
of species per sample etc. BUT, where Im having problems is when I wish to
investigate the average abundance of a particular species over all samples.
Because the zero records arent included in the Abundance Table, I cant use
the aggregate function built into Access as it only averages over the samples
where that species was present. I want the average, and variance to
incorporate zero samples too. Can anyone please suggest how this might be
more easily done? or how I might change my database design so this can be
done?

thanks,

KS

 

[Klatuu]

Try using the DCount function to count the occurances you want to include.
Then you can use the DSum funtion get the value.