Solved Why adding criteria to a column makes a query asking for parameters? (1 Viewer)

[KitaYama]

I have this query which shows the correct results

SELECT
    d.PartID,
    Sum(d.Quan_In) AS InSum,
    Sum(d.Quan_Out) AS OutSum,
    [InSum]-Nz([OutSum]) AS Stock,
    [TraceCode] & "" AS TraceC
FROM
    tblOldData AS d
GROUP BY
    d.PartID, [TraceCode] & ""
ORDER BY
    d.PartID;

If I add a >0 as a criteria to Stock column above, the query asks for two parameters (InSum & OutSum).

The query without criteria for Stock :

Any kind of help is appreciated.

 

[Pat Hartman]

I don't see either "InSum" or "OutSum" in the table's column list.

 

[KitaYama]

They are calculated fields in the query.
Without the criteria the query is OK.

Thanks

Edit : I edited my post above to make it more clear.

 

[theDBguy]

They are calculated fields in the query.
Without the criteria the query is OK.

Thanks

Edit : I edited my post above to make it more clear.

Hi. Not sure if that's allowed in SQL Server, but in Access, try repeating the expression. For example:

HAVING Sum(etc.) > 0

Sent from phone...

 

[MajP]

SELECT tblOldData.PartID,
       Sum(tblOldData.Quan_Out) AS SumOfQuan_Out,
       Sum(tblOldData.Quan_In) AS SumOfQuan_In,
       Sum(Nz([quan_in])-Nz([quan_out])) AS InMinusOut
FROM tblOldData
GROUP BY tblOldData.PartID
HAVING Sum(Nz([quan_in])-Nz([quan_out]))>0

 

[KitaYama]

AH....I should have guessed.

Million thanks to both.

 

[The_Doc_Man]

Just for clarity: When you get an "Enter Parameter" box, it is because the exact name that it is asking for appears somewhere in the query but Access does not know where/how to find that value or field. This occurs because of either a spelling error in the query or failure to qualify the name with a table prefix when multiple tables are involved (e.g. a JOIN) and the answer is ambiguous.

 

[KitaYama]

Doc, thanks for the explanation. I couldn't understand why the query itself is running fine, but as soon as I add a >0 to the criteria of a field, I received the Parameter input box.

 

[ebs17]

but as soon as I add a >0 to the criteria of a field, I received the Parameter input box

The order of query processing is the reason. The assignment of column aliases (naming to the outside world) is practically the very last step. Columns named in this way are therefore not yet known in calculations that are carried out beforehand (here the filtering for sums).
Therefore, the calculation expression must be specified in the filtering (HAVING part).

I have put some thoughts on it here (in German):

Grundlagen - SQL ist leicht (11) - Reihenfolge der Abfrageabarbeitung - MS-Office-Forum

SQL ist leicht (11) - Reihenfolge der Abfrageabarbeitung Microsoft Access - Code Archiv

www.ms-office-forum.net

 

[KitaYama]

@ebs17 Thanks. I'll try Google translate to see if I can understand something.

 

[MajP]

My understanding is that SQL and I assume JET/ACE uses logical query processing, in an order similar to this
FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE/ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

At least that is the order for SQL server. Step 8 is where columns are actually selected and rows returned so you can use an alias in an Order By clause because it comes after 8, but not in the where clause. The alias names an output column, whereas a WHERE clause operates on input columns to determine which rows to select for output.

 

[The_Doc_Man]

The order of query processing is the reason. The assignment of column aliases (naming to the outside world) is practically the very last step.

Thank you for that clarification. I knew in general about the order of query processing but didn't know about when the alias assignments occurred. Good to know!