Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Counting orders (Access 2003/SP 1)

    I think there's a simple answer to this problem, but I must have too simple a mind to come up with it. I have a query that I use for a report and it has everything I need except I need to have a sum or count of orders by state. See the attachments. The "mrpt Orders by State by Month" query does what I need (I was experimenting outside of my actual query) but how do I get it integrated into the actual query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting orders (Access 2003/SP 1)

    Create a third query based on the two you have, joined on State. Use this new query as record source for your report.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting orders (Access 2003/SP 1)

    OK. I think I understand that the third query needs to be a Totals Query? I did that and I think I have it almost figured out. My only problem is, I need only a total number of orders for each state. When I did what you suggested, I got that, but if there is more than 1 category, it counts the state twice. To clarify that, look at the attachment. KS has 2 different categories of products, but all I need is the actual 5 records on the report. Will that not add 10 records for Kansas on the report instead of 5?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting orders (Access 2003/SP 1)

    No, the third query does not need to be a Totals query. Since mrptUSA Non Members by State is grouped by Category and State, and mrptOrders by State by Month is grouped only by State, the third query will repeat the count by state for each category. That shouldn't be a problem. But if you change it to a Totals query, those counts will be added together, causing incorrect results.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting orders (Access 2003/SP 1)

    <P ID="edit" class=small>(Edited by 79schultz on 09-Jun-06 16:32. Remembered some minor details.)</P>OK, I'm mired down. See attachment. Is there supposed to be a join on the states? If I don't I get weird numbers, but if I do, it works, but still gives me a duplicate on KS.

    I checked the 2 calculation queries individually and they both work like a charm.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting orders (Access 2003/SP 1)

    Your query looks OK, but I don't know what exactly you want to display in your report. Your query is grouped by Category first, then by state. Is that what you intended?

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting orders (Access 2003/SP 1)

    I haven't tried the report yet. Maybe I'm getting ahead of myself.

    I've got an appointment I have to make, I'll have to resume next week. Thanks for your help thus far.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting orders (Access 2003/SP 1)

    Before you continue with the queries next week, think about what exactly you want to accomplish with the report:
    - Should it display the categories at all?
    - If so, would you like to display info for each category, and for each state within a category, or the other way round: display info for each state, and for each category within a state?

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting orders (Access 2003/SP 1)

    All right. I finally decided to take the time to post a stripped down version for you to see.

    I 'do' need a total number per category for each state. On the 'orders', I only need a total count for the state, not 1 order for Gospels and 5 for tracts. If it's 6 orders, I need 6 orders, not broken down into categories.

    Another thing I need is to have a total sum in the report header for each category. I'm not sure how to do that. In this case, there would be a total sum of 500 Gospel's and 600 tracts.

    I hope I've made myself clear.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting orders (Access 2003/SP 1)

    I'll look at your database later, but the first thing you ought to do is to open calcqry USA Non Members in design view and drag the Category column to the right of the State column.
    Open the report in design view and put the Orders text box in the State group header instead of in the Category group header.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting orders (Access 2003/SP 1)

    For the totals by category, you can use a subform. To avoid being prompted for the date range several times, you can create a form and refer to that in the queries.

    I have attached a modified version. The form has a combo box to select a month instead of a date range.
    I cleaned up the design a bit - removed superfluous indexes, added missing relationships, simplified the queries and the report.

  12. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting orders (Access 2003/SP 1)

    That is precisely what I've been trying to come up with! Thanks a lot!

    Now my one question on that is, why do you have to start that all off of the form? When I click on the report, it asks for a parameter value instead of opening the frmSelect to select the month.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting orders (Access 2003/SP 1)

    It would be possible to have the report open the form in its On Open event, but it would mean extra coding, with extra checks. It's more convenient to open the form first - I assume that you have some kind of menu or switchboard from which the user can select the available options.

  14. #14
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting orders (Access 2003/SP 1)

    I have a switchboard, but it's not real functional, i.e. I don't have everything I need on it. I'm developing a database piecemeal as I have time. I'll probably have more questions about the switchboard later on. What I've read on the forum about switchboards, I don't know much about them.

    That's fine for me to work off the form for now. You solved a big issue for me. Once again, merci beaucoup!

  15. #15
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting orders (Access 2003/SP 1)

    I ran into another problem I can't seem to figure out.

    Why does the sum of orders add an extra number on the report? I need the number of orders per state, but then I need a total number of orders in a month.

Page 1 of 2 12 LastLast

Posting Permissions

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