IIF Error Checking Query
in [Help]
|
From: Craig on 7 Jul 2010 09:50 Im 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. Ive 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 its possible?
From: paii, Ron on 7 Jul 2010 10:13 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(a)gmail.com> wrote in message news:f169d399-24fd-4a2f-ae29-be44d6e3f8c4(a)b35g2000yqi.googlegroups.com... 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?
From: Craig on 7 Jul 2010 10:21 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...(a)no.com> wrote: > 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" <cjohn...(a)gmail.com> wrote in message > > news:f169d399-24fd-4a2f-ae29-be44d6e3f8c4(a)b35g2000yqi.googlegroups.com... > Im 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. > Ive 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 its possible?
From: Salad on 7 Jul 2010 10:21 Craig 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? 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.
|
Pages: 1 Prev: Convenient search feature for Access Next: help with slow form loading problem |