Field Search

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pearl - 02 May 2008 19:21 GMT

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?

Reply to this Message

John W. Vinson - 03 May 2008 01:01 GMT

>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.

Signature

John W. Vinson [MVP]

Reply to this Message

Pearl - 03 May 2008 03:01 GMT

thanks very much for the advice. I've never done that before but will try to
set it up

> >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
[quoted text clipped - 13 lines]
> 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.

Reply to this Message

John W. Vinson - 04 May 2008 00:37 GMT

>thanks very much for the advice. I've never done that before but will try to
>set it up

There are some good tutorials and introductory references available:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Signature

John W. Vinson [MVP]

Reply to this Message