[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[ProgSoc] Statistics from a database
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.
You are subscribed to the progsoc mailing list. To unsubscribe, send a
message containing "unsubscribe" to email@example.com.
If you are having trouble, ask firstname.lastname@example.org for help.