Quotations Hit Rate

[PaulMo]

Hi All


I can mark a Worksorder form as a Quotation, a unique number is allocated and the 'Quote' report is sent to the customer. On acceptance, the 'Quote' check-box is un-checked, and an Invoice with the same number is produced (actually the same document, just "Quotation" and "Tax Invoice" headings are interchanged).

In order to track Quotations that become Orders (by department), I need to mark the invoice as having been a Quote. Similarly, I need to generate a report of how many conversions happened.

QUESTION: When the Quotation check-box is ticked, I want to populate another (non-user) control with some code (permanently) in order to design a query that counts conversions by department.

Doing the queries and reports is not part of the question, just HOW TO MARK A WORKSORDER (dependant on the Quote tick-box having been, at some time, ticked) AS HAVING BEEN UPGRADED FROM A QUOTATION ...

Hope the explanation is clear ... ! Thanks in advance.

PaulMo

[ItsMe]

I would create separate tables for Quotes, Sales Orders, Customer PO's, and Invoices.

[PaulMo]

Thanks ItsMe, Perfectly logical concept ...

This system has been running since 1997, when I designed it. Since then I have upgraded twice to new Access versions. Now in Access 2007 (the customer is reticent to upgrade again!), all they've asked for is a report showing the conversion of Quotations to Orders. The Yes/No control needs to permanently mark a record as " ... having been a quote ..." because to change to an Invoice, the Y/N control is simply un-ticked.

I cannot think of a way to do this!

[orange]

You may find the question and responses in this post and
this post with picture helpful in your situation.

Good luck with your project.

[ItsMe]

...The Yes/No control needs to permanently mark a record as " ... having been a quote ..."...

Then add another Boolean to the table.

[orange]

I agree with ItsMe's comment having seen this in your post-

This system has been running since 1997, when I designed it.

.

You know the system (from design based on requirements, and years of operations) better than anyone.
It you just need an indicator to show this "entity" was once a quote(evolved from a quote), then add a boolean.

Good luck.

[PaulMo]

Thanks Guys

I'll try that in favour of changing the tick-box to a combo-box (imagine what will happen to 50 000+ existing records!). A combo-box can (easily) address more than one field, but the repercussions could be disastrous.

Thanks again

Paul (Mo)

[PaulMo]

Hi Guys

Tried what I understand about Boolean and did some searches, but can't get it to work. What I need is:

If WorkOrders.Quotation.DataType(Yes/No) is "Yes", then write "Something" in WorkOrders.QuoteHistory.DataType(Text) 'This HAS to be a non-operator input field

Then If WorkOrders.Quotation.DataType(Yes/No) is subsequently changed to "No" (when the Quotation becomes an Order) , WorkOrders.QuoteHistory.DataType(Text) must NOT change.

The I can do a Query.SortOnQuoteHistory as Source for Report!Hit_Rate_Of_Quotations_Became_Orders"; i.e. Count the total Quotations by Department versus Number of Quotes turned to orders. The new Boolean field must not appear on ant Form.

That's the best way I can explain the detail - it sounds pretty simple - but ooh!boy

Thanks again

Paul (I'm 77 years old - be gentle with me!)

[ItsMe]

QUESTION: When the Quotation check-box is ticked, I want to populate another (non-user) control with some code (permanently) in order to design a query that counts conversions by department.

If you need to determine whether or not a record was ever a quote, you will need to store that information in your database somewhere. One option might be to use an additional field/column in the same table the Quote checkbox is located. This additional column would be the same data type as the Quote checkbox. You would add a Yes/No field, aka Boolean.

What you name this additional field and the purpose of this field will, ultimately, need to be determined by you. I would guess the most intuitive thing to do would be to use a default value of 'No' and name it 'CustOrder'. With that, you would assign the value 'Yes' or 'True' or -1 to your 'CustOrder' field when a new order is created or a Quote becomes a new order, leaving the value of Quote alone.

[orange]

The idea of a boolean to indicate that this Order (or WorksOrder) was converted from a Quote goes something like this. (I'm not clear on your entities and their names and relation to one another)

---Note this is just for consideration since we do not know your set up nor repercussions as well as you-

Add a field (Boolean, Yes/No data type) eg: WasQuoteYN to your Orders table. This is simply a flag/indicator to identify - if Y, that this Order was once a Quote.
This field does not have to be exposed to Customer/user. In your process that does the conversion from Quote to Order, you update the WasQuoteYN to Y.
The issues:
-- you have 50,000+ existing records --How would you currently identify if these were converted from Quotes??? You would need to update the WasQuoteYN based on this logic.
-- If an Order was not converted from a Quote, How exactly did it get to be an Order??
--WasQuoteYN starts empty, you have to assign Y or N as appropriate.

You'll have to identify the options/methods by which an Order is created:
--what exactly is/are the process(es) to create an order/workorder?
--what are the phases/stages a "potential Order" goes through and what conditions are involved??

It would be helpful if we could see your tables and relationships and/or your table design(s).

OOOops: I see ItsMe posted while I was typing. I think the messages are similar.

[PaulMo]

Hi Guys
Give me a day or two and I'll answer questions and try your suggestions. I know Ohm's Law etc. much better than VBA etc. being trained in the Dark Science of High Frequency RF! This system took me a few months to write; and I've forgotten a lot ...
Thanks
Paul Mo

[ItsMe]

...being trained in the Dark Science of High Frequency RF! ...

I used to have a TV that had a dial for tuning UHF. Go ahead and lay the technical jargon on us.

Even if we do not understand, it may help us to help you. In other words, it may be better for us to guess about technical aspects of your business than for you to use one side of an analogy to describe a problem you are faced with.