A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Field Search



 
 
Thread Tools Display Modes
  #1  
Old May 2nd, 2008, 07:21 PM posted to microsoft.public.access.forms
Pearl
external usenet poster
 
Posts: 99
Default Field Search

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?
  #2  
Old May 3rd, 2008, 01:01 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Field Search

On Fri, 2 May 2008 11:21:00 -0700, Pearl
wrote:

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

John W. Vinson [MVP]
  #3  
Old May 3rd, 2008, 03:01 AM posted to microsoft.public.access.forms
Pearl
external usenet poster
 
Posts: 99
Default Field Search

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

"John W. Vinson" wrote:

On Fri, 2 May 2008 11:21:00 -0700, Pearl
wrote:

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

John W. Vinson [MVP]

  #4  
Old May 4th, 2008, 12:37 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Field Search

On Fri, 2 May 2008 19:01:00 -0700, Pearl
wrote:

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

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.