Use of IIF statement in query or other solution

[Kevin_S]

Hi everyone,

I think this is query focused but I'm not sure this is the only solution so I thought I would post it here. Here is what I am trying to accomplish:

I have two tables: tblMain and tblSectionReview

There is a many to one relationship between the two tables as there are many reviews for each record entry in tblMain. What I need to do in a query (I think) is to test which records are entered on the many side of the relationship.

For example: All records in tblMain with have a total of 6 related records in tblReview as tblSectionReview holds the approval sign offs for the supervisor reviews. What I need to do in a query is return all records in tblMain that are at a certain stage in the sign off process so, for example, If I'm looking to create a report of all records in tblMain that need the fourth supervisor sign off I would need to filter the records where Supervisor3 has signed off and approved BUT supervisor4 has not signed off(made a record entry in tblSectionReview)...

Should I use a nested IIF Statement in the query (never have used before) or is there some other means by which to get the desired results?

Thanks in advance for all help,
Kevin

 

[wiltshire]

I think I understand what you mean - I would simply include all of the tables in a query .

If you put criteria in the same row of a query it means 'and'
If you put criteria in different rows it means 'or'

I'd just put your criteria in the query and use the 'IsNull' and 'Is Not Null' criteria to pull out the required records.

 

[Kevin_S]

wiltshire -thanks for taking an interest -
unfortunitly it isn't that simple as I need to evaluate multiple records not a condition to be found in a single record.

For example: Here is a query result using just the two tables and no criteria:

SubID: :SupvrID: :SupvrDecision:
A2202 Supvr1 1 'Approved
A2202 Supvr2 1
A2202 Supvr3 1
A2201 Supvr1 1
A2201 Supvr2 1
etc...


So, If I tried to add in the query design grid something like:

="Supvr3" And <> "Supvr4" the query fails - the only way I could use Is Null and Is Not Null in this example would be if each supervisorID had its own field:

SubID: :SupvrID1: :SupvrID2: :SupvrID3: etc...

but this would go against the rules of normalization....

 

[jeremie_ingram]

If the sign-off tbl has a field for each of the required sign-offs then this would be a snap.

if the tbl sign-offs lokks something like

SignOffKey - Primary key
SignOff1
SignOff2
SignOff3
SignOff4
SignOff5
SignOff6

Then when you create a query you can add the criteria IS NULL or IS NOT NULL.

So if you wanted to know if there were no signoff4, you would use the IS NULL criteria, which would return every record that has nothing in that field.

 

[Kevin_S]

This would also go against the rules of normalization (see my post above).

The table tblSectionReview is currently set up as follows:

Fields :
ReviewID (Autonumber) (PK)
SubmissionID (FK - joined to tblMain)
SupvrLevel (text)
SupvrDecision (number)
ReviewDate (Date/Time)

This way the table is normalized and allows for the process to change (Example: reduction in the number of supevisor sign offs from 6 to 3) over time and eliminates repeating groups. If the individual supervisor levels were built into the table ( Supvr1, Supvr2, Supvr3, Supvr4, Supvr5, Supvr6 ) then, although this would be easy to filter for the records using Is Null and Is Not Null, it wouldn't be normalized as there are repeating groups and wouldn't be flexible to process/business change...

 

[neileg]

Not a hundred percent sure I understand, but...

If your approval process always runs in order, can you count the number of approval records and deduce the stage in the process?

Use the SupvrLevel field, or add an inspection order field, and find the max.

 

[Kevin_S]

neileg - Sometimes you cant see the forest through the trees!

Thank you very much as you suggestion was exactly what I was looking for! Since I already restrict updating the record until the previous supervisor has signed off in the form, using the count function in the query will produce the results I was looking for in a much, much, much simpilier fashion then a nested IIF statement

Thanks Again for you help,
Kev

 

[wiltshire]

yeh - I was just gonna say that (!!)