Cost vs quote design question

[Graeme at Raptup]

Hi,
I seem to be having some problems designing these days, mental block or
something.
I have the following scenario for a small painting/building business;
Each Job can have many Quotes.
Quotes are based on prices from a number of suppliers and each quote can
include many products.
I have got the following table relationships:
1 Job => Many Quote
1 Quote => Many For 1 <= Product
1 Product => Supplier

1 Job => Many Cost
1 Cost => Many Product

I am now thinking that I don't need the Cost table.
In the Product table each item has a CostPrice and a DateofPrice.

What we had in the Cost table was a PriceCharged field - this would help us
identify if the supplier was charging differently to the cost price.
Similarly in the For table we have a Discount field so that the CostPrice
could be discounted. (Note: we would have a calculated field to generate a
margin adjusted quote price)

I would welcome any suggestions as to how I should be structuring this!

Thanks,

Graeme

 

[Roger Carlson]

Actually, you DO need a Cost table. But the relationship you've established
is wrong. Each Product can have many Costs (over time). The CostPrice and
a DateofPrice should be in the Cost table as well as the Product ID as a
foreign key.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Graeme at Raptup]

Hi Roger,
thanks for replying.
I have subsequently changed my structure.
I now have:
1 Job => Many Quote
1 Quote => Many QuoteDetails
1 Product => Many QuoteDetails
1 Supplier => Many Product
1 Quote => Many CostDetails
1 Product => Many CostDetails

I hear where you are coming from with the Price/Date, but rather than
entering the price each time we want to be able to use a pre-entered price.
For this reason I thought that I could have the price and PriceDate in the
Product table. This way we could select the most recent price each time.
(There are only 3 or 4 suppliers)

Or does this still not make good sense?

Thanks,

Graeme

 

[Roger Carlson]

If each product can have multiple costs, then the costs should be in their
own table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Graeme at Raptup]

Hi Roger,
not sure if this thread is still active, but here goes.

I have changed relationships to look like this;

1 Client => Many Job
1 Job => Many Quote
1 Job => Many Payment
1 Quote => Many QuoteDetails (Includes the cost price and a date field)
1 Quote => Many CostDetails
1 Pricing => Many QuoteDetails
1 Pricing => Many CostDetails (The cost elements of the project will differ
to the quote)
1 Product => Many Pricing
1 Supplier => Many Pricing

It looks right but I battle to put forms together for a job quote with all
the products and their prices.
Am I on the right track?

Cheers,

Graeme