Will,
After seeing that some of my assumptions re: your current structure have
proved incorrect, the scope of rendering your application into fully
normalized tables that will be easy to maintain, while not conceptually
difficult, is larger than I first imagined.
The first step is getting your tables "normalized"--that is redesigning them
such that they follow certain rules of relational database design that render
it into one or more hierarchical, normalized "forms. Most developers try to
meet the requirements of 3rd normal form. Access doesn't fully support some
normal forms. I have posted links on the topic at the end of this post.
Inadequacies of Current Design:
--------------------------------------------------------
- Your current design violates first normal form because it contains
repeating groups: Descx, Bfillx, Qfillx, Uphx. Searches, for example, for a
given Quilt fill must pool searches across the QFill1, QFill2, QFill3 &
QFill4 fields.
- Furthermore, since all of these groups, plus the Spring, Pattern, Needle
Label, and Label Description fields are text fields, you have redundant data,
which is likely to be riddled with misspellings, further frustrating search
attempts. If you want to know which models use Spring 7CX, you may have to
search by:
7CX
7 CX
7C-X
- I suspect Label Description is fully defined by the Label field. If so,
the Label Description field is unnecessary.
- I'm not sure of the purpose of the multiple Description fields. It seems
they could either be a single Text or Memo field or belong in a one-to-many
detail table.
Correcting Single Field Design Inadequacies - Overview
---------------------------------------------------------------------------------------
While none of the steps are conceptually difficult, they are sufficiently
complex that I've broken it down into this Overview and more detailed
instructions below. Read the post completely before proceeding.
Major Steps:
- Normalize the tables by creating "lookup" tables that store a description
ONCE, thereby eliminating spelling permutations. In the process, we will use
temporary IDs to denote the equivalence of any spelling permutations that
currently exist.
- Create detail tables that eliminate the field groups. It sounds like you
may have already done this.
- Modify the existing form, and create and embed subforms for the detail
tables.
Resulting Table Designs
--------------------------------------------------------------------------------
The resulting structures will store only the numeric key from the "lookup"
table (called a foreign key) in the main and detail tables. The quotes
around "lookup" means that these are tables that you can use as the RowSource
of a combo box, NOT the to-be-avoided, problematic "lookup field"
functionality provided by Access in the table definition phase. They are
simply separate tables with an AutoNumber or other numeric primary key, and a
text description field.
Since BFillx, QFillx, and Uphx all contain fill materials, a single
FillMaterial lookup table can handle all three.
The resulting structure will look like the following. Choose more
meaningful field and table names as appropriate.
Mattresses (Main Table)
-----------------------------
ID AutoNumber (PK)
ModelNumber Text
SpringID Integer (Foreign Key to Springs)
PatternID Integer (Foreign Key to Patterns)
NeedleID Integer (Foreign Key to Needles)
LabelID Integer (Foreign Key to Labels)
Lookup Tables:
Springs
------------------------------
SpringID AutoNumber (PK)
Spring Text
Patterns
------------------------------
PatternID AutoNumber (PK)
Pattern Text
Needles
------------------------------
NeedleID AutoNumber (PK)
Needle Text
Labels
------------------------------
LabelID AutoNumber (PK)
Label Description
Descriptions *
------------------------------------------
DescriptionID AutoNumber
Description Text
* This might be a single text field in the main table w/no Lookup table
required, or a lookup table and a one-to-many detail table, depending on your
intent.
FillMaterials
------------------------------
FillMaterialID AutoNumber (PK)
FillMaterial Text
DetailTables (all in one-to-many relationship with Mattresses):
(I prefer to name detail tables using the name of the main & lookup tables +
Detail, but name them however you wish)
Note that all contain the MattressID field, by which we will link their
respective subforms to the main table.
MattressBattonDetail
-------------------------------------
MattressBattonID AutoNumber (PK)
MattressID Integer (FK to Mattresses)
FillMaterialID Integer (FK to FillMaterials)
MattressQuiltDetail
------------------------------
MattressQuiltID AutoNumber (PK)
MattressID Integer (FK to Mattresses)
FillMaterialID Integer (FK to FillMaterials)
MattressUphDetail
------------------------------
MattressUphDetailID AutoNumber (PK)
MattressID Integer (FK to Mattresses)
FillMaterialID Integer (FK to FillMaterials)
MattressDescriptionDetail
-----------------------------------------
MattressDescriptionDetailID AutoNumber (PK)
MattressID Integer (FK to Mattresses)
DescriptionID Integer (FK to Descriptions)
Correcting Data - Detail
---------------------------------------------------------------------
- Create a backup of your database.
- Modify the design of the Mattresses table, adding the following integer
fields: SpringID, NeedleID, PatternID, TempSpringID, TempNeedleID,
TempPatternID.
- Sort the Mattress table by Spring, and look for any spelling
permutations. Assign the same integer value to the TempSpringID field for
any equivalent springs, and a unique integer value to each unique spring.
For example, you might end up with something like the following:
Spring TempSpringID
-----------------------------------------
XC102 1
XC 102 1 (spelling permutation)
XC103 2
XC104 3
Xc104 3 (spelling permutation)
- Create the Spring table. Include an extra integer field to hold the
TempSpringID's just assigned.
- Review the Materials table. For each unique TempSpringID, enter this
value and a single "master" description for it into the Springs table.
Access will automatically assign the AutoNumber SpringID. You now have your
master "lookup" records.
- Create a query consisting of the Mattresses and Springs tables. Link them
by the TempSpringID, but delete the link Access assigns on SpringID. Drag
the SpringID of the Mattresses table to the query grid.
- Choose Query, Update Query from the menu.
- In the Update To: row, type Springs.SpringID. Access will place brackets
around the table and field names.
- Choose View, SQL to see the SQL. It should look like the following,
different ONLY by any different field and table names you choose:
UPDATE Mattresses INNER JOIN Springs ON Mattresses.TempSpringID =
Springs.TempSpringID SET Mattresses.SpringID = [Springs].[SpringID];
- If all looks good, press the Exclamation icon to run the update query.
- Review the Mattresses and Springs tables side-by-side. Whereever the
TempSpringID field matches from the Mattresses to the Springs table, the
SpringID should also match. If it does, you can delete the TempSpringID
field from both tables.
Modifying the Main Form
---------------------------------------------------------------------------------------------
- To display it correctly on your main form, load it in design view. Enable
the combo box wizard by selecting View, Toolbox, and making sure the button
with the wand and stars is depressed. Now add a combo box, preferably next
to the existing textbox bound to the text-based Spring field. Tell the
wizard to look up values from the Springs table. Select both fields, and
Hide Key Field (Recommended). This will display the text field in the combo
box but store the numeric key field. At the end, tell the wizard to store
the selection in the SpringID field. Save the form and return to Normal view.
- Scroll through the existing records. The value in the text Spring field
should correspond (less spelling permutations) with the value displayed in
the combo box. If this is verified, you can delete the textbox from the
form, and delete the text Spring field from the Mattresses table.
- You've now normalized the Springs field. Repeat the data correction and
form modification processes for the other main form fields--Needle, Pattern,
and Label.
When you've got these all working on the main form, repost. I'll think
carefully about how to handle the field groups in the meantime, it's a little
more complex now, since the fields are simply text.
Hope that helps.
Sprinks
Normalization Links:
ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878
