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!
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!