Quotes to Orders (1 Viewer)

[Fizzio]

Hi folks. I'm a bit rusty with not using Access for a bit so I need your guidance. A friend of mine has caught wind that 'I've done a bit' and asked me to produce a Db to simplify his sales and manufacturing part of the business. Unfortunately, working in the NHS leaves me with no experience in this type of Db design. However I digress....

What I want to know is the best structure for Quotes to Orders. Essentially he wants to audit all quotes, which ones go on to orders etc...

I have 2 thoughts
1. Single Order Table with a flag to identify whether it is an quote or confirmed order and when an order is placed, copy the quote information into a new record and 'lock' the quote details.

2. Separate Quote Details and Order Details tables which have almost identical info (hence violating 1NF) and copying the quote data into the Order Data.

The reason for the audit as often, the actual order differs from the quote and they need to keep a record of the quotes and orders placed for eah customer.

I am swaying towards option 2 but I'd appreciate your expert opinion (for once, I actually include Rich in that statement )

Cheers Folks..

 

[dcx693]

OOOHH. It's Fizzio !

What I would do is keep separate Orders and Quotes tables. I'm not basing that answer on any particular database design rules. Intuitively, a quote is a quote. It might very well move on to being an actual order, but until then, it's just a quote.

When it does become an actual order, the details may or may not be exactly the same. If the details are the same, does it violate 1NF to have both quote and order details? Maybe, but what if the quote and order details are different? This set up also sounds like it's easier to maintain than "locking" the quote details at some point. You'd still have both the order and quote details. Better to have a calculation that tells you whether the order and quote details were the same.

 

[Fizzio]

Yes, Fiz is back (well for a while anyway!)

Cheers dcx, that is the answer I was hoping for as I was slanting towards that anyway.
Thanks for replying at the weekend. No doubt by Monday, this post will have disappeared from the front page and no one will notice I sneaked in!

heh heh

 

[Rich]

Yes, Fiz is back (well for a while anyway!)

No doubt by Monday, this post will have disappeared from the front page and no one will notice I sneaked in!

Hopefully

 

[Rich]

Some people, just push off without so much as a bye or leave, then come back and push off again

I'm not sure I'd undertake this project as a favour for a mate, before you know it this will become a full time project.
However, some thoughts, why does your mate want to keep a history of quotes, how many orders are derived from quotes, do a consistent number of customers have repeated quotes, how many quotes result in firm orders, what's the time limit for quotes, is the bulk of the business derived from orders without quotes, does your mate want to change pricing policy based on issued quotes versus firm orders?

Hopefully we'll be given sufficient info to provide guidance

 

[Fizzio]

Hey Rich, nice to see you again as well

After 'agreeing' to do this - it soon became apparent what a can of worms I'd let myself in for. Luckily, he also sees this and has agreed to 'compensate' me for the time I take on this. Thanks for your interest and I'll try to answer your questions.

why does your mate want to keep a history of quotes

Primarily in case of dispute where he has a record of what was actually quoted. Apparently, some of his customers orders are 'as per quote' and hence the quote (and subsequent order) are the record of the order also.

how many orders are derived from quotes / how many quotes result in firm orders

This is apparently very inconsistent. The business makes panels for the construction industry and there is a bit of competition so there is not a ballpark figure for quotes converting to orders. He hopes that that is something the Db can provide.

do a consistent number of customers have repeated quotes

Yes and no. There are several repeat customers but also many one-off customers (just out of interest, why do you ask this?)

what's the time limit for quotes

Don't know that one - haven't asked.

is the bulk of the business derived from orders without quotes

No, all orders come from previous quotes - there does not seem to be any orders without quotes (which I'm glad of as this could create a nightmare structural design )

does your mate want to change pricing policy based on issued quotes versus firm orders?

That is a very interesting question as I'm fairly sure that that has not ever crossed his mind. I would like to know a little more detail on what this may mean - as I could not advise him on this with my limited knowledge.

Cheers Rich, If I leave again for a while, I'll remember to say bye this time........but not quite yet!!!

 

[___]

Some people, just push off without so much as a bye or leave, then come back and push off again

I'm not sure I'd undertake this project as a favour for a mate, before you know it this will become a full time project.
However, some thoughts, why does your mate want to keep a history of quotes, how many orders are derived from quotes, do a consistent number of customers have repeated quotes, how many quotes result in firm orders, what's the time limit for quotes, is the bulk of the business derived from orders without quotes, does your mate want to change pricing policy based on issued quotes versus firm orders?

Hopefully we'll be given sufficient info to provide guidance

Rich, That has to be your longest reply ever!

 

[Rich]

Rich, That has to be your longest reply ever!

Isn't

 

[Hroy]

re quotes + orders

Hi I am a newbie to this kind of event, so hope it is alright to join the thread.

I have a similar problem in a db I am building for the small business I work for. I wanted a way of saving the original quote (in report form) so that there was a record of the original quote and prices. This seems to be impossible as I cannot save a subform or subreport in Word. I could save a snapshot as a pdf, but my boss would like to be able to edit in a word processing package.

Still was interested to see your question as perhaps I should be thinking about using tables earlier on but wuld that mean starting from scratch for each order based on a quote, or indeed any re-order? Would be interested to hear how you get on.

Sorry- no answer just communing
H

 

[Mile-O]

Luckily, he also sees this and has agreed to 'compensate' me for the time I take on this.

You'll get a lollipop and a hollow thanks.

 

[Fizzio]

You'll get a lollipop and a hollow thanks.

Yep, that is always a worry but I trust him to be fair for the work I do. Anyway, it gives me an excuse to come in here again for a bit banter - surely that can be compensation enough??

 

[Mile-O]

Anyway, it gives me an excuse to come in here again for a bit banter - surely that can be compensation enough??

..but then, surely we deserve some form of compensation for that!

 

[Fizzio]

Still was interested to see your question as perhaps I should be thinking about using tables earlier on but wuld that mean starting from scratch for each order based on a quote, or indeed any re-order? Would be interested to hear how you get on.

I'll try to remember to send you the basic structure I come up with - remind me on in a couple of weeks.

 

[Fizzio]

..but then, surely we deserve some form of compensation for that!

Fair point - how about that lollipop??

 

[Mile-O]

You can use the calendar to set reminders.

 

[Mile-O]

Fair point - how about that lollipop??

Only if it's a Chupa Chups - the one that's wrapper was designed by Salvador Dali.

Just random thinking; I wonder if Chupa Chups translates as Suck Suck as chupacapras is Spanish for 'goat sucker'....

 

[Fizzio]

I wonder if Chupa Chups translates as Suck Suck as chupacapras is Spanish for 'goat sucker'....

I do not even want to contemplate how you know that

Cheers for the calendar tip...

 

[Mile-O]

I do not even want to contemplate how you know that

South American paranoia explaining away caprine carcasses drained of blood with no explanation.

 

[dcx693]

Hey - can I get some compensation? I need to compensate for things.

 

[Fizzio]

Hey - can I get some compensation? I need to compensate for things.

Is that what the sports car is for?

Rich, do you have any more help that you can offer? I appreciate your input.