Format - Quote portion of DB (1 Viewer)

[Shaunk23]

I am adding a section to my database.. rather then creating a separate DB. This will be used for quotations to customers... While my company does have some common customers or repeat customers we have many scattered as well.. I am trying to think about the best way to set this up.... I had thought about making a table for "Customers" when a quote was done.. if another quote existed in the past for that client then it would pull his info in, else it would create it... That being said i feel like that may be a ton of data to store.. Many one time clients.

Basically i will have the customer reach out to my company for a quote.. That client could want a variety of different size " sea containers". Sometimes they need 1 x 20 foot container, sometime's they need 1 x 40 AND 1 x 20 etc.. I would also have a pricing segment.. where employees can put in the COST & SELL for each part of the quote.. Trucking / Ocean Freight / Air Freight / documentation Fee / customs etc.

Any recommendations on this setup?

Maybe
Customer Main > 1 to 8 > Actual Quote with Details > 1 to 8 > Pricing segment

I would be ok with putting ONE single table for customer info & the quote info but i think that would be to much data in one table correct?

 

[NigelShaw]

Hi,

1 table would be ok for the customers with a separate table for quotes. Think of it like 1 customer can have many quotes so a 1 to many relationship would be required. Then maybe, another table for quote revisions and, because 1 quote can have many revisions, a 1 to Many relationship there too.

i only have 1 table that contains names & numbers. Suppliers & Customers are separated in a query with 'Supplier' or 'Customer' criteria filtered. This is because in the past, some of my suppliers became Customers.

i dont quite understand the 1>8 part. whats that about?

In regard to the 'adding the COST & SELL part, you would need a table to hold the product with the cost & % but i wouldnt store the sell value as that could change. I would display the sell value on screen and only save the sell value when the item is added to the quote becuase then, its a confirmed cost.

To link the products to the quote, i would have the selected product added to my quote table along with the quote number & revision in the same record. They can be taken from the form displaying the current quote number & revision number.

To invoice, query the quote and get the quote number, revision number, cost & sell values, notes, descriptions etc.

hope that helps and i'll keep an eye on this thread to see how it goes


Cheers

Nidge