Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006

    Question Unanswered: Help with Access Query / Report


    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.

    Any suggestions?

  2. #2
    Join Date
    Apr 2004
    metro Detroit
    You can do this in one query.

    Use a group by query.

    group by the customer field
    count the quote number field
    sum iif([WonOrder]=true,1,0)

  3. #3
    Join Date
    Oct 2006


    Worked Like a Charm. Figured it would be something simple, but wracked my brain for two days trying to figure it out.
    Thank you for your assistance. Woot! Finally Done....Bobby

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts