Splitting up Tables

[Mailmanny]

I am building a database to Price out Furniture that we build in a small
factory. about 3/4 of what we build is built to order. the database treats
each peice of furniture like an invoice where the designer can add parts from
a list. currently it works well but it is set up to price our lineup of stock
designs.

what i would like to do is basically copy the two tables that hold the
information about the individual peices and alter them slightly to be used
for individual customers pricing. but i need it to keep the refrences to the
parts table so if the part prices are updated they will trickle down to the
stock furniture pricing and the custom furniture as well.

please can anyone halp me with this task.

 

[Jeff Boyce]

It all starts with the data.

Please describe a bit more specifically your current table structure. Also,
providing some sample data will help us understand what you are working
with...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mailmanny]

I bacically have three affected tables.
Parts, Products and Product Details
Produts has fields such as size, description and wood type for our furniture

Parts holds the information for furniture parts as well as their prices.
Like doors, drawers, switches, mirrors, and shelves.

Product details has a quantity, a partID and a ProductID.

This works great and when we update the price for one drawer then the report
for a file cabinet is automatically updated.

what i want to do is create another table that holds information on a
specific customers peice of furniture. using a table like :custom-products
and custom-product details. but it will still get is't pricing information
from the parts table containing all of the parts and prices. is there a fast
way to do this?

maybee this should be another thread but i would like to also be able to
select a preexisting field in the table that holds all of the information for
our stock products and copy it into this new custom table so the user can
change things like color or wood type that affect the price without affecting
the original data.

 

[Jeff Boyce]

This may be one of those (fairly rare) situations in which it makes sense to
store a "calculated" value.

If you have a customer who buys a piece of furniture last year, do you
REALLY want his/her purchase to reflect the changes to parts prices you made
last month? I would think you'd want to know how much s/he paid for that
particular piece THEN, and not have price changes ripple into "historical
records".

Regards

Jeff Boyce
Microsoft Office/Access MVP