Using a non-aggregate expression in another expression

[threegoldstars]

Hello,

I have an expression



PSFQual: IIf([nrazor]=2,1,0)+IIf([rk2]=1,1,0)

that when grouped, returns a 0, 1 or 2 (my actual expression is much longer, but I shortened it for the purposes of this question).

Now, I would like to use "PSFQual" in another expression like NewNeg: IIF([PSFQual]=1,1,0 + IIF(IIf([rhtr]=1,1,0).

It's basically algebraic substitution and I don't want to (can't) include the entire PSFQual expression in my NewNeg expression and I would like to use substitution instead. Can I do this somehow?

Thanks in advance.

[ranman256]

in the query, make the 2 fields to add:
Q1:
select nrazor, IIf([nrazor]=2,1,0) as F1, IIf([rk2]=1,1,0) as F2 from table

Q2:
select F1+F2 as PSFQual from Q1

[CJ_London]

or you can alias a query as a table

select F1+F2 as PSFQual from (select nrazor, IIf([nrazor]=2,1,0) as F1, IIf([rk2]=1,1,0) as F2 from table) Q1

you can still develop as separate queries but when you are happy with the outcome, just copy the Q1 sql (excluding the ';' and paste into the Q2 query and surround with brackets and shown in red - you can use AS if you want (... table) AS Q1) but it is not necessary

[ssanfu]

Welcome to the forum....

I read this a little different.

It sounds like you want to do a calculation in a query, then use the result in another calculation without having to retype the entire first calculation.

Start out by using two queries. ( they can be combined into one query, but I'm going to show two queries)

Name the first query "Query1". (pretty slick, no???)
The query SQL would be

SELECT nrazor, rk2, rhtr, IIf([nrazor]=2,1,0)+IIf([rk2]=1,1,0) AS PSFQual FROM YourTable;

Change "YourTable" to the actual name of your table.
Execute the query to see that it returns the expected results.

If all is OK, now create another query (maybe named "Query2" )
The SQL for this query would be

SELECT PSFQual, rhtr, IIF([PSFQual]=1,1,0 + IIf([rhtr]=1,1,0) AS NewNeg FROM Query1;

If you didn't name the first query "Query1", be sure to change "Query1" to the name use used.

Execute this query to see if you get the expected results....
Now you can use the query names you want with all of the fields you need. Add the fields to both queries.

[threegoldstars]

Welcome to the forum....

I read this a little different.

It sounds like you want to do a calculation in a query, then use the result in another calculation without having to retype the entire first calculation.

Start out by using two queries. ( they can be combined into one query, but I'm going to show two queries)

Name the first query "Query1". (pretty slick, no???)
The query SQL would be

SELECT nrazor, rk2, rhtr, IIf([nrazor]=2,1,0)+IIf([rk2]=1,1,0) AS PSFQual FROM YourTable;

Change "YourTable" to the actual name of your table.
Execute the query to see that it returns the expected results.

If all is OK, now create another query (maybe named "Query2" )
The SQL for this query would be

SELECT PSFQual, rhtr, IIF([PSFQual]=1,1,0 + IIf([rhtr]=1,1,0) AS NewNeg FROM Query1;

If you didn't name the first query "Query1", be sure to change "Query1" to the name use used.

Execute this query to see if you get the expected results....
Now you can use the query names you want with all of the fields you need. Add the fields to both queries.

So let me see if I understand what you are saying...basically, you are saying that I can create a query to effectively calculate a value of PSFQual. This query gets saved and will be called later.

Now make a second query and call the first query in the SQL statement. The results of the 1st query will be in the "equation" in the 2nd query.

This makes sense, BUT, now won't all of my results be restricted to records called by the first query? Hmmm....maybe not. I will play with it and report back. Thank all of you for your help!!

[ssanfu]

Now make a second query and call the first query in the SQL statement.

It is not "Calling" the first query. You ADD the query designer just like you added the table in the first query. Think of a query as a "virtual table".


How many records are in "Table1"?
Create "Query1", add fields, create the calculated column for "PSFQual".
Execute the query. How many records are returned? Should be the same number as records in the table.
Create "Query2". using "Query1" instead of "Table1". Add fields and create the calculated column "NewNeg".
Execute the query. How many records are returned? Should (still) be the same number as records in the table.

In "Query2", you now have the fields of the original table, the calculated column "PSFQual" AND the calculated column "NewNeg".

[threegoldstars]

Ok, I think I understand! The first query is going to be like another table, but containing PSFQual. In effect, I will have created another field in my database, but in a pseudo-table of sorts (which is Query1). Then, I can use that field however I want....right??

[ssanfu]

Then, I can use that field however I want....right??

You got it...

[CJ_London]

basically what I said in post #3