none
Sort Access 2010 report by Group Total RRS feed

  • Question

  • I have an access report that provides vendor information like company name, items ordered, quantities, prices, etc.

    I have it grouped by company so that it shows everything we ordered from one supplier during a specified date range and provides the total cost we spent with that vendor.

    They want to see this report in order by total spent with each supplier. So if we bought 4 items from vendor A for a total of $100 and 2 items from vendor B for a total of $500, vendor B would be listed on the report first because we spent more money with them.

    Is this possible? If so, how can I make it happen?

    Thanks,

    Jessica

    Friday, February 23, 2018 1:47 PM

All replies

  • There is not a feature for this (sort by sub group total) within the report object itself.

    What you can do is create a stand-alone query that does this same math total by vendor.  Once you've created that query - then you join it to the record source query for this report.  The value is then part of the data set to sort on.

    Friday, February 23, 2018 1:56 PM
  • You could add a Sum column to the report query, then sort by that.

    The sum column could be calculated using the DSum function. Something like:

    CompanySum: DSum("[Quantity] * [Price]", "myQuery", "CompanyID=" & myQuery.CompanyID)


    -Tom. Microsoft Access MVP

    Friday, February 23, 2018 2:03 PM
  • I tried that based on what I found trying to search for an answer yesterday and I couldn't get it to work. 

    For one it wasn't getting the same answer for the group total as the report was getting. I couldn't figure out what I was missing. 

    For two because it is grouped on vendor with A on top it doesn't care that I want to sort it by the total.

    I'm so confused.

    Jessica

    Friday, February 23, 2018 2:06 PM
  • Maybe an example will help. Check this out: https://1drv.ms/u/s!AnmKsZFxs8_Kh6ctTLudsjM0Oh9sEQ

    This is the Northwind sample application where I modified the Order Summary query and added the CompanySum field.

    Then I used this query to create the Order Summary report. It's ugly of course, but it demonstrates that the companies are sorted by CompanySum.


    -Tom. Microsoft Access MVP

    Friday, February 23, 2018 2:36 PM
  • Ok I think I understand what you did. You added a column in the query to total the subtotal and then moved that sort above the grouping.

    I'm going to try it.

    Thanks,

    Jessica

    Friday, February 23, 2018 3:01 PM
  • Ok I copied the code you had in field one of your query and am trying to modify it to fit my query and database but I am getting an error.

    Can you break it down to show me where each item comes from? 

    For example:

    CompanySum: CCur(DSum("[Order Total]","Order Summary","[Customer ID] = " & [Orders].[Customer ID]))

    CompanySum = Field name - I used the same to make it easier to modify

    CCur = ? - I don't know what this means

    DSum = function

    [Order Total] = Field name referenced within query - Mine is the Subtotal field in my query

    "Order Summary" = ? - I don't know where this is coming from. I thought maybe the query name?

    [Customer ID] = Field name referenced within query - Mine is Supplier field in my query

    [Orders].[Customer ID] = Customer ID from Orders table - Mine is [dbo_PorHistReceipt].[Supplier]

    It looks like this:  

    CompanySum: CCur(DSum("[Subtotal]","qryRawMaterialsBought","[Supplier] = " & [dbo_PorHistReceipt].[Supplier]))

    When I run it I get an error: "The expression you entered as a query parameter produced this error: "No current record" - I click OK and another message pops up that says, "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." I click OK again and i'm back in query design.

    Thanks,

    Jessica


    • Edited by Jessicasdd Friday, February 23, 2018 3:23 PM Wasn't done typing.
    Friday, February 23, 2018 3:21 PM
  • If you use a DSum in a report, then the DSum needs a reference to the report itself as well as the field. So Try:

    DSum("[Subtotal]","qryRawMaterialsBought","[Supplier] = Reports![YOUR REPORT NAME]![Supplier]")

    Friday, February 23, 2018 4:34 PM
  • This is so frustrating.

    Now it's erring telling me it can't find the name 'Orders.Customer ID' you entered in the expression. My expression is: CompanySum: DSum("[Subtotal]","qryRawMaterialsBought","[Supplier] = [rptRawMaterialsBought]![Supplier]")

    There is no reference to 'Orders.Customer ID'.

    I deleted the field that I copied from the example you provided and typed it in myself the way you suggested above so why is it still seeing that reference when after I initially copied it I changed it right away, then deleted it completely, and retyped it myself?


    • Edited by Jessicasdd Friday, February 23, 2018 4:48 PM Wasn't done typing
    Friday, February 23, 2018 4:46 PM
  • It's:

    "[Supplier] = Reports![rptRawMaterialsBought]![Supplier]")


    Friday, February 23, 2018 5:12 PM
  • Same error. 

    The expression you entered as a query parameter produced this error: "Inventory cannot find the name 'Orders.Customer ID' you entered in the expression. 

    Again my expression is:

    CompanySum: DSum("[Subtotal]","qryRawMaterialsBought","[Supplier] = Reports![rptRawMaterialsBought]![Supplier]")

    There is no 'Orders.Customer ID' in my expression.

    Friday, February 23, 2018 5:22 PM
  • OK, then open your query in design mode and look for any criteria or any field that is attempting to read Orders.CustomerID. Apparently, your query is what is causing the problem, not the DSum expression. If you run the query, you will probably get the same message. Make sure your query runs without any message. It should simply give you the results.
    Friday, February 23, 2018 5:44 PM
  • If I delete that field from my query then it runs without any errors.

    I don't understand why it is doing this.

    Thanks,

    Jessica

    Friday, February 23, 2018 6:03 PM
  • Without access to the query, I can't tell you either, but does the Orders table have a field named CusomerID? And if it does, can you add that field to your query without error? If your report has a textbox that is bound to CustomerID, then it needs to be included in your query for the report. Otherwise, the report will look for the CustomerID field and throw an error if it does not find it. If the CustomerID field is not used at all for anything in the report, don't worry about it.
    Friday, February 23, 2018 6:30 PM
  • Oh now i'm really confused. Let me just say that i'm not a programmer for a reason. I think y'all can see that. LOL

    Ok so I deleted the field and saved my query and closed it. Reopened it and retyped the code into an empty field and clicked View in Datasheet view. I got a new error.

    The expression you entered as a query parameter produced this error: "Inventory cannot find the referenced form 'frmRawMaterialsBought''

    Now this report is run from that form. They enter the date range they want and hit the button and it runs the report whose record source is this query that I am working on. The query does reference the form in the DateReceived field. It says "Between [Forms]![frmRawMaterialsBought]![BeginDate] And [Forms]![frmRawMaterialsBought]![EndDate]"

    But it works fine without this new field.

    Friday, February 23, 2018 7:06 PM
  • What do you mean by "...retyped the code into an empty field"? Retyped what code into what empty field? If your report is opened from a form, then the form needs to be open when the report is run and the BeginDate and EndDate fields need to be entered in order for the reports query to read the [BeginDate] and [EndDate] parameters. Make sure the form remains open. Also, what is Inventory? Is that the report name?
    Friday, February 23, 2018 8:09 PM
  • Re your questions about what CCur and "Order Summary" etc. is:
    You need to use the VBA help file. Look up CCur. It's short for "Convert To Currency".
    Look up DSum. It's a function that takes 3 arguments. "Order Summary" is its second argument. That would make it the name of a table or a query.

    Better would be to ask the WHY question: Why use CCur? Would it not work without that?
    A: You could experiment with taking it out yourself. Without CCur CompanySum is treated as Text when using it as a Sort for the report. This would sort values alphanumerically (you know: 10, 100, before 20, 200). CCur makes it abundantly clear it is a number of subtype currency and it will sort numerically (10, 20, 100, 200).

    -Tom. Microsoft Access MVP


    Saturday, February 24, 2018 3:00 AM
  • The code you provided modified to fit my database - CompanySum: DSum("[Subtotal]","qryRawMaterialsBought","[Supplier] = [Reports]![rptRawMaterialsBought]![Supplier]")

    Empty field = A blank field in the query. 

    When I click view as datasheet without the form open it asks for the data range and I enter it.

    Inventory is the name of the database.

    So I opened the form and entered the dates and tried to run it and it erred saying it couldn't find the report. So I opened the report in design mode so it was there and reran it and got the error, "Unknown" and then, "ODBC-call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Each GROUP BY expression must contain at least one column that is not an outer reference. (#164)

    Thanks,

    Jessica

    Monday, February 26, 2018 1:34 PM
  • I removed CCur because it doesn't have to be in currency. I just need it to calculate correctly. I removed it after I looked it up to see what it meant. 

    I have the DSum function page from Microsoft open and have been trying to understand it but it's hard to when you can't make it work. - https://support.office.com/en-us/article/dsum-function-08f8450e-3bf6-45e2-936f-386056e61a32

    Thanks,

    Jessica

    Monday, February 26, 2018 1:37 PM
    1. There should be no "blank" fields in a query. I don't know how you included one.
    2. When you open the input form in regular Form View and enter the two dates, how is the report opened? Is there a command button that opens it when clicked or is it opened by some other method? Please provide us with the VBA code line that opens the report.
    3. You stated that you opened the report in Design mode and "reran it". Do you mean you opened the report in Report View or Print Preview?
    Monday, February 26, 2018 5:30 PM
  • Every query in design mode has "blank" fields. Those are the fields that are sitting there blank when you first start designing your query. As you make selections from the tables you are using those selections fill into those blank fields. That's what i am talking about. Not some special blank field. I entered the code that was provided above into the last blank/empty field in the query design. 

    The report is opened when you click the button on the form. It runs a macro that Opens the report in Print Preview.

    I was getting an error when running the query that was referencing the report so i opened the report in design mode and reran the query.

    Thanks,

    Jessica

    Monday, February 26, 2018 6:30 PM
  • OK, now I understand. You entered CompanySum: DSum("[Subtotal]","qryRawMaterialsBought","[Supplier] = [Reports]![rptRawMaterialsBought]![Supplier]") into the last field. All this time I thought you were using the DSum formula as a Control Source in a text box on the report.

    If you use a formula in a query, then you will need to open the query in design mode and click the 'Totals' button under the Design Tab. Next, go to the CompanySum formula field and select 'Expression' in the Total: row Group By dropdown box. This tells ACCESS that the query field is an expression. So now when you run the query after inputting the dates from your form, one of the reports query fields will be a formula named CompanySum. Next, open the report in design mode and make sure there is a text box on the report with CompanySum as a Control Source if you don't have that already. You will also need to type CompanySum in the text box Name propery. So both the text box Name and Control Source are CompanySum.

    Monday, February 26, 2018 7:28 PM
  • I'm sorry. I gave up. It was too confusing.

    I removed the code from the last field and saved it. The totals were already on and all fields were set to Group By. I had tried the code with Expression but it wasn't returning anything. I'd get a blank column where the code was entered. So I just deleted it and tried something else.

    I created a second select query and used the first select query as the source, instead of a table. I pulled the Supplier and the subtotal columns and turned on totals. I have supplier group by and subtotal sum.

    I then created a third select query and used the first and second queries and the source. I joined supplier from query 1 to supplier in query 2 to only include lines where they are equal. This gave me everything I needed in one query and I used that third query as the record source for the report.

    It works.

    Thanks,

    Jessica

    Monday, February 26, 2018 8:41 PM
  • Hi Jessicasdd,

    It seems your issue has been resolved, I would suggest you mark the helpful reply as answer to close this thread.

    Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 8:35 AM