The problem is I want to record the zeros, but I don’t want to enter a zero every time an animal is missing from a sample (or put another way, I only want to enter species that were present and that I counted). For one or two species adding the zeros would be easy, but I’m dealing with 400+ species, and adding a zero for them every time they are present in one sample, but not in the other would be quite cumbersome. Is there some way that I can tell Access, if a species is present in one sample (in this case samples 1 and 2), but not in the other (sample 3), to auto enter that species and give it a zero for abundance.
This is kind of difficult to explain in text. Please let me know if I can clarify my question.
set a default value when designing a table
or in the .addnew event (if you have already started to design the forms and the default no longer works)
but be warned that unless you actually want to record 0 for a specific site there is a risk by doing this that you could skew your statistical functions.. when calculating averages or SD. as long as you are aware of the issues relating to that its fine.
persoanlly it sounds to me like flaky design.. Id expect to have a table that identifies details of sureveys (ie date/time, who by place etc)
another table identifying the critters you expect to find
and an intersection table which identifies which critters were found as part of which survey
Actually the table I tried to show in my question is an intersection tbl, where the PK is a combination of the SiteID, SampleID, and SpeciesID, all of which have their own tables with associated data.
You'd essentially need to cycle through each sample (assuming there are no gaps in the sample numbers) for a given species and add the "zero entry". You could even repeat this for each species if you wanted to. A VBA loop could do that relatively easily. But I also would be very cautious about doing that one; it would throw in a LOT of zeros which could, as healdem said, skew your statistics.
StarTrekker is correct. I would like to automate a zero during data entry. I don't think a default would work because I would still have to select each organism (from the SpeciesID list) then set the value to zero (or default the value to zero). Really my 'dream' solution would allow me to just enter data for those Species that are present and have a count/abundance associated with them (e.g. Species 1 = 5, Species 2 = 15) then set the value to zero for those that don't appear in the sample (e.g. Species 3 to 50 = zero). Part of the problem is I have so many species to deal with from any one sample (up to 400) that manually entering the zeros would be too cumbersome.
I still don't understand why this data even needs to be stored.
One table for species, one table for test information (when and where etc) and another table to store the details of the test with a composite key of speciesID and testID.