More Access Help

  • Thread starter Thread starter amy.smith4 \(removethis\)
  • Start date Start date
A

amy.smith4 \(removethis\)

I need to add a field to my database that is very large -
more than 255 characters. The field is basically a list
of names to list other partners in a firm. In some cases
it is only one partner, but in others it is more than
100. Additionally, some of the firms, actually groups,
don't have a group name, so my group name field only has
one person's name in it, and we have no way of knowing if
this is the "primary partner" if you will. This field
listing the additional partners lists everyone else.

Here is what I need to do. I need to be able to look up
or search on the "additional partners" field if the person
I'm looking for isn't the right person in the group name
field, and I need a field large enough to handle more that
255 characters. Is there a solution?
 
You are wanting to store multiple values in a single field and goes against
the basic rules of database design. The correct solution would be a separate
table for the partners linked to the first table by the primary key field.
This allows you to have anything from zero to a very large number of
partners for each firm (group).

What you say about the group name field sometimes only having one name in it
is also worrying. A field, any field, should only ever contain one thing -
name or whatever it may be. You should not have a field that contains both a
group name and the name of a person. If this is what you've got then you
should seriously consider redesigning your tables. In fact, you should store
people's names over at least two separate fields - FirstName and LastName. I
would have thought that the primary partner's name should be stored in the
second table along with all the other partners. You could easily include
another yes/no field to indicate the primary partner.

These are not academic points - they will make a huge difference to how
usable your database is, and how easy it is to maintain it. For example, if
a partner leaves a firm and you want to delete his name, how would you do
this. In the "correct" design you'd just find his record and delete it. If
you've got all the names in one field - up to 100 of them - you'll need to
manually scroll through the field until you find the correct name, and then
edit the field - much slower and prone to errors. What if you want to list
the partners in alphabetical order of surname? With everything in a single
field - totally impossible. With a two table design, no problem at all.
 
Thanks for your quick reply yesterday. I know exactly
what you mean about storing multiple values in a single
field, etc. However, I am a contractor and here just to
build this database. I have explained why this cannot be
done well in this way, but it's what they want. In fact,
I started out doing it the right way and they didn't like
it so I went back to their original request.

Your comment suggestions were very helpful though and I
think I know how to proceed now. Thanks so much!!

ASmith
 
I dont see that the customer has any business specifying how they want the
data *stored*. No user has any control over how Excel stores its data, for
instance. As long as Excel continues to show it on demand, in the form
people are used to, then surely it doesnt matter. Same applies to your
data. Show it however they want, on forms and reports. Hide the tables
totally from the end user, but make the structure so that *you* can get the
data to them in the fastest, easiest way.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top