Search multiple fields in multiple tables from one layout
Four tables, each representing a different US Federal Census report (ex: one for 1870, one for 1880, etc.), with the census form used as the layout "base" behind fields. A single record might contain 8 family members; each family member has its own name, gender, birth year, marital status, etc. Those fields are labeled Name 1, Name 2, Name 3, Gender 1, Gender 2, Gender 3, Birth Year 1, Birth Year 2, Birth Year 3, and so on.
To create a single "universal search" layout that allows me to type in a person's name, click a button, then have it show each census year (layout) that person appears in (ideally, along with their age, marital status, and location). Basically, searching across all layouts/tables simultaneously, looking for James Smith, then displaying "here's where the name occurs" in the results.
- - - - - - - - - - - - - - -
Hope this makes sense. If not, please ask questions. In the end, the database will contain a few hundred records spanning 20+ layouts, each with a corresponding Table (since no two census reports are alike).
Help, please. Am I nuts for trying this? If not, how do I accomplish it?