I have a Quotes Database which is almost complete, with only one small glitch.
I am using MS Access 2003 SP2
Database Use: We use this database to create, view, print, and track quotes for our customers. For each quote there is a field [Won Order]. This field is a Yes/No check box which is "turned on" if the customer places an order based on the quote.
Fields of Interest:
Issue: On an internal report, I would like to display the total number of quotes versus the total number of quotes that have become orders. I would like to group this set of data by CustomerOrganization and CustomerContactName.
Current Situation: I have created two separate queries, "TotalQuotesBySection" which totals the number of quotes by CustomerContactName and CustomerOrganization by counting the field [QuoteNumber] where it is not null.
"TotalQuotesToOrder" which totals the number of quotes turned to orders grouped by CustomerContactName and CustomerOrganization. This is accomplished by counting the [QuoteNumber] field WHERE [WonOrder] equals Yes.
These two queries work fine separately, however in a third query I've combined the two so I can use the data in a report is where it all breaks down. The problem arises in that there are certain customers who have several quotes, but no orders have arisen.
I understand WHY it's happening, I just do not know how to solve it. When I combine the two queries, based on the common fields CustomerContactName and CustomerOrganization, the third query (hence the report) is ONLY returning the data where there's a match between total quotes AND total orders.
John Doe from Electric has 4 quotes and 2 orders
Jane Doe from Electric has 3 quotes and 1 order
Joe Schmedlap from HVAC has 5 quotes and 0 orders
Joseph Snuffy from HVAC has 3 quotes and 1 order
The report displays:
John Doe 4 Quotes 2 Orders 50%
Jane Doe 3 Quotes 1 Order 33.3%
Joseph Snuffy 3 Quotes 1 Order 33.3%
The report omits Joe Schmedlap. However, it is useful for us to know which customers consistently have few to no orders resulting from their quotes (helps to prioritize work load)
What I would like to be able to do is show ALL quotes versus orders, even when certain customers do not have any orders stemming from their quotes.
I know this was kind of wordy, but just wanted to give all available information.