Thread: Help writing a query or multiple if needed

1. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
Provided Answers: 15
Originally Posted by JBarwick
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?

3. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
Provided Answers: 15
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```

5. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
Provided Answers: 15
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?

Posting Permissions

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