is a sub query required here?

[macri]

i have my query set up with calculated fields like so:

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 [Scrap Produced - LBS], 
FROM Main
WHERE (((Main.[Mth/day]) Between [First Date] And [Second Date] And [Shift]=[Main].[Shift]));

what i'm trying to do now is calculate the net production by subtracting the two calculated fields [Gross Production - LBS] - [Scrap Produced - LBS] AS [Net Production] and i need to Sum the date range i input because the final data must display a full month or a quarterly summary. not sure where to go from here.

 

[macri]

bump?
bump?
bump?
bump?
bump?
bump?
bump?
bump?
bump?
bump?
bump?
bump?
bump?
bump?

 

[Beetle]

If you want to do this in the same query, then you cannot refer to the alias field names ([Gross Production - LBS] and [Scrap Produced - LBS]), to produce the third calculated field. You would need to redo the original calculations like so;

[Run Hrs]*[Actual Lbr/hr]-([Scrap Lbs 1]+[Scrap Lbs 2]+[Scrap Lbs 3]) As [Net Production]

Alternatively, you could create another query, based on the first query, in which you could use the the alias names from the first query;

[Gross Production - LBS]-[Scrap Produced - LBS] As [Net Production]

 

[macri]

ok ok i've got her set up like this, the calcutlations appear to be working however when i run it as i've set it up i get requested for first date second date shift and then for whatever reason actual lbs/hr pops up out of no where as a text entry point but it's not configured as a perameter /le confusions

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 [Scrap Produced - LBS], [Run Hrs]*[Actual Lbr/hr]-([Scrap Lbs 1]+[Scrap Lbs 2]+[Scrap Lbs 3]) As [Net Production]
FROM Main
WHERE (((Main.[Mth/day]) Between [First Date] And [Second Date] And [Shift]=[Main].[Shift]));

 

[macri]

Update: I derped it and typed Actual Lbr/hs when writing thanks for the help all worked out great i'm doing the rest of my calculations this way despite how redundent it seems to be i guess this works just the same really though. thank you <3