A
Andy Sparks via AccessMonster.com
I am creating a database application to quote products in our sales dept. at
a made-to-order manufacturing facitilty. We manufacture three different
product groups that each have unique attributes. Since these products are
made to order, there are literally hundreds of thousands of different
permutations of product characteristics that prevent using a simple product
code to define each product. I have created one table called TBLQUOTES that
will store individual quote information including customer's name, contact
name, date of the quote, delivery time estimate, quote ID, revision level,
etc. Then I have created another table TBLQUOTEDETAILS that contains data on
the invidual product that will be quoted to the customer. This product may be
one of three different products; let's say PROD1, PROD2, PROD3. The table
TBLQUOTES is related to the table TBLQUOTEDETAILS by the QUOTEID in a one-to-
many relationship. Each quote may have multiple line items of unique products
all from one product group type, PROD1, PROD2, PROD3. Because each of the
three have different attributes, I want to avoid having one table called
QUOTEDETAILS that has some of fields populated but many others with null
values. Is there a way to efficiently design the application with maybe three
different quote detail tables that would be called based on a selection of
either PROD1, PROD2, or PROD3? Then the table TBLQUOTES would be linked to
one of three different TBLQUOTEDETAIL tables based on the product type
desired.
My original intention is to have the main quote information entered through a
form that is based on TBLQUOTES and the specific product information entered
in a subform that is linked to the other form.
Am I making any sense?
a made-to-order manufacturing facitilty. We manufacture three different
product groups that each have unique attributes. Since these products are
made to order, there are literally hundreds of thousands of different
permutations of product characteristics that prevent using a simple product
code to define each product. I have created one table called TBLQUOTES that
will store individual quote information including customer's name, contact
name, date of the quote, delivery time estimate, quote ID, revision level,
etc. Then I have created another table TBLQUOTEDETAILS that contains data on
the invidual product that will be quoted to the customer. This product may be
one of three different products; let's say PROD1, PROD2, PROD3. The table
TBLQUOTES is related to the table TBLQUOTEDETAILS by the QUOTEID in a one-to-
many relationship. Each quote may have multiple line items of unique products
all from one product group type, PROD1, PROD2, PROD3. Because each of the
three have different attributes, I want to avoid having one table called
QUOTEDETAILS that has some of fields populated but many others with null
values. Is there a way to efficiently design the application with maybe three
different quote detail tables that would be called based on a selection of
either PROD1, PROD2, or PROD3? Then the table TBLQUOTES would be linked to
one of three different TBLQUOTEDETAIL tables based on the product type
desired.
My original intention is to have the main quote information entered through a
form that is based on TBLQUOTES and the specific product information entered
in a subform that is linked to the other form.
Am I making any sense?