Table design question

W

wade.wall

Hi all,

I have collected data on lily whorls and have the data stored in an
access database. I have it organized according the individual and
number of leaves per whorl.

I first entered the data thus:
Individual_Lily #leaves whorl 1 #leaves whorl 2 . . . . . # leaves
last whorl

with each count of leaves being a separate column. Therefore many of
the columns I have contain 0s because I had to have enough columns to
accommodate the individual with the greatest number of whorls.

My question is this. I assume that proper table construction would be

Individual_Lily specific whorl # leaves

but this would create a situation where I would have to enter the
Individual_Lily field everytime. Is that correct or is there a way
around this by way of a form or a macro?

As you can probably tell, I am new to databases, but trying to learn
proper construction, techniques.

Thanks a lot

Wade
 
J

Jeff Boyce

Wade

The first patter you described is what you'd have to do if you were limited
to a spreadsheet. You are correct in assuming a "narrow-but-deep" design.

But why would you need to (re-)enter the Lily (or LilyID) each time?

Take another look at your underlying data.

You have lilies.

Each lily can have multiple whorls.

This means you have a one-to-many relationship, and would use two tables,
one for the lily, and one for the lily X whorl data.

But this is not a spreadsheet, so you wouldn't need to (and shouldn't) enter
data directly in a table/tables. Instead, create one form that is based on
your lily table. Use Single Form for that, so the form shows one lily per
form.

Build a second form based on the Lily X Whorl table. Now, embed the second
form in the first as a "subform". When you do this, Access helps you
specify the relationship. You only need the LilyID (as a foreign key) in
the Lily X Whorl table, and Access will automatically put that in if you use
the main form/subform design.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John W. Vinson

Hi all,

I have collected data on lily whorls and have the data stored in an
access database. I have it organized according the individual and
number of leaves per whorl.

I first entered the data thus:
Individual_Lily #leaves whorl 1 #leaves whorl 2 . . . . . # leaves
last whorl
Click to expand...

Then you've entered it incorrectly (at least for normalization purposes).
with each count of leaves being a separate column. Therefore many of
the columns I have contain 0s because I had to have enough columns to
accommodate the individual with the greatest number of whorls.

My question is this. I assume that proper table construction would be

Individual_Lily specific whorl # leaves
Click to expand...

Not quite. You have a one (lily) to many (whorls) relationship, so you need
TWO TABLES:

Lilies
LilyID <some unique identifier such as an autonumber>
<information about the lily, e.g. variety name, location, ...>

Whorls
LilyID <link to Lilies; Long Integer if you use an autonumber>
WhorlNumber <1, 2, 3, ...>
Leaves said:
but this would create a situation where I would have to enter the
Individual_Lily field everytime. Is that correct or is there a way
around this by way of a form or a macro?
Click to expand...

A Form based on the Lilies table with a subform based on Whorls will make this
simpler. You'ld add the lily once only, and then just add each whorl in turn.
If you wish, it's easy to automatically insert sequential whorl numbers; post
back if you wish to do so.

You can run a "Normalizing Union Query" to migrate the data from your current
spreadsheet-like structure into the normalized table. Create a new query based
on your table; select only the Individual_Lily and [#leaves whorl 1] fields
for starters. On the menu choose View... SQL. You'll see something like

SELECT [Individual_Lily], [#leaves whorl 1] FROM yourtable;

Edit this to

SELECT [Individual_Lily], (1) AS WhorlNumber, [#leaves whorl 1] as Leaves FROM
yourtable WHERE [#leaves whorl 1] > 0
UNION ALL
SELECT [Individual_Lily], (2) AS WhorlNumber, [#leaves whorl 2] as Leaves FROM
yourtable WHERE [#leaves whorl 2] > 0
UNION ALL
SELECT [Individual_Lily], (3) AS WhorlNumber, [#leaves whorl 3] as Leaves FROM
yourtable WHERE [#leaves whorl 3] > 0
UNION ALL
SELECT [Individual_Lily], (4) AS WhorlNumber, [#leaves whorl 4] as Leaves FROM
yourtable WHERE [#leaves whorl 4] > 0
UNION ALL
<etc etc>

Save this query and base an Append query on it to populate the new Whorls
table.

John W. Vinson JVinson *at* Wysard Of Info *dot* com



John W. Vinson [MVP]
 
J

Jeff Boyce

Should have been "pattern"...

Jeff

Jeff Boyce said:
Wade

The first patter you described is what you'd have to do if you were
limited to a spreadsheet. You are correct in assuming a "narrow-but-deep"
design.

But why would you need to (re-)enter the Lily (or LilyID) each time?

Take another look at your underlying data.

You have lilies.

Each lily can have multiple whorls.

This means you have a one-to-many relationship, and would use two tables,
one for the lily, and one for the lily X whorl data.

But this is not a spreadsheet, so you wouldn't need to (and shouldn't)
enter data directly in a table/tables. Instead, create one form that is
based on your lily table. Use Single Form for that, so the form shows one
lily per form.

Build a second form based on the Lily X Whorl table. Now, embed the
second form in the first as a "subform". When you do this, Access helps
you specify the relationship. You only need the LilyID (as a foreign key)
in the Lily X Whorl table, and Access will automatically put that in if
you use the main form/subform design.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Top