Multivalue Fields and Normalization

[Brian Carlson]

In general are multivalue fields a bad idea to use? I am trying to ensure my
small database is normalized as possible, but it seems that I every table I
try to create ends us breaking into smaller related tables and these having
to be additionally broken. It seems that multivalue fields will allow me to
eliminate some of this, but I do not want to lose functionality for
conveniance. Thanks in advance.

 

[John W. Vinson]

In general are multivalue fields a bad idea to use? I am trying to ensure my
small database is normalized as possible, but it seems that I every table I
try to create ends us breaking into smaller related tables and these having
to be additionally broken. It seems that multivalue fields will allow me to
eliminate some of this, but I do not want to lose functionality for
conveniance. Thanks in advance.

I'd avoid them.

They are available in Access, but NOT in any of the databases to which you
might transfer it - SQL/Server, Oracle, MySQL, etc. They're very much
nonstandard.

Under the covers, they're actually implemented using a related table ANYWAY -
a concealed related table that you can't at all easily work with.

Using the Table Analyzer may be causing you to "overnormalize" - it gives
suggestions, sometimes good ones, but it has no human intelligence. Database
normalization is an art as well as a science, and requires some judgement; and
AFAIK no computer yet has that attribute.

 

[Allen Browne]

Multi-valued fields (MVF) do not avoid those small tables you refer to; it
merely hides them. Hidden away in your database, you still have a related
table: it's just that you *can't* get to that table.

That's one of the main problems with MVFs: you're limited in what you can do
with them. If you are a complete novice who has no idea what normalization
is, they may help you get where you need to go. But if you know what a
related table is, you're better of creating one IMHO, rather than snooker
yourself with stuff you can't get at.

There are other issues as well, e.g.:
- upsizing: 'real' databases don't have MVFs.
- limitations in SQL statements (e.g. ... FROM Table1 IN
'c:\somefile.accdb')
- problems with filters: http://support.microsoft.com/kb/926701
- problems with OldValue (not maintained correctly.)

And, of course, you have to re-write any generic code you use for handling
recordsets, because a 'field' is no longer a discrete value. (Some would
that storing complex (non-atomic) data in a field is itself a violation of
normalization.)

So, if you know enough to ask the question, my recommendation would be not
to use them.

 

[Mark]

While the other posters' points are very true, I do think multi-valued fields
are great for small databases that are never destined to be anything larger
than Access. If there's any chance that you're going to have to transition
this database to another system, save yourself the trouble and avoid MVF's.
However, if this is something small, maybe for an individual department or
office, I've found multi-valued fields to be a nice feature.

Mark

 

[Jeff Boyce]

Define "nice" ... ?easy to set up ... ?easy to make do what you want

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mark]

Sure, by "nice" I mean they are both easy to set up and easy to use. Most
databases are designed with other end users in mind besides the database
creator. In most cases, these end users aren't as knowledgeable about proper
database design and use, so it is important to design the database and its
interface in as simplistic a fashion as possible, both so that it's easy for
them to use and so that they don't "screw it up" by entering unnormalized
data. Rather than making users create entries themselves in a separate
related table, MVF's simply allow them to choose the applicable values from a
list of choices. It is more of an intuitive process than the alternative.

Mark

 

[John W. Vinson]

Rather than making users create entries themselves in a separate
related table, MVF's simply allow them to choose the applicable values from a
list of choices.

So do properly designed Forms with Subforms. Users should certainly NEVER be
required to "create entries themselves" - in fact should never even see a
table datasheet!

 

[Brian Carlson]

John:
You have touched on the crux of my problem. I am using a main form with
multiple subforms. One of these subforms has a subform underneath it.
However, the first tier subform will not allow continous view, which is
causing me problems.

 

[Allen Browne]

So you are investigating alternatives to sub-subforms, Brian?

In the old Northwind sample database, there's a form named Customer Orders.
It has a subform where you select an order. When you do, the details of that
order appear in another subform below it. This is not a sub-subform (i.e.
both subform sit directly on the main form.) Its Link Master Fields is:
[Customer Orders Subform1].Form![OrderID]

I've used a modified version of that approach several times, so when you
click on a record in one continuous subform, the details are displayed in
the other continuous subform beside it.

If the sub-subform is for display purposes only, another option might be to
place a list box in the Form Footer of your continuous subform.

For me, those are preferable to MVFs. For you, take your pick.

 

[John W. Vinson]

John:
You have touched on the crux of my problem. I am using a main form with
multiple subforms. One of these subforms has a subform underneath it.
However, the first tier subform will not allow continous view, which is
causing me problems.

You can use "correlated subforms" with a little bit of hassle. For example you
can put a textbox on Mainform with a control source

=SubformA.Form!LinkingFieldControl

and use that textbox as the MasterLink field for SubformB, to make SubformB
dependent on the currently selected record on SubformA.

 

[Brian Carlson]

Allen:

I am an extreme novice to this, but I will give it a whirl. Where would I
find the Northwinds sample database so I could take a ganderand see if I
could decipher it? Thanks for the help.

Brian

So you are investigating alternatives to sub-subforms, Brian?

In the old Northwind sample database, there's a form named Customer Orders.
It has a subform where you select an order. When you do, the details of that
order appear in another subform below it. This is not a sub-subform (i.e.
both subform sit directly on the main form.) Its Link Master Fields is:
[Customer Orders Subform1].Form![OrderID]

I've used a modified version of that approach several times, so when you
click on a record in one continuous subform, the details are displayed in
the other continuous subform beside it.

If the sub-subform is for display purposes only, another option might be to
place a list box in the Form Footer of your continuous subform.

For me, those are preferable to MVFs. For you, take your pick.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You have touched on the crux of my problem. I am using a main form with
multiple subforms. One of these subforms has a subform underneath it.
However, the first tier subform will not allow continous view, which is
causing me problems.

 

[Brian Carlson]

John:

If you say it would be a little hassle, for me it would be near impossible.
I am not up to that level of sophistication...I just figured out
subforms...some what.

One follow-up question: It seems that having a subform to a form should
create a relationship between the two tables represented, but looking at the
relationship windows this is not the case...am I missing something?


Brian

 

[Allen Browne]

If you have the default install of Access 2003 or earlier, you will find it
on your local hard disk, in your Program Files, under Microsoft Office, if
you follow the tree down.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

I am an extreme novice to this, but I will give it a whirl. Where would I
find the Northwinds sample database so I could take a ganderand see if I
could decipher it? Thanks for the help.

Brian

So you are investigating alternatives to sub-subforms, Brian?

In the old Northwind sample database, there's a form named Customer
Orders.
It has a subform where you select an order. When you do, the details of
that
order appear in another subform below it. This is not a sub-subform (i.e.
both subform sit directly on the main form.) Its Link Master Fields is:
[Customer Orders Subform1].Form![OrderID]

I've used a modified version of that approach several times, so when you
click on a record in one continuous subform, the details are displayed in
the other continuous subform beside it.

If the sub-subform is for display purposes only, another option might be
to
place a list box in the Form Footer of your continuous subform.

For me, those are preferable to MVFs. For you, take your pick.

You have touched on the crux of my problem. I am using a main form
with
multiple subforms. One of these subforms has a subform underneath it.
However, the first tier subform will not allow continous view, which is
causing me problems.

 

[John W. Vinson]

John:

If you say it would be a little hassle, for me it would be near impossible.
I am not up to that level of sophistication...I just figured out
subforms...some what.

If you'll post the names of the two subform controls and the name of the
fields that link them, one of us can give you precise step by step
instructions.

One follow-up question: It seems that having a subform to a form should
create a relationship between the two tables represented, but looking at the
relationship windows this is not the case...am I missing something?

Yes. You're missing the fact that tables are PRIMARY, and forms are *nothing
but tools*, windows that let you interact with the data in table. Forms don't
have relationships; tables do. The form may well reflect the relationship
between the tables, but that's actually optional. Nothing you do to the
structure of a form affects the underlying tables in any way.

 

[Jeff Boyce]

Mark

I'm with you ... unless an application is easy to use, it's ... 'shelfware'.

But a well-designed graphical user interface would never expose end users to
the underlying table structure, and wouldn't require them to know anything
about relational databases or normalization.

From my experience, "Easy ... is HARD!". Easy to use (an end-user
assessment) requires considerable work from the developer to hide all the
intricacies and protect the user from ever making an 'unnormalized' entry.
Doing that is NOT "easy to set up".

And since most end-users view data entry as a necessary evil in managing
some process, it is as critically important that the end-users have an easy
way to get information back out. This is where I believe the use of
multi-valued fields starts creating difficulties.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian Carlson]

John:


I am not sure if your original suggestion is the same as or a slight
variation of Allen's, but in either case. I curretnly have a table called
t_CrewDaily. This table has the following fields: CrewDate, CrewName,
CrewLabor, and CrewEquipment. The CrewEquipment field is currently the
multivalue field, which is being looked up from another table t_Equipment. I
would like to get rid of the multivalue field without losing the option to
have t_Crewdaily as a continous subform, yet still have the option have the
CrewEquipment field as a lookup. I am not sure if this is clear, but in
either case thank you in advance for taking the time to assist me.

Brian