Results 1 to 5 of 5
  1. #1
    seaape is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    3

    Following Environmental Events

    I'm planning a database to track environmental events. The main elements are Events, Reporter, and Species. Events and Reporter relates one-to-one, so that's easy.

    Each event may involve one or more instances of interaction with one or more species. The data types for each specie are different, so there is a table for each.

    My aim is to use a single report form That can display multiple and various subforms with minimal or no coding. I am familiar with VBA, and macros, somewhat. So far the table design is: (Trivial fields are condensed.)

    tblEvents
    -EventID
    -Location
    -DateandTime
    -ReporterID

    tblReporter
    -ReporterID
    -Name
    -ContactInfo

    tblSpecies


    -SpeciesID
    -SpeciesName
    -RelatedTable (How could this work?)

    tblSpeciesEvents
    -SpeciesEventID
    -EventID
    -SpeciesID

    tblTurtleEvent
    -TurtleEventID
    -SpeciesEventID
    -TurtleData (i.e. Shell size)

    tblWormEvent
    -WormEventID
    -SpeciesEventID
    -WormData (i.e. Segmentation)

    I'm pretty sure this works OK, (suggestions welcome) my problem is creating the form that can display any of several subforms and record multiple records for each. Wouldn't mind hearing some suggestions for that.

    Thank you all!
    Last edited by seaape; 06-23-2011 at 02:25 PM. Reason: Added related table

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    What I would do here is eliminate the table for each specie. If you make a generic "speciesData" field in tblSpeciesEvents, you can list them all there. You will know by the speciesID what the speciesData field is holding.

    For example, lets use your turtle (speciesID1) and worm (speciesID2):
    SpeciesEventID | EventID | SpeciesID | SpeciesData
    1 | ABC | 1 | 62
    2 | WMF | 2 | 36in

    Now, while the data doesn't necesarily jive, you can use the SpeciesID to tell just what kind of data you put in there. You know that all SpeciesID 1 will be segmentation and all SpeciesID 2 will be Shell Size. You can create conditionals (If/else, Select Case, etc) in your report to format that field as you wish.

  3. #3
    seaape is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    3
    The information and number of response fields vary widely. There are actually dozens of species, each with completely different data. If there were one form, it would require different prompts for each species, and most fields would be blanks, and the field names would have to be field1, field2, ... and only text types, and I'd need a constant reference to what they mean for each species.

    Thanks for the help, if I have to go that way, but that's where I started.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    The only other thing I could think of would be to take the specie with them most data fields, lets say 10, and create a tblSpeciesData with a PK, the 10 fields of a generic name (as you mentioned), and a FKspeciesID. You would then reference the PK of this field in the species events, and have an alias as the data field names depending on which species is being referenced.

    Further, if you keep everything as a text type, you can still run calculations with Cint().

    The reason I'm trying to get this onto one table is that in order to reference a different table for each species you would have to create separate SQL strings for everything and then use a Select Case to determine which SQL to base it off of. This would be a pain in the ass, of course.

  5. #5
    seaape is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    3
    There are plants, animals, fish, reptiles, and insects involved. Some species require taking much data, say 30 fields, including lookups specific to that species. Most species require fewer than 10. It depends on what the biologists are looking for. Seperate tables are more flexible in this regard. How do you add a field or another species with your design. Consider maintenance.

    I'm not sure how you get different prompts to load for each field for a couple of dozen different species. Theoretically, I could write the code to show or hide them. Sounds nuts to me.

    Doesn't access write the SQL?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Events are "Non-Events"
    By caddcop in forum Forms
    Replies: 4
    Last Post: 04-07-2011, 10:08 PM
  2. count of events
    By old_chopper in forum Access
    Replies: 1
    Last Post: 02-28-2011, 12:49 PM
  3. Different Events
    By jo15765 in forum Programming
    Replies: 2
    Last Post: 12-07-2010, 12:45 PM
  4. Events Confusion?
    By homerj56 in forum Programming
    Replies: 5
    Last Post: 10-27-2010, 01:45 PM
  5. Events Report, please help!
    By Suzan in forum Reports
    Replies: 3
    Last Post: 04-19-2006, 01:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums