Assistance Please

[thebigmac]

I’m creating a database to keep track of various options and components but I
am having trouble with some areas of the database development.

The manufacturer I work with creates a product which I shall call a WIDGET.
Each Widget is serialized with a unique number (a primary key). Each Widget
can also be customized with a variety of options the manufacturer makes
available to the client. Thus each Widget is unique but can have many options.


The options are divided into SETS. Example: one set covers the kitchen,
another set the garage, still another the bathrooms. Each option set is
identified by a unique number set and each option by a unique number, such as
the kitchen is identified by the 10000 series numbers (10000-10999). The
garage by the 11000 series (11000-11999). The bathrooms by the 12000 series
(12000-12999) and so forth. (Example: a towel rack would be 12031, a stove
10056.)

The original option set was created in Excel which I imported into Access. It
is a table with the Option number being the primary key. Using this table I
set up a query for each numbered set, hence "dividing" the original file into
many smaller files; one for the kitchen, one for the garage and so forth.
This works well and I am pleased with the results. (My original thought was
to use these queries in the database but with all the trouble I am having I
may not be able to.)

My database is built around the unique Widget number. Knowing that each
Widget is unique but can have many options I set out to create a series of
forms that reflect the unique Widget number but allows me to select
individual options (one Widget, numerous options).

It doesn’t work. I cannot get my form, my queries, or tables to recognize one
Widget with numerous options. I am able to pull up either the Widget number
or the Option number but not both, which is what I need to do. When I do get
both I cannot pull in the Widget number assigning one Widget to the Options
available. Most distressing. The blasted thing won't do what I want it to do!

What I have done:

I established a Widget primary key in the OPTIONS table. No good. Access will
not allow me to create the key. I get the message that the key field cannot
contain a null value. Makes sense as the table is already populated.

I created a query to connect the Widget primary key to a field in the Options
table (original Options table not the individual queries, but I did that too).
I added and deleted fields, created queries, established and reset primary
keys, created relationships (all types), joined the tables and queries on
different fields, everything I can possible think of but to no avail. I
cannot get the database to recognize one Widget – numerous options.

What I want the database to do is display all the options in a subform
allowing me to select an option using a SELECT (yes/no) field. The option(s)
would then be applicable to that unique Widget number. When I finish with
Widget 001 I select the next Widget number, 002, and repeat the process. Thus
each of my widgets is customized with its related options. I can then print
out a report by filtering on the YES field (haven’t got that far yet, first I
need to get this thing to work).

I am hoping somebody in this list can tell me where I am going wrong. It is
very frustrating yet seems so simple. There has to be a simple solution but
for three days it has eluded me. Assistance please.

 

[tina]

let's forget queries and forms for a minute, and examine your tables
structure. you should have a tblWidgets, with the unqiue serial number as
primary key (as you described); i'll call the pk field "WidgetID". the
fields in this table describe only a widget, not its' options. you should
also have a tblOptions, with the unique option number as primary key (i
won't go into the whole "option series" issue at this point; it's not
germaine to your stated problem); i'll call the pk field "OptionID". the
fields in tblOptions describe only an option, not anything about widgets.

one widget may have many options, AND one option may be added to many
widgets. so the two tables have a many-to-many relationship. resolve this by
adding a "linking" or "join" table, as

tblWidgetOptions
WidgetID (foreign key from tblWidgets)
OptionID (foreign key from tblOptions)
<you can use those two fields together as the table's primary key, or you
can add another field - probably Autonumber data type - to serve as a
surrogate primary key for the table.>

if one widget has 5 options, there are five records for that widget in
tblWidgetOptions, one for each option. if another widget has 17 options,
there are 17 records... you get the idea. a standard form setup for data
entry would be: main form bound to tblWidgets, subform bound to
tblWidgetOptions, with a combo box control in the subform with its'
RowSource set to tblOptions so that you see the options in the droplist. or
you could reverse that: main form bound to tblOptions, subform bound to
tblWidgetOptions, with a combo box control in the subform with its'
RowSource set to tblWidgets so that you see the widgets in the droplist. you
can also do a somewhat more complicated (for the developer, not the user)
setup that allows selecting a widget from tblWidgets in the main form,
displaying all the options from tblOptions in a subform with a checkbox next
to each, and then appending the checkmarked options into tblWidgetOptions.

hth

 

[Ed Warren]

Try thinking in 'realtional' terms.
Each option can be the member of many sets
Each set contains many options
Each widget has one and only one SET of options (This is an assumption from
your discussion, if not true then Each Widget is related to many Options,
and each option is related to many widgets. This will require an additional
table)

Each set of options can be related to many widgets

So we have:
Options Many to Many Sets
Widgets M: 1 Sets

Tables we need
Options (OptionID as Primary Key, other stuff about the options)
Options_Sets (OptionID, SetID)
Sets (SetID as Primary Key, other stuff about the set)
Widgets (WidgetID as PK, SetID as ForeignKey, other stuff about the widget)

Relationships
Options --> Options_Sets <--Sets
Sets --> Widgets

Forms
A detailed form for the widgets. (one widget shown, not a group of rows)
A form for the Sets
A form for the Option_Sets1 (OptionID is hidden and SetID is a dropdownlist
populated from the Sets Table)
A form for the Option_Sets2 (SetID is hidden and OptionID is a dropdownlist
populated from the Options Table)
A form for the Options
Add Option_Sets1 as a subform to the Options form
Add Option_Sets2 as a subform to the Sets form.

Now you can see/add/edit the options in a set and the sets an option belongs
to.

At last we work with the widgets Form
We can add (one widget, one set) a field to hold the SetID and from there we
can get the options
or
We can add another M:M relationship between widgets and options (another
table similar to the Sets_Options table) and use a subform here

Hope this helps

You will have to do some data transfer, conversion to get the data into
these tables, but that can be done using queries.

Ed Warren.

 

[thebigmac via AccessMonster.com]

Tina, thanks for the input but let me clarify somethings so that I get this
straight in my head.

Each Widget is unique but has various attributes associated with it. For
example, Widge 001 may be 74 feet in length, without a top (an open
convertable for lack of a better term) and a specific color, plus a couple of
extra attributes. Widget 002 may be 25 feet in length, have a hard top (not a
convertable) and a specfic color. You get the gist. These are the standard
attributes for the Widgets. They are not considered options but rather apply
to the basic model. Hence my Widget table is designed to support these basic
attributes.

Now the customer has the choice of adding OPTIONS to the purchase, such as a
wide screen TV, trash compactor, gold plated mirrors, and so forth. The
customer has a choice of over 1,300 options (which is why we need to keep
track of them) specific to his Widget. The OPTIONS table contains only
options, nothing else.

I broke down the original options set into small files with the idea of using
a Tabbed form to bring up each specific set of options. (I may have to
scratch that idea for now and use the one single Options table converted from
the Excel spreadsheet.) I would still like to do that but that concept is
not set in stone.

Now my question, does this change the concept of the solution you sent? If no
then there is not a problem, if yes then I am right back to where I started.

True, one Widget with many options, many options applies to one Widget.

When I get to work I will apply your solution and see how it works. I really
appreciate the input from all, its more than what I had hopefully will solve
my de-lemon-na.

 

[tina]

comments inline.

Tina, thanks for the input but let me clarify somethings so that I get this
straight in my head.

Each Widget is unique but has various attributes associated with it. For
example, Widge 001 may be 74 feet in length, without a top (an open
convertable for lack of a better term) and a specific color, plus a couple of
extra attributes. Widget 002 may be 25 feet in length, have a hard top (not a
convertable) and a specfic color. You get the gist. These are the standard
attributes for the Widgets. They are not considered options but rather apply
to the basic model. Hence my Widget table is designed to support these basic
attributes.

okay, sounds like those attributes are intrinsic to the widget (just like
gender, height, weight, and eye color are intrinsic to a person). as long as
you are not entering a series of the same values in separate fields - naming
fields [Something1], [Something2], [Something3], is a tip-off - you're okay.

Now the customer has the choice of adding OPTIONS to the purchase, such as a
wide screen TV, trash compactor, gold plated mirrors, and so forth. The
customer has a choice of over 1,300 options (which is why we need to keep
track of them) specific to his Widget. The OPTIONS table contains only
options, nothing else.
okay.


I broke down the original options set into small files with the idea of using
a Tabbed form to bring up each specific set of options. (I may have to
scratch that idea for now and use the one single Options table converted from
the Excel spreadsheet.) I would still like to do that but that concept is
not set in stone.

don't confuse data display with data storage. your tables should be built to
data normaliazation standards; how you choose to display the data in forms
is almost unlimited except by your imagination (and skill level in Access
development).

Now my question, does this change the concept of the solution you sent? If no
then there is not a problem, if yes then I am right back to where I

started.

no, my suggestion stands. recommend you read up on data normalization
principles, though, because you need to make sure that your tables are
properly structured (including that Options table). see
http://home.att.net/~california.db/tips.html#aTip1 for more information.

True, one Widget with many options, many options applies to one Widget.

that's not what i said. to determine the relationship between two entities,
you need to define how one record in each table is related to the other
table. so "*one widget* may have many options, AND *one option* may be added
to many
widgets."

by contrast, your statement "one Widget with many options" defines how one
widget record is related to the options table, and then restates that same
relationship "many options applies to one Widget" - just backward.

it may seem that i'm picking nits, but i'm not. defining entity
relationships is fundamental to building correctly structured tables in a
relational database. it's the hardest part of database development to learn,
and the part that almost everyone struggles with at first - and if you don't
do it right, you'll have problem after problem with the rest of the database
development process.

When I get to work I will apply your solution and see how it works. I really
appreciate the input from all, its more than what I had hopefully will solve
my de-lemon-na.

good luck with your project, and don't hesitate to post back to the
newsgroups as needed; we're always here.