Multiple Options Per Items in Database

[bassmanfranc]

i am working with an order entry database and need to price out a product
that could possibly have as many as 75 options, each with an associated cost
that would increase a base unit cost.

I was wondering what would be the best way to go about setting up that
environment in access and how would the order entry form be best laid out?

I need to have the order record, show and archive the base unit as well as
the options chosen along with the final cost of the unit.

I was wondering if I needed to have a separate table for the base units and
then one for the options or what would work the best.

I am working on my first database development project.

 

[vbasean]

Orders have many Products?
Products have many Options

so Orders table is related to many Products
and Products table is related to many Options

On a form you have two nested sub forms
form for Order
sub form for Products
with a sub form in Products for Options

if you're trying to keep track of Products that always have the same Options
you'll have to concider 'time in point' data, that is, data that pertains to
Product being sold and not the current Product and it's Price.

if that's the case, you would need tables for the current Products and their
current Optioins with their current prices WHERE these values get COPIED to
the Product being sold and the Options cost at time of sale.

So...

tbl_Products (for current Products)
linked to
tbl_Options (for current Options and cost)

tbl_CustomerProducts (for Products sold to a customer)
linked to
tbl_CustomerProductOptions (for the options in each product sold to the
Customer)

VBA in your Order form can automate pasting the current product/option
values as they are added. You could even automate adding all the current
Options to a Product at sale via vba.