Numbering versions of revised records

ML!

Registered User.
Local time
Yesterday, 20:02
Joined
May 13, 2010
Messages
83
Here's the scenario...I am working on an application that will provide complex customer quotes. A quote is drafted and sent to the customer. They ask for a revision and the original quote is revised, but we want to keep the original one intact (just in case the customer changes their mind and we have to go back to it). Now this might happen multiple times until the quote is accepted.

Quotes are delivered via a report in a pdf format. We'd like each to relate to the original. Say the original quote number is 1001-1. We'll have to bring up that record and make revisions to it and save the new with the revised datat. The first revision should be something like 1001-2, then 1001-3 etc to identify it as a revision of the original. We want to flag the accepted quote too which I guess I'll just do in a separate field. In fact, I'll probably track each version with a status like 'draft' and 'final' There should only be one final and that one we might want to track as 'won' or 'lost'. Maybe 2 yes/no fields?

I know there's a few ways to do this but I'd like some advice on what the best practice might be to track and manage the revised records. Maybe someone can get me pointed in the right direction...

Relevant tables and fields so far include the following:

tblQuotes
QuoteID (PK)
QuoteNumber
CustomerID (FK tblCustomers)
QuoteDate
Status (draft/final?)
Success (won/lost?)

tblDetails
DetailID (PK)
QuoteID (FK tblQuotes)
ProductID (FK tblProduct)
Qty
Price

TIA Access Gurus!
 
Not an expert but consider joint primary keys with QuoteNumber as the primary Key and RevisionNumber as the next field also a Primary Key.

This would mean the first quote is Quote 1 Revision 1

The last quote is the last record in the sort order of joint primary keys.

You would also have QuoteDate field which would provide sorting assistance.

You could have a Revise Quote Form that asks for the Quote to be Revised and then displayed the data but didn't update that quote, instead, created a new quote with the old quotes ID included.
This would avoid re entering old data, just editing, adding and deleting.
 
Thanks Bill...would it be a problem on the composite key, that you'd have to revise the Quote number manually (or I guess autoincrement via code)

The other thing I thought is maybe the quote table should be broken up and there should be a master quote table and a child revision table with one to many relationship which I guess would accomplish the same thing and follow normalization standards - however, it might be more complicated with respect to relationships.

I absolutely agree on the revise quote form - the number of items in a quote can be in the dozens. I'm definitely planning that once I figure out the best numbering scheme.
 
Shouldn't need two tables for Quotes and Revised Quotes.

You should consider a TblQuoteHeader and TblQuoteDetail.
First to hold quote numbers, Cust Num, DateQuoeted etc. 2nd to hold the Quote Records - QuoteID, ProductID, QuoteQty, QuotePrice, maybe QuoteLineNum if you want some consistency through the revisions of what products are where on your quote.

Quote value is calculated for line and total so long as each record in your TblQuoteDetail has a QuoteID, product, qty and price then you have a record of all past quotes.
 
Hmmm...that's an interesting approach. I think I like it. Just to clarify...so you're saying QuoteID would be the Primary Key in tblQuoteHeader and would link to the child table tblQuoteDetail?
 
Wait, I made an error - what I've described is what I was already doing. What you mean is putting the revision number in the detail table - right?
 
Yes,

All of your Quote Line Records would be stored in your QuoteDetail table. Doesn't matter if there are 100's of thousands of them.

This is the main issue of using a database.

Your shopping list doesn't need to be made up as 3 lists for three stores. A database just needs some way to know which item comes from which store - QuoteID - and off you go. As you enter each store, the database will just give the items for that store. Bit simple but this is how it is done.

Side issue - Assuming you have resolved your QuoteID issue - You can have a field that is not your Primary Key but just a Number that is called your Quote Number. You would have QuoteID and QuoteRevisionNum as your combined Primary keys and a third field is QuoteNumber, or not even a field, just in a query that produces the report (quote). Quote Number is the result of QuoteID and QuoteRevisionNum and uses a concatenated value along with the next number so Quote Number would be [QuoteID]&a number derived from the sort order of your [QuoteRevisionNum].

If there was absolutely no way two revisions would be done in one day then this could be resolved with just your QuoteID and QuoteDate to get the Quote Number displayed.
You could hold this value just in case something did cause a @%^*! in the future and your customer is holding a document with Quote 1245/12 and you hold a different report for for this number. Say a Revision is deleted then this would mess up your dynamic numbers.

Sorry to ramble but sometimes as you solve one issue another one appears.
 
Wait, I made an error - what I've described is what I was already doing. What you mean is putting the revision number in the detail table - right?

Not really, No such thing as Revision Number - just mirrors for your customer.

Each quote is just like any other except... where it is based on a previous quote, the new QuoteHeader will hold the old QuoteID it is based on. 123/2 is based on 123/1. 135/12 is based on 135/11. The fact that you call it a revision doesn't effect the QuoteDetail Table records, they just know what quote they belong to 135/12.
 
OK I think I got it. You're being such a huge help - here's another wrinkle. We want the flexibility to do a line item discount which we can just manage by changing the line price for that item. However, to win the quote, we might have to apply a 20% discount to the total. Would you put the global discount in tblQuoteHeader?

Maybe it would be better to apply global discounts to the second half of the composite key using a seperate table. Because 135/11 might be a 10% discount, and 135/12 might be 20%. Actually I think I answered my own question but would still like to know your thoughts if you're so inclined :)
 
Line discount as a field in the QuoteDetail Table and Overall Discount in the QuoteHeader Table.
Line price total price is not a field in your QuoteDetail table but calculated.

Of course your "profit" by item or by quote will have to include the header disc. But not an issue so far.
 
Last edited:
I do understand that results of calcs are not in stored in tables and are only displayed on the form/report.

There still might be followups as I continue to work on this but I wanted you to know you've been an awesome help Bill. I appreciate it.
 

Users who are viewing this thread

Back
Top Bottom