[ProgSoc] Statistics from a database

[jay.banyer@nospam.jcu.edu.au]

I have a database (MS Access) which contains scientific measurements. Samples 
were taken by towing a net in the ocean (a "tow") and then the count of each 
species in each tow was recorded (a "towcount").

So there is a Tows table and a TowCounts table. Each row in the TowCounts table 
has two fields: Species and Count. There is also the foriegn key to the Tows 
table. One Tow has many TowCounts: one for each species.

If a particular species was not found in a Tow, there is not a row in TowCounts 
for it. Ie there are no zero-count rows. This causes problems when extracting 
statistics using queries; it's not possible to calculate the mean because the 
number of rows does not correspond to the number of Tows, because there are no 
rows for tows where a species was not found.

Can anyone suggest a solution to this problem?

I can think of a couple:

1. Create zero-count rows in the TowCounts table. This would be a big job 
(there are over 60 species), so I'd have to do it programatically. I don't like 
this solution because it suggests my schema is incorrect, ie a missing zero-
count row causes a problem. Also, if a new species was added, zero-count rows 
would have to be added for every Tow. Ugly.

2. Don't calculate the mean using a query; use queries only to extract totals. 
Then calculate the mean using Excel or such by entering 'n' manually.

Cheers,
Jay.

-
You are subscribed to the progsoc mailing list. To unsubscribe, send a
message containing "unsubscribe" to progsoc-request@nospam.progsoc.uts.edu.au.
If you are having trouble, ask owner-progsoc@nospam.progsoc.uts.edu.au for help.