Creating zeros from non-existent data

D

DonD

I need to create a function that will calculate an appropriate mean and
variance when there are "missing" data. Let me explain with a fisheries
sampling example. I have two tables with a one to many relationship.
Table 1 is the sampling event table. It records the type of sample, the
sampling station, replicate #, date of sample, etc.
Table 2 is the child and contains the information of what I caught in the
sample (spp, length, weight, etc)

Lets say I have four replicates for sample 1 and that I catch the following
numbers of catfish in each rep: 5, 0, 3, 0. Obviously the mean would be 2
(8/4), but heres the kicker. For the reps where I didn't catch any catfish
(0's), I do not have a record for them. Likewise for the other 40 spp; we
only record what we catch.

Now, for an individual by individual species basis, I think I can do an
outer join (e.g. include all records from table 1) with the Nz function to
generate the zeros; However, I'm trying to figure out a way to do this "on
the fly" in a Pivot chart so I can look at mean catch rates of multiple
species simultaneously.

Any guidance or pointers in the right direction would be greatly appreciated.

DonD
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top