selectable options

  • Thread starter thebigmac via AccessMonster.com
  • Start date
T

thebigmac via AccessMonster.com

I'm working on a database that is based upon the following concept:

* A customer buys a product that has a unique serial number (primary key).
* Each product can be "customized" with numerous options.
* Each option has a unique number (may be a primary key) associated with it.
* There are approximately 1400 options to choose from. Each customer
"selects" how their purchase will be customized. The customer can select 1, 5,
50, 100 or more options from the list.

My challenge is to create a database where the sales rep can enter the unique
serial number and then select one or more options from the available list.

So...how do I create a form based on one serial number, then select the
options (possibly using a subform) applicable to that serial number?

I know I need a yes/no field. I DO NOT want to type in the option number
individually (I have already figured that out and it is way too time
consuming). I simply want to select the product serial number, open the
options list, select the option(s), and print a report specific to that
unique product serial number. To edit I would simply go in and add or delete
the options as necessary then reprint the report. (It is easier to scroll
through the available options and enable or disable the appropriate yes/no
field.)

It sounds simple but I am really at a loss on this one. My Access skills are
rudimentry at best (but I'm learning fast). Any help would be most
appreciated.
 
J

Jeff Boyce

I'm not sure a yes/no field is what you need/want.

The data you've described has a "one" (unique product) to "many" (option(s))
relationship. First, your tables need to reflect this - a table that holds
CustomerProduct, then a table that holds CustomerProductOption. (this
assumes you have one table for customer, one for product, and one for
option)

Next, you'll need a "main" form for putting Customer and Product together
(use combo boxes to pick a customer, pick a product). If you are likely to
have one customer, many products, you could make a Customer main form and a
Product subform.

Once you have Customer and Product connected, one way to handle options is
to use "paired listboxes". You've probably seen these using the query or
table wizards -- you know, pick one/more from the left list box, "move" them
to the right list box. You could do something similar with all available
options in the left, and those "belonging" to CustomerProduct in the right.

If you have too many options for reasonable use of a listbox, you could
consider using a combobox to select each option before "moving" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

thebigmac via AccessMonster.com

The data you've described has a "one" (unique product) to "many" (option(s))
relationship. First, your tables need to reflect this - a table that holds
CustomerProduct, then a table that holds CustomerProductOption. (this
assumes you have one table for customer, one for product, and one for
option)
Click to expand...

The customer and product/serial numbers is intergrated into one table.
Generally one customer per product (these are multimillion dollar products).
Dealers will buy more than one product and customize the item as they see fit,
but individuals who buy generally only buy one item. Hence my decision to
place customer/product into one table. If this makes a difference I can break
it out into two different tables and link them with an autonumber or some
other attribute. I am open to suggestions.
Once you have Customer and Product connected, one way to handle options is
to use "paired listboxes". You've probably seen these using the query or
table wizards -- you know, pick one/more from the left list box, "move" them
to the right list box. You could do something similar with all available
options in the left, and those "belonging" to CustomerProduct in the right.
Click to expand...

I'm not familiar with "paired listboxes", so you lost me on that one. I have
seen them I just don't know how to make them work in Access.

Thanks for the support.
 
Top