Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Unanswered: Programming a biological db with a lot of zeros

    I’m programming a biological database of animals occurring in water samples.

    An investigator collects three samples at a site and then identifies the bugs present in each sample.

    The results look like this.

    SiteID___SampleID___SpeciesID___Abundance
    1__________1______Crab larvae_____500
    1__________2______Crab larvae_____25
    1__________3______Crab larvae_____0

    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.

    Thanks for reading- Biodata
    Last edited by BioData; 04-17-08 at 15:13.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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

  3. #3
    Join Date
    Nov 2007
    Posts
    6
    Dear healdem:
    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.

    -Biodata

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are we thinking in loops instead of sets?

    If you want to set the value of a field in a table to x where the value is currently y, what do you use..?
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Because he's wanting to generate records, not update them.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    still think setting a default value in the table is the smartest route
    failign that set a deafult value when the user adds a record
    say place some code in the forms before insert event

    Private Sub Form_BeforeInsert(Cancel As Integer)
    abundance.text=0 'sets the value of control abundance to 0
    End Sub

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    How is a default value going to help??

    He wants to auto-generate a bunch of records with zero as a value, not automate entering a zero during data entry... unless I am horribly mistaken
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2007
    Posts
    6
    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.

    Thank you all for your ideas and time.

    -Biodata

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    oops my mistake...

    ah so you want to write a series of records (a template)
    then I'd consider a button which triggered a SQL insert query

    you could specify what critters you are expecting in the survey and then insert the rows into the sub table.. shown on a subform?

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd consider a button which triggered a SQL insert query
    Yup. Probably the best way.

    Could also use VBA to loop through the species, but the insert query would be quicker.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •