I would like to create a field where I can place multiple names (as many as
100) and then have the ability to search on that field for a specific name in
the group. Is there a field that I can use to do that?
Don't. That massively violates the principle that fields should be atomic -
having only one value.
You're using a relational database - use it relationally! If you have a one to
many relationship, use TWO TABLES in a one to many relationship. If, on the
other hand, you have a many to many relationship you need THREE tables. The
classic example is a class-enrollment application: you would have a table of
Students (names) with a primary key StudentID; a table of Classes with a
primary key ClassNo; and an Enrollment table with fields for ClassNo and
StudentID. If there are 100 students enrolled in Database Design 101, you
would add 100 *records* - not 100 fields, not 100 names - to the Enrollment
table, one for each student. This structure is properly relational, allows
names to be searched very readily, and is expandable to any number of names.
