Results 1 to 8 of 8
  1. #1
    GregShah is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Report will not sort data based on calculated expression

    My report identifies each employee and totals the number of production pieces along with the weight for the month. in the report footer, I sum up the entire output of the shop in pieces and weight.



    Using the individual and total production, i calculate the percentage of production by each employee.

    All of the data calculates properly. however, i would like the order of employee to sort with the highest percentages on top. i cannot get the report to sort this way.

    I'm not a coder and rely on the friendly prompting of Access Is there some way to get this sort completed in the report design?

    thanks

    Greg

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,520
    You need to set its "Sorting and Grouping".

    In Design view, right click the Page Header bar and select "Sorting and Grouping"
    You can then make the appropriate selections
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    GregShah is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Not quite Working Yet

    Short Version.zip

    I tried to attach a short version of my DB so you could see what I did.

    Basically, I am using a query to pull all the individual numbers per employee. In order to get a percentage, I totalled the output to calculate the percentage. Iadded two percenyages together and want the report to sort on that sum. I cannot get this to work properly.

    Thanks for your help so far.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,819
    You will need to do that calculation in the query as the report only sorts on incoming fields?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Report can sort on a calculation in the Sorting & Grouping settings, however, your calculation includes an aggregate function and this cannot be in an ORDER BY clause which is what the sorting feature of Sorting & Grouping uses.

    Change your report RecordSource query to include a subquery that calculates the Sum([SumOfQtyCompleted]) then you can calculate the percentage in query as well.

    SELECT Scoring_A.*, SumOfQtyCompleted/(SELECT Sum(SumOfQtyCompleted) FROM Scoring_A) AS SQCPCT FROM Scoring_A;

    Now use the SQCPCT field in your report to display the percentage and to sort.
    Last edited by June7; 12-04-2022 at 05:37 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    GregShah is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Thanks for the input. I have been attempting to add in the subquery but this is something that I have never attempted. The existing SQL statement is:

    SELECT Production_by_person_Rev_A.Initials, Sum(Production_by_person_Rev_A.QtyCompleted) AS SumOfQtyCompleted, Sum(Production_by_person_Rev_A.TotalWeight) AS SumOfTotalWeight, Production_by_person_Rev_A.EmployeeFROM Production_by_person_Rev_A
    GROUP BY Production_by_person_Rev_A.Initials, Production_by_person_Rev_A.Employee;

    What is the easiest way to add your subquery? Can it be spliced into this existing SQL? If so, I am not sure where to add it. If this must be done a different way, can you lead me in the right direction. I am willing to try new things but been stuck since yesterday trying to add the sub in.

    Thanks again,

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    No, you build query as I posted.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    GregShah is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Got it. I know just enough to be dangerous. Thanks for the help and your patience.

    Greg

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-19-2022, 12:21 PM
  2. Replies: 7
    Last Post: 03-28-2018, 05:46 AM
  3. Replies: 1
    Last Post: 07-24-2014, 08:35 PM
  4. Calculated expression in report
    By Grizz2 in forum Reports
    Replies: 3
    Last Post: 12-20-2010, 08:50 PM
  5. Report Based on Form - Sort
    By jeffyyy in forum Reports
    Replies: 1
    Last Post: 10-16-2010, 06:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums