Insidious Plot to Normalize Data!! Bwahahaha!!

[Guest]

Hi folks! Sorry for the melodramatic introduction... Here's the thing:
I've got a database that relies heavily on a non normalized table. The
structure is like this: Order Number is the primary key and there are about
30 fields with information about the particular order that are filled in by
Order Entry personnel. There are four fields (and related fields) for
entering information about up to four different part numbers per order. I
would like to normalize this table with a union query that John Spencer
helped me develop so that I can split off the part numbers to another table
and reference them by Order Number. I figure that I will need two tables:
one for the Order information, and another for the Part Number information.
And , of course there are tables for things like our vendor information that
do not change and are already split off onto their own tables. My question
is this: Is there a way to acomplish this while maintaining the original
table and its relationships? There are tons of relationships based on this
table and its current structure and I don't want to screw them up, but I need
the added functionality of (a) normalized table(s) to develop other forms and
reports. There is also a ton of archive data IN SEPARATE DATABASES
(ferchrissakes) that I need to be able to access with the current setup.
(Until it too gets normalized...) I have toyed with the idea of apending
data to two separate tables (tblOrderInfo, and tblPartNumber) with an
AfterUdate event or something similar. That way everyone else can continue
to work as if nothing had happened and I can develop other tools with the
normalized data. What do you think of that idea? Any traps/pitfalls to
avoid? Alternate suggestions? IMPORTANT RELATED QUESTION: How can I limit
the normalized tblPartNumber table to just four part numbers per work order?
Thanks a lot, guys... Hopefully, you'll be able to help me out!


Why are you asking me? I dont know what Im doing!

Jaybird

 

[John W. Vinson]

Hi folks! Sorry for the melodramatic introduction...

Here's the thing:
I've got a database that relies heavily on a non normalized table. The
structure is like this: Order Number is the primary key and there are about
30 fields with information about the particular order that are filled in by
Order Entry personnel. There are four fields (and related fields) for
entering information about up to four different part numbers per order.
:-{(

I would like to normalize this table with a union query that John Spencer
helped me develop so that I can split off the part numbers to another table
and reference them by Order Number. I figure that I will need two tables:
one for the Order information, and another for the Part Number information.

Well... *three* tables. One for PartNumbers (if you don't already have one),
and something like Northwind's OrderDetails table with one record for each
part *ordered*.

And , of course there are tables for things like our vendor information that
do not change and are already split off onto their own tables. My question
is this: Is there a way to acomplish this while maintaining the original
table and its relationships?

Not without the VERY considerable pain of maintaining the same data
redundantly in two different tables. This will be an update and maintenance
nightmare. I'd really, really suggest implementing the normalized structure as
a "shadow" copy of the wide-flat, testing it thoroughly (not just yourself but
multiple typical users, especially users with cyberphobia).

There are tons of relationships based on this
table and its current structure and I don't want to screw them up, but I need
the added functionality of (a) normalized table(s) to develop other forms and
reports. There is also a ton of archive data IN SEPARATE DATABASES
(ferchrissakes) that I need to be able to access with the current setup.

Can you link to these tables?

(Until it too gets normalized...) I have toyed with the idea of apending
data to two separate tables (tblOrderInfo, and tblPartNumber) with an
AfterUdate event or something similar. That way everyone else can continue
to work as if nothing had happened and I can develop other tools with the
normalized data. What do you think of that idea? Any traps/pitfalls to
avoid?

Oh, lots and lots. "You are in a maze of twisty passages, all alike..."

What about deleting records? What about changing a part number in an order?
What about adding a new part number to an existing order? What about removing
a part number from an existing order? Like I said... a nightmare.

Alternate suggestions? IMPORTANT RELATED QUESTION: How can I limit
the normalized tblPartNumber table to just four part numbers per work order?

Is that a *business requirement* or just a constraint introduced by your
current database? If the latter, why would you want to retain it? If the
former... why? If someone orders five parts do you tell them to go to your
competitor, or put in two orders, or what?

Thanks a lot, guys... Hopefully, you'll be able to help me out!

"I'll be glad to help you out... which way did you come in?" <in the same
spirit as the subject line>

John W. Vinson [MVP]

 

[Guest]

Well, i was afraid of that... Really, I just need something to stand in
until the main table gets normalized and all the associated forms, queries,
and reports get re-created. It could take some time... I guess the big
question I have is how to limit the work order to four part numbers (since I
guess I need to go ahead and do all the things that everyone advises me not
to do). I need to limit the work order to four part numbers to keep them
from getting too complex. If a work order were to have twenty parts on it,
it would be a nightmare to keep track of. Back to your recommendation for
breaking up my main table into three... I have used a two table approach
with a LEFT INNER join with some success in the past. Is there a reason you
don't like it? What would YOU do in my shoes? Attempting to normalize this
table now would screw everything up... I would be the least liked kid in
kindergarten... again!
--
Why are you asking me? I dont know what Im doing!

Jaybird

 

[John W. Vinson]

Well, i was afraid of that... Really, I just need something to stand in
until the main table gets normalized and all the associated forms, queries,
and reports get re-created.

Not sure what you mean by "stand in". There's no reason you can't keep using
the existing non-normalized table and all the existing forms, reports, etc.
while you're developing the normalized version; you can take a snapshot of the
existing data, or a subset of it, just to have "real" data to work with. Since
you'll mainly be developing the user interface it's not essential that the
data be completely concurrent, is there?

It could take some time... I guess the big
question I have is how to limit the work order to four part numbers (since I
guess I need to go ahead and do all the things that everyone advises me not
to do). I need to limit the work order to four part numbers to keep them
from getting too complex. If a work order were to have twenty parts on it,
it would be a nightmare to keep track of.

If you have a Form (for orders) with a Subform (for parts used in that order),
you can put VBA code in the Subform's BeforeInsert event. Use DCount() to
count the number of parts already in the order, and just set the Cancel
argument of Beforeinsert to True if there are already four - and you'll also
want to use MsgBox to warn the user that they can't do any more. E.g.

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[PartsUsed]", "[OrderID] = " & Me.OrderID) >= 4 Then
Beep
Cancel = True
MsgBox "Only four parts per order!", vbOKOnly
End If
End Sub

Back to your recommendation for
breaking up my main table into three... I have used a two table approach
with a LEFT INNER join with some success in the past. Is there a reason you
don't like it? What would YOU do in my shoes? Attempting to normalize this
table now would screw everything up... I would be the least liked kid in
kindergarten... again!

I'd normalize.

A table for Orders.
A table for Parts, with one record for every part which might be used in an
order (or in many orders); I presume that a given part can be used in multiple
orders.
A table for PartsUsed with fields for OrderID and PartID, links to the primary
keys of the Orders and Parts tables; and perhaps other fields such as quantity
and unit cost (I don't know your business so I don't know what would be
appropriate).

No Query or left joins needed: just use a Form based on Orders, a subform
based on PartsUsed, and a ComboBox on the subform based on Parts. See the
Northwind sample database Orders form for a very close parallel.


John W. Vinson [MVP]

 

[Guest]

Oof! Awful cavalier with my street cred on the playground, aren't we? So,
normalize is the way to go... I'm glad to hear that I can run this thing
concurrently. The plan I had conceived was to run a Union Query (to break up
the part number fields into separate records) from within one or several Make
Table Queries that would actually do the normalization. I'm wondering if I
should run this (these) queries everytime I open the forms that I will be
basing them on (thereby updating the data right before it is referenced) or
if it should be appended to this (these) table(s) periodically as the data is
entered from the Order Entry table?

John, I should take this opportunity to say how grateful I am that you and
others take the time to think about and answer questions like these. Without
the benefit of an in-house access guru, this forum and the generous
contributions of persons like yourself are the next best thing. I have
learned (slowly) and continue to learn a lot from you guys. Thanks so much.


--
Why are you asking me? I dont know what Im doing!

Jaybird

Well, i was afraid of that... Really, I just need something to stand in
until the main table gets normalized and all the associated forms, queries,
and reports get re-created.

Not sure what you mean by "stand in". There's no reason you can't keep using
the existing non-normalized table and all the existing forms, reports, etc.
while you're developing the normalized version; you can take a snapshot of the
existing data, or a subset of it, just to have "real" data to work with. Since
you'll mainly be developing the user interface it's not essential that the
data be completely concurrent, is there?

It could take some time... I guess the big
question I have is how to limit the work order to four part numbers (since I
guess I need to go ahead and do all the things that everyone advises me not
to do). I need to limit the work order to four part numbers to keep them
from getting too complex. If a work order were to have twenty parts on it,
it would be a nightmare to keep track of.

If you have a Form (for orders) with a Subform (for parts used in that order),
you can put VBA code in the Subform's BeforeInsert event. Use DCount() to
count the number of parts already in the order, and just set the Cancel
argument of Beforeinsert to True if there are already four - and you'll also
want to use MsgBox to warn the user that they can't do any more. E.g.

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[PartsUsed]", "[OrderID] = " & Me.OrderID) >= 4 Then
Beep
Cancel = True
MsgBox "Only four parts per order!", vbOKOnly
End If
End Sub

Back to your recommendation for
breaking up my main table into three... I have used a two table approach
with a LEFT INNER join with some success in the past. Is there a reason you
don't like it? What would YOU do in my shoes? Attempting to normalize this
table now would screw everything up... I would be the least liked kid in
kindergarten... again!

I'd normalize.

A table for Orders.
A table for Parts, with one record for every part which might be used in an
order (or in many orders); I presume that a given part can be used in multiple
orders.
A table for PartsUsed with fields for OrderID and PartID, links to the primary
keys of the Orders and Parts tables; and perhaps other fields such as quantity
and unit cost (I don't know your business so I don't know what would be
appropriate).

No Query or left joins needed: just use a Form based on Orders, a subform
based on PartsUsed, and a ComboBox on the subform based on Parts. See the
Northwind sample database Orders form for a very close parallel.


John W. Vinson [MVP]

 

[Larry Linson]

Oh, lots and lots. "You are in a maze of twisty passages, all alike..."

Sounds like an advanced version of the old pre-DOS and DOS BASIC game,
Wumpus Hunt.

But, trying to gradually normalize a database and an archive does fit that
description, John.

If I were faced with such a task, I'd try to automate it, doing the
normalization with VBA code, then test thoroughly with copies of the
production database, until I was "dead-dog-certain" I'd got all the obvious
errors out of it.

Then, I'd pick a "low-use" period and make it a "no-use" period -- barring
users on a weekend night or such -- and run the normalization code on the
production version to convert it. I'd have converted a copy of the archive
during my testing. Then I would enlist some users to do a "shakedown run" on
the newly-normalized database (users can accidentally find errors better
than even professional testers, at times). Once I was comfortable, then I'd
promote the tested version to be the production version, and let the users
back in.

I did something like that when a client wanted enhanced error handling*
added to an ADP... fortunately, all the offices using the database were
closed on Sundays, so all we had to do was notify anyone who planned to come
in and do a little weekend catch-up that they couldn't do so, "Not this
weekend."

* Identification of the specific procedure in which the
error occurred, the user ID, date and time in addition
to the information about the error itself, and logging
errors when they occurred. This required changing the
errorhandling in every procedure in the DB.

Larry Linson
Microsoft Access MVP

 

[Guest]

Thanks a lot for your response, Larry. I'm not sure what you mean by backing
into the changes using VBA code, however. We do have down time on the
weekend, so that would be a convenient time to roll out anything when and if
I get it going.