I’m creating a query that is going to go through and look for various
impossible data entries. What I would like to do is use an IIF
statement and saved subqueries in conjunction with an exists in
statement to create a new variable to tell me what error occurred.
I’ve found some limited info on including subqueries the problem is
that the queries I would need to use are to complex so I was wondering
if there is a way to reference stored queries.

Generic syntax for explanation purposes

Example:

IIF([Assessment_PK] Exists [Subquery1], "Error Type 1",
IIF([Assessment_PK] Exists [Subquery2], "Error Type 2", "Other"))

Where subquery1 is similar to

SELECT Tbl_36Mo_Assessment.Assessment _PK
FROM Tbl_36Mo_Assessment
WHERE (((Tbl_36Mo_Assessment.Trial)="1" Or
(Tbl_36Mo_Assessment.Trial)="2" Or (Tbl_36Mo_Assessment.Trial)="3" Or
(Tbl_36Mo_Assessment.Trial)="4" Or (Tbl_36Mo_Assessment.Trial)="5" Or
(Tbl_36Mo_Assessment.Trial)="6" Or (Tbl_36Mo_Assessment.Trial)="7" Or
(Tbl_36Mo_Assessment.Trial)="8" Or (Tbl_36Mo_Assessment.Trial)="9" Or
(Tbl_36Mo_Assessment.Trial)="10" Or (Tbl_36Mo_Assessment.Trial)="11"
Or (Tbl_36Mo_Assessment.Trial)="12" Or
(Tbl_36Mo_Assessment.Trial)="13" Or (Tbl_36Mo_Assessment.Trial)="15"
Or (Tbl_36Mo_Assessment.Trial)="16" Or
(Tbl_36Mo_Assessment.Trial)="17" Or (Tbl_36Mo_Assessment.Trial)="19"
Or (Tbl_36Mo_Assessment.Trial)="21" Or
(Tbl_36Mo_Assessment.Trial)="22" Or (Tbl_36Mo_Assessment.Trial)="23"
Or (Tbl_36Mo_Assessment.Trial)="25" Or
(Tbl_36Mo_Assessment.Trial)="26" Or (Tbl_36Mo_Assessment.Trial)="27"
Or (Tbl_36Mo_Assessment.Trial)="29" Or
(Tbl_36Mo_Assessment.Trial)="46" Or (Tbl_36Mo_Assessment.Trial)="47"
Or (Tbl_36Mo_Assessment.Trial)="51" Or
(Tbl_36Mo_Assessment.Trial)="52" Or (Tbl_36Mo_Assessment.Trial)="53"
Or (Tbl_36Mo_Assessment.Trial)="56" Or
(Tbl_36Mo_Assessment.Trial)="57" Or (Tbl_36Mo_Assessment.Trial)="58")
AND ((Tbl_36Mo_Assessment.Final_Res)="2" Or
(Tbl_36Mo_Assessment.Final_Res)="3")) OR
(((Tbl_36Mo_Assessment.Trial)="32" Or (Tbl_36Mo_Assessment.Trial)="33"
Or (Tbl_36Mo_Assessment.Trial)="34" Or
(Tbl_36Mo_Assessment.Trial)="35" Or (Tbl_36Mo_Assessment.Trial)="36"
Or (Tbl_36Mo_Assessment.Trial)="37" Or
(Tbl_36Mo_Assessment.Trial)="38" Or (Tbl_36Mo_Assessment.Trial)="39"
Or (Tbl_36Mo_Assessment.Trial)="40" Or
(Tbl_36Mo_Assessment.Trial)="41" Or (Tbl_36Mo_Assessment.Trial)="42"
Or (Tbl_36Mo_Assessment.Trial)="43" Or
(Tbl_36Mo_Assessment.Trial)="44" Or (Tbl_36Mo_Assessment.Trial)="45")
AND ((Tbl_36Mo_Assessment.Final_Res)="0" Or
(Tbl_36Mo_Assessment.Final_Res)="1"));

Any ideas on how to do this or if it’s possible?

Try DLookup

iif(not isnull( dlookup("[Assessment_PK]", "Subquery1", "")), "Error Type
1")

Take a look at the "IN" clause for your query to replace the "OR".

Adding a Lookup table for your conditions and a Inner Join may also speed
things up.

"Craig" <cjohns38 (AT) gmail (DOT) com> wrote

I’m creating a query that is going to go through and look for various
impossible data entries. What I would like to do is use an IIF
statement and saved subqueries in conjunction with an exists in
statement to create a new variable to tell me what error occurred.
I’ve found some limited info on including subqueries the problem is
that the queries I would need to use are to complex so I was wondering
if there is a way to reference stored queries.

Generic syntax for explanation purposes

Example:

IIF([Assessment_PK] Exists [Subquery1], "Error Type 1",
IIF([Assessment_PK] Exists [Subquery2], "Error Type 2", "Other"))

Where subquery1 is similar to

SELECT Tbl_36Mo_Assessment.Assessment _PK
FROM Tbl_36Mo_Assessment
WHERE (((Tbl_36Mo_Assessment.Trial)="1" Or
(Tbl_36Mo_Assessment.Trial)="2" Or (Tbl_36Mo_Assessment.Trial)="3" Or
(Tbl_36Mo_Assessment.Trial)="4" Or (Tbl_36Mo_Assessment.Trial)="5" Or
(Tbl_36Mo_Assessment.Trial)="6" Or (Tbl_36Mo_Assessment.Trial)="7" Or
(Tbl_36Mo_Assessment.Trial)="8" Or (Tbl_36Mo_Assessment.Trial)="9" Or
(Tbl_36Mo_Assessment.Trial)="10" Or (Tbl_36Mo_Assessment.Trial)="11"
Or (Tbl_36Mo_Assessment.Trial)="12" Or
(Tbl_36Mo_Assessment.Trial)="13" Or (Tbl_36Mo_Assessment.Trial)="15"
Or (Tbl_36Mo_Assessment.Trial)="16" Or
(Tbl_36Mo_Assessment.Trial)="17" Or (Tbl_36Mo_Assessment.Trial)="19"
Or (Tbl_36Mo_Assessment.Trial)="21" Or
(Tbl_36Mo_Assessment.Trial)="22" Or (Tbl_36Mo_Assessment.Trial)="23"
Or (Tbl_36Mo_Assessment.Trial)="25" Or
(Tbl_36Mo_Assessment.Trial)="26" Or (Tbl_36Mo_Assessment.Trial)="27"
Or (Tbl_36Mo_Assessment.Trial)="29" Or
(Tbl_36Mo_Assessment.Trial)="46" Or (Tbl_36Mo_Assessment.Trial)="47"
Or (Tbl_36Mo_Assessment.Trial)="51" Or
(Tbl_36Mo_Assessment.Trial)="52" Or (Tbl_36Mo_Assessment.Trial)="53"
Or (Tbl_36Mo_Assessment.Trial)="56" Or
(Tbl_36Mo_Assessment.Trial)="57" Or (Tbl_36Mo_Assessment.Trial)="58")
AND ((Tbl_36Mo_Assessment.Final_Res)="2" Or
(Tbl_36Mo_Assessment.Final_Res)="3")) OR
(((Tbl_36Mo_Assessment.Trial)="32" Or (Tbl_36Mo_Assessment.Trial)="33"
Or (Tbl_36Mo_Assessment.Trial)="34" Or
(Tbl_36Mo_Assessment.Trial)="35" Or (Tbl_36Mo_Assessment.Trial)="36"
Or (Tbl_36Mo_Assessment.Trial)="37" Or
(Tbl_36Mo_Assessment.Trial)="38" Or (Tbl_36Mo_Assessment.Trial)="39"
Or (Tbl_36Mo_Assessment.Trial)="40" Or
(Tbl_36Mo_Assessment.Trial)="41" Or (Tbl_36Mo_Assessment.Trial)="42"
Or (Tbl_36Mo_Assessment.Trial)="43" Or
(Tbl_36Mo_Assessment.Trial)="44" Or (Tbl_36Mo_Assessment.Trial)="45")
AND ((Tbl_36Mo_Assessment.Final_Res)="0" Or
(Tbl_36Mo_Assessment.Final_Res)="1"));

Any ideas on how to do this or if it’s possible?

I'm wondering if it would reasonable to create a union query with each
segment generating errors so the final list of errors is stacked. I
think that would avoid creating overly complicated IIF statements of
what happens if more than one error occurs on each line of data.

.....

Good call on the "IN" I actually just started playing around with that
and it seems to be working well.



On Jul 7, 9:13*am, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Craig wrote:

This might address your problem, maybe not. Make 3 queries; Main query
and 2 subqueries. In the main query, add both subqueries. Link the IDs
between the main and subqs. Double click on the relationship lines and
set for All Recs in Main and Those That Match in Subq. Drag the PK from
both subq's into a column. Give each column a unique name. Ex.
PK1 : PK
PK2 : PK
If PK is the ID it will now be called PK1 or PK2. This in case the main
ID is also called PK.

Now when you run the main query, if PK1 has an ID and PK2 is blank, it's
error 1. If PK2 has an ID and PK1 is blank, it's error 2. If both PKs
exist its Other.