I have a database of plant species with a layout that displays the species where they grow best, for example, in sun with dry soils. Fields include the species name, light requirements, and moisture requirements.
But some species are quite tolerant of light or moisture requirements. These species thrive in more than one light-moisture zone. A light tolerant species may thrive in both a sunny-dry zone and in a partial sun-dry zone. A species that is tolerant of both light and moisture conditions might thrive in even more zones such as sunny-dry soils, sunny-moist soils, part sun dry soils, and part sun-moist soils.
I can easily sort records for one light-moisture zone (e.g., the sun-dry soil zone), but need to display more tolerant species in a second (or even several) light-moisture zones, if they also thrive there. My sort lists 9 light-moisture zones (from sunny-dry to shady-wet with 7 zones between them). How can I display the record in EVERY zone in which the species thrives (sometimes two or more light-moisture zones).
Each record has the data I need, telling me if the species thrives in sun, part sun, and/or shade (3 fields) and dry soils, average soils, and/or wet soils (3 more fields). Fussy plays list just one light and one moisture zone. More tolerate plants list two or all three light zones and/or two or all three moisture zones in which they thrive.
I could, of course, just duplicate the species record and indicate which additional zone where I want that record to be listed. But I'm really resisting creating duplicate species records to create this layout that shows a record in one, two, or more zones (theoretically up to all 9 zones) at the same time. This is probably a classic relationship or portal concept, but I can't get my head around it.
Your suggestions might point me in the right direction to achieve a highly useful layout for the public to landscape with native species.
My Database Fields:
Light zone 1
Light zone 2 (my be an empty field)
Light zone 3 (my be an empty field)
Moisture zone 1
Moisture zone 2 (my be an empty field)
Moisture zone 3 (my be an empty field)
...and of course, a hundred more fields and dozens of additional layouts for other purposes, but we won't go there!
Thank you for any suggestions!