Results 1 to 4 of 4
  1. #1
    nrowey is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    2

    Group By Query Results With Division Expression, Access 2016

    I am trying to get a an expression to work in a Group By query that would divied two coulombs but not be part of the grouping, I am looking for just the results of the division expression to be related to the sum of the two columns.



    In the example Statement below when I add the < [MtdHistory]![LABOR$]/[MtdHistory]![SOLD-HOURS]) AS ELR > it separates all the unique values and brakes up the group by sum of the fields I need totaled.


    SELECT MtdHistory.STORE, MtdHistory.SA, Left([MtdHistory]![LBR-TYPE],1) AS [Labor Type], Sum(MtdHistory.[SOLD-HOURS]) AS [Total Sold Hrs], Sum(MtdHistory.[LABOR$]) AS [SumOfLABOR$], IIf([MtdHistory]![SOLD-HOURS]=0,0,[MtdHistory]![LABOR$]/[MtdHistory]![SOLD-HOURS]) AS ELR
    FROM MtdHistory
    GROUP BY MtdHistory.STORE, MtdHistory.SA, Left([MtdHistory]![LBR-TYPE],1), IIf([MtdHistory]![SOLD-HOURS]=0,0,[MtdHistory]![LABOR$]/[MtdHistory]![SOLD-HOURS]);


    Thanks for any help

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make a query that has your special group.
    then make a query using THAT query to sum and group.

  3. #3
    nrowey is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    2
    So are you saying a separate query for the expression [MtdHistory]![LABOR$]/[MtdHistory]![SOLD-HOURS]
    and somehow join them together?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure I understand what you are wanting here - if the group by is "Expression" and that isn't giving you the desired results, then do as Arnie says and create a prior query to do your calculations, then join that query in. Sometimes it takes a few queries joined together to get the data the way one wants it.

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

Similar Threads

  1. ODBC connection for Access 2016 - results in error
    By Ashish_Panchal in forum Access
    Replies: 3
    Last Post: 11-25-2016, 01:35 AM
  2. Replies: 3
    Last Post: 09-25-2015, 12:29 PM
  3. Replies: 5
    Last Post: 11-12-2014, 04:36 PM
  4. Division by zero error on a query
    By thorwood in forum Access
    Replies: 3
    Last Post: 05-19-2011, 07:20 AM
  5. Group query results
    By shak2 in forum Access
    Replies: 7
    Last Post: 10-20-2010, 02:41 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