Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    6

    Unanswered: Help writing a query or multiple if needed

    So I have some data for a project and need help figuring out how to streamline this process. "Est." and "Actual" information is already in my table but I need some help with totaling and calculations.

    The directive number is derived from taking the total from the "Est" column and dividing it by the "Actual" and multiplying that by the "Actual" column. As you can see 1.0112 * 32 = 32.3595

    Est.....Actual.....Directive.....Adjustment
    25......32..........32.3595.......7.36
    25......37.2........37.6179.......12.62
    100....115.7.......117............17.00
    75......72...........72.8089.......-2.19
    35......0.............0................-35
    10......10.1.........10.2134......0.21

    270.....267.........270.............0 Totals

    270 / 267 = 1.0112

    How can I get this to work? I tried setting up multiple queries but it got messy and I couldn't get the 1.0112 number to multiply by each different row. Also having this update into the original table would be amazing!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by JBarwick View Post
    The directive number is derived from taking the total from the "Est" column and dividing it by the "Actual" and multiplying that by the "Actual" column.
    This is not clear. Do you mean divide the total from the [Est] column by the total of the [Actual] column, then multiply the result by the value in the [Actual] column for each row?
    Have a nice day!

  3. #3
    Join Date
    May 2012
    Posts
    6
    Correct. Since this is a monthly type report the totals will change and thus the directive multiplier will change as well

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You mean something like this?
    Code:
    SELECT [SomeTable].[Est], 
           [SomeTable].[Actual], 
           [SomeTable].[actual]* Totals.y As Directive
    FROM SomeTable, ( SELECT Sum([SomeTable].[Est]) AS SumEst, 
                             Sum([SomeTable].[Actual]) AS SumActual, 
                             Sum([SomeTable].[Est])/Sum([SomeTable].[Actual]) AS y 
                      FROM SomeTable 
                    ) as Totals
    Have a nice day!

  5. #5
    Join Date
    May 2012
    Posts
    6
    This worked out great, thank you!

    One idea that may come up in the future is to only sum those rows based on another column with an identifier. I assume a WHERE or HAVING clause will work in this case for instance adding:

    WHERE [SomeTable].[Identifier]>0

    but it does not return correct data. Is this an easy fix?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. WHERE performs the selection based on its criteria on the full data set, i.e. before any grouping operation.
    2. HAVING performs the selection based on its criteria on the data set resulting from a grouping operation.

    Where in the query did you try to place a WHERE condition?
    Have a nice day!

Posting Permissions

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