Summing a calculated field in the same query can it be done?

PARAMETERS [First Date] DateTime, [Second Date] DateTime, [Shift] Text ( 255 );
SELECT
    Main.[Mth/day],
    Main.Shift,
    ([Run Hrs]*[Actual Lbs/hr]) AS [Gross Production - LBS],
    [Scrap Lbs 1]+[Scrap Lbs 2]+[Scrap Lbs 3] AS开发者_JAVA技巧 [Scrap Produced - LBS],
    [Run Hrs]*[Actual Lbs/hr]-([Scrap Lbs 1]+[Scrap Lbs 2]+[Scrap Lbs 3]) AS [Net Production],
    [Run Hrs]*[Actual Lbs/hr]*0.035 AS [Scrap Target],
    [Run Hrs]*[Std lbs/hr]-([Run Hrs]*[Actual Lbs/hr])*0.035 AS [Target Net Production],
    [Run Hrs]*[Std lbs/hr] AS [Target Gross Production],
    (([Run Hrs]*[Std Lbs/hr])/([Run Hrs]*[Actual Lbs/hr]*0.035)) AS [Target Scrap Rate]
FROM Main
WHERE
    (((Main.[Mth/day]) Between [First Date] And [Second Date]
    And [Shift]=Main.Shift
    And [Std lbs/hr]>0));

I need to also sum each calculated field so they can also retrieve monthly summaries and the like. Can i do this in the same query or am I best to make another query based off of this one's results?

This is really something that should be handled in the front-end or report writer, not through SQL.

In MS Access 2007 the following should work:

1. Open your report in layout view
2. Click on one of the cells in the column that you want to total
3. On the Format tab click on Totals and select Sum

A cell should be added to the report footer with your aggregate.

Start with a query which uses only fields and field expressions which don't use SQL Aggregate Functions. Then save that query and use it as the data source for another query where you do your summing and any other aggregate functions.

In other cases, you could take just the SQL from the first query and use it as a subquery in another query. However, because of those square brackets required for the names, this one would be troublesome as a subquery.

In the future, you can make it easier on yourself by choosing object names which don't require bracketing.