count calculated field in query access

Question

I have a query where I want to calculate the open position - close (count the closed rows in another table) and to exclude all the 0 results

SELECT Positions.[Req #], tblPositionList.PositionName, 
       Positions.[Opening Date], Positions.[Close Date], 
       Positions.Status, Count([OpenPosition]![Close Date]) AS Closed,
       Positions.Openings AS Opened, [Opened]-[Closed] AS Remaining
FROM tblPositionList INNER JOIN (
       Positions INNER JOIN OpenPosition ON Positions.[Req #] = OpenPosition.[Req #]) 
ON (tblPositionList.PositionID = Positions.PositionID) AND
   (tblPositionList.PositionID = OpenPosition.PositionID)
GROUP BY Positions.[Req #], tblPositionList.PositionName, Positions.[Opening Date], 
         Positions.[Close Date], Positions.Status, Positions.Openings;

I want to add a filter to Remaining field.

Try to replace ! by . in Count([OpenPosition]![Close Date]) — Sergey S.
Hi Sergey, Thank you. The code is working as it is but I want to add a filter on Remaining — Sandra
You cannot calculate a field in a SELECT list and use the name of that field later in the same SELECT list. Instead of [Opened]-[Closed] AS Remaining try using Positions.Openings - Count([OpenPosition]![Close Date]) AS Remaining. — user3728595

Sandra Sandra · Accepted Answer · 2018-03-16T07:28:44

Thank you, @user3728595, it works perfectly. The final script is

 SELECT Positions.[Req #], tblPositionList.PositionName, Positions.[Opening 
    Date], Positions.[Close Date], Positions.Status, Count([OpenPosition]![Close 
    Date]) AS Closed, Positions.Openings AS Opened, Positions.Openings-
    Count([OpenPosition]![Close Date]) AS Remaining
    FROM tblPositionList INNER JOIN (Positions INNER JOIN OpenPosition ON 
    Positions.[Req #] = OpenPosition.[Req #]) ON (tblPositionList.PositionID = 
    Positions.PositionID) AND (tblPositionList.PositionID = 
    OpenPosition.PositionID)
    GROUP BY Positions.[Req #], tblPositionList.PositionName, Positions.[Opening 
    Date], Positions.[Close Date], Positions.Status, Positions.Openings
    HAVING (((Positions.Openings-Count([OpenPosition]![Close Date]))>0));

count calculated field in query access

1 Answers