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!