A search box that filters through multiple fields

[WesHarding]

Hi Everyone!

I am seeking the help that this forum always seems to give!
My goal: I want to have a "First Name" search box, which will filter through contacts. However, these first name field(s) of contacts are contained within one table. For example, I will have one big farm, Bob's Farm for example. Bob's farm has 3 contacts: Bob, Jim, and Billy. I would like it so that either of those names could be typed into the search box and that farm would be retrieved. I tried doing this through my query but have resulted in getting zero results. To avoid the mental anguish I turned to you wonderful people!



Hope to hear back!

Cheers!

[WesHarding]

To clarify each first name is contained within a separate field within the table (Contact first name 1, 2, etc.)

[Homegrownandy]

How can the record of one farm have many contacts on the same table? I feel like this is probably not a normalised structure but that's why I'm asking.

your table should relate to one record. bob jim and billy are three separate entities and should be on the "contacts" table. Then... each contact.. If as an individual they can only be associated with one farm then have a field for that on their table.

you would have farm_ID for each contact...

Or if one person can be associated with many farms you will need a junction table that handles the relationship between the contacts and the farms.

I don't know if this is any use to you so feel free to question/ask anything.

[WesHarding]

What you're saying makes a lot of sense Homegrownandy. I was trying to make my boss happy with how he wanted this table to be structured. He actually wanted these contacts imbedded in a record that contains the farm (one field has a farm name, three other fields contain the names of its contacts). However, I believe this is confusing Acess and is likely why i'm running into this issue i've encountered. I will transfer all my contacts to a contacts table and like these to the farm.

Thank you for the help, once again this site provides clarity

[AccessPower]

EDIT: Guess you found what you're looking for