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