Searching Across Tables

[JLC10123]

Background:

I am presently working with a database with 5 tables: Donations, People, Families, FamilyMembers (a join table between People and Families), and Companies. Donations can come from People, Families, or Companies (henceforth collectively referred to as "donors"). The same folks listed in People can be associated with specific Families (hence the FamilyMembers join), and for each donation by a Family, a specific party must be selected as responsible. For example, if the Smith family is making a donation, either Jane Smith or John Smith must be selected as the primary contact for that transaction. Ideally, at some point in the near future, a Person record will also include a list of relatives, which will show other People's relationship to that Person.

Goal:

I need to create one search field on the Donations screen that searches all three types of donor records at once. It needs to populate a drop-down list that narrows down applicable results with each keystroke. For example, if I type in "Smith", the drop-down list should populate with "Smith Family" (Family record), "John Smith" (Person record), "Jane Smith" (Person record), and "Smith Consulting" (Company record). The user should be able to click a name from this list to assign the donor. Then, based upon the user's selection, the appropriate hidden tab on the layout (relevant to the donor type) will be selected and the chosen party's contact information auto-populated.

Question:

What is the best way to create this cross-table search field?

Thoughts so far:

1) A "Donor" join table between People/Families/Companies and Donations.

Cons: Messy and inefficient. All Donation records would have to be created through a very silly join table with just one field.

2) Combining all the donor types into one single table (again, "Donor") instead of three, and making the donor type nothing more than an attribute.

Cons: Complicated and confusing. Ignores the Person to Family relationship and would mean I now have no idea how to:

i) Make a specific person the responsible party in a family donation; and

ii) Show calculated relationships of individuals to one another.

-----

There you have it! I am 1,000% open to ideas and suggestions. Thank you in advance for any input!

[Fitch]

I wish more people would take the time to organize their posts the way you did. Nice job. One question though: what's the relationship between People/Families/Companies and Donations? Do you have separate foreign key fields in Donations for each type of donor or do the keys have a prefix that identifies their source or what?

[JLC10123]

Thanks! My hope is that hyper-organization will make up for me having no idea what I am doing.

In response to your question, there are indeed separate foreign key fields in Donations for each type of donor. I have attached the ERD I started with and a view of the current relationships graph in case those would be helpful. Thank you for the speedy reply!