Trying to create a simple wild plant or wildlife checklist

[wlegard]

Hi.

I'm the botanist at a large Park District that manages over 50 parks. I'm trying to create a simple wild plant checklist for eventual use on a tablet (like an iPad with the Access Mobile Database Client). I'd also like to create a comparable wildlife checklist that can be used by the wildlife biologists on staff.



Currently I do surveys in more than 50 parks using a traditional printed alphabetical checklist of all the species that may occur in a particular park. The paper form for a plant survey looks roughly like this (with '.....' indicating many additional rows):


Date: __March 25, 2012__ Park Name: _Wildcat Canyon______

Ferns or fern-like:
O Adiantum aleuticum - FIVE-FINGER FERN
O Adiantum jordanii - CALIFORNIA MAIDENHAIR FERN
.....
Grasses or grass-like:
O Agrostis avenacea - PACIFIC BENTGRASS
.....
Hebaceous:
O Achillea millefolium - YARROW
.....
Woody:
O Acer macrophyllum - BIG-LEAF MAPLE
........

The printed plant checklist is currently 26 pages long, with a total of more than 1,300 species. I check off each plant I see in the field, and back in the office I manually update my access database with the results.

My goal is to create a tablet-sized access database where:

1) 'Date' could be entered manually (or automatically).
2) 'Park Name' could be chosen from a list (a drop down list?).
3) The plant list (or wildlife list) could be easily scrolled or searched with a text entry.
4) Observed species could be easily checked off (with a checkbox or radio button?).
5) The results could easily be transferred to a master database back in the office.

For lookup tables, I've created:
1) tblParks: ParksID (autonumber primary key), ParkName (text) - 66 records
2) tblPlants: PlantsID (autonumber primary key), PlantName (text), CommonName (text) - 1,359 records

To aid in searches (filters?), I've created:
1) tblType: TypeID (autonumber primary key), TypeName (Ferns, Grasses, etc.) - 4 records
2) tblFamily: FamilyID (autonumber primary key), FamilyName (text) - 146 records

At this point, I'm stuck.

Should I use a blunt force approach: Add 'Date', 'ParkName' and 'Check' fields to tblPlants and just edit a big table? I think I could set that up, but I'd prefer a more elegant solution.

Should I use a survey results table (tblSurvey) that includes Date, ParksID/ParkName, and PlantsID/PlantName in each record? That sounds like the right solution, but I currently don't know how to get there. How should I design and link it? And how should the data entry form be designed?

Thanks in advance for any suggestions.

[June7]

Your last solution is the conventional approach for relational database. The relationship is many-to-many between survey dates and plants. This would mean a table of general survey info (ID (pk), DateSurvey, Surveyor, Location) and a junction table (SurveyDetails) that will associate the Survey with identified plants (ID, SurveyID (fk), PlantID (fk), Quantity, Condition).

Data entry would be a form/subform arrangement. Main form bound to Survey, subform bound to SurveyDetails. SurveyDetails would not use checkboxes, instead use combobox to select plant from list.

Your other approach would seem to be easier for data entry but could result in a lot of frustration evaluating the data.

It is a balancing act between normalization of data and ease of data entry/output.