IIf statement query issue (1 Viewer)

Alinchains

New member
Local time
Today, 17:10
Joined
Nov 4, 2020
Messages
4
Hello group.
I have a table I need to have an update done to and I am struggling to get it to work.
I have a table named AU-ARCM Details.
There are 3 fields. [Activity AU Frequency] [Control AU Frequency] [Monitoring Tool AU Frequency]
Each field has a frequency of Daily, Monthly, Quarterly, Simi-Annual or Annual
Depending on what is in each field, I need it to update a 4th field [Pass/Fail] For instance:
If [Activity AU Frequency] = "Daily" and [Control AU Frequency] = "Monthly" and [Monitoring Tool AU Frequency] = "Quarterly" then update [Pass/Fail] 'Pass"
I need to account for all the possible scenarios.
Help please!
 

Minty

AWF VIP
Local time
Today, 23:10
Joined
Jul 26, 2013
Messages
10,355
Welcome to AWF !

You haven't given us enough to go on really. We need the following;

I would suggest mocking up some sample data. Two sets
A) Starting data - What you actually have
B) End Result - What you expect to see.

Rules: How we get from A to B.

You can do the mock-up in excel and post it back here to help people work on your actual data.
 

Isaac

Lifelong Learner
Local time
Today, 16:10
Joined
Mar 14, 2017
Messages
8,738
Agree with Minty,
at the very least give us WHAT all the possible scenarios (and the desired outcome of it), is. rather than 'i need to account for all possible' and then we guess.

+1 for Alice in Chains, in case that's your username meaning!
 

Alinchains

New member
Local time
Today, 17:10
Joined
Nov 4, 2020
Messages
4
Thank you.
I have attached a very simplified version of this. In the last field is the result of the Pass/Fail. Yes, huge Chains fan!
 

Attachments

  • Example.accdb
    372 KB · Views: 103

Minty

AWF VIP
Local time
Today, 23:10
Joined
Jul 26, 2013
Messages
10,355
Okay - I still don't understand the rules.
What is the logic behind a pass or fail?
AU-ARCM Details AU-ARCM Details

IDActivity AU FrequencyControl AU FrequencyMonitoring Tool AU FrequencyPass/FailWhat the expected result in Pass/Fail would be
1​
DailyDailyDailyPass
2​
DailyMonthlyQuarterlyPass
3​
DailyQuarterlyAnnual or LessFail
4​
QuarterlyDailyAnnual or LessFail
5​
DailySimi-AnnualQuarterlyFail
6​
QuarterlyQuarterlyAnnual or LessPass
7​
MonthlySimi-AnnualQuarterlyFail
 

Alinchains

New member
Local time
Today, 17:10
Joined
Nov 4, 2020
Messages
4
That is the trick. I have to account for all the different possible situations.
If a process is done daily then it probably should be monitored frequently. So a monitor of daily and a control of daily would be best case but you could also take it out a out bit and say if the process is done daily then you could monitor it weekly and then the control monitor could be monthly and it would still be ok..
If a process is done daily, then to only monitor it quarterly and have controls reviewed Annually isn't sufficient.
Do you think I need a table of every possible situation?
Okay - I still don't understand the rules.
What is the logic behind a pass or fail?
AU-ARCM Details AU-ARCM Details

IDActivity AU FrequencyControl AU FrequencyMonitoring Tool AU FrequencyPass/FailWhat the expected result in Pass/Fail would be
1​
DailyDailyDailyPass
2​
DailyMonthlyQuarterlyPass
3​
DailyQuarterlyAnnual or LessFail
4​
QuarterlyDailyAnnual or LessFail
5​
DailySimi-AnnualQuarterlyFail
6​
QuarterlyQuarterlyAnnual or LessPass
7​
MonthlySimi-AnnualQuarterlyPass
 

Minty

AWF VIP
Local time
Today, 23:10
Joined
Jul 26, 2013
Messages
10,355
I'm afraid if you can't accurately explain the rules then no amount of clever programming is going to help.
Logic doesn't allow for maybe, possibly, might.

See the attached for all the permutations.
 

Attachments

  • Example.accdb
    420 KB · Views: 97

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:10
Joined
Feb 28, 2001
Messages
27,001
The start of this process will be a "truth table" approach, I think.

We start with choices. Each of 3 fields can have one of five values: Daily, Monthly, Quarterly, Semi-Annual or Annual. According to permutation rules, I believe that means you have 125 possible outcomes. That means your first step is to build a table of four columns with your values for each. I am free-hand typing this so I'm going to terribly abbreviate things - like using the first letter of the frequency code to represent that frequency. Hope I don't confuse you too much.

Code:
PassFail Table:  Activity, Control, Monitoring, Result
D,D,D,Pass
D,D,M,Fail
...
D,M,Q,Pass
...
D,S,Q,Fail
...
M,S,Q,Fail
...
A,A,A,Pass

I know most of those are right because I took them from your table earlier. But I guessed here and there. Now declare the first three columns of the PassFail table to each be indexed. DO NOT make any of them the prime key. However, you COULD, if you felt you needed to, make the first three columns taken together as a compound primary key in order to assure that the 125 combinations would not be duplicated.

Phase 2 is that your Details table (again, abbreviating the name, and I will again abbreviate the fields) has something in common with that PassFail table - namely the same fields as the first three members of this reference table.

Code:
UPDATE Details INNER JOIN PassFail
    ON Details.Activity = PassFail.Activity AND Details.Control = PassFail.Control AND Details.Monitoring = PassFail.Monitoring
SET Details.PF = PassFail.Result ;

OK, what did I just suggest - in English? The PassFail table is a place for you to store your predetermined results, pass or fail as may be for each combination. A pain in the patootie to set up - but you only have to do it once. Then in your query, you join Details to the PassFail table to look up the correct value. You have a low post count. That doesn't tell us your level of expertise, but I can make some assumptions that you are still learning. (It's OK, we ALL went through that phase.) A JOIN query is a way to find common elements between two tables, so what I did is set it up to have all possible combinations available - meaning you can look up anything. Then the UPDATE query executes the JOIN, and the ON clause is like a "preliminary WHERE clause" to facilitate the joining of matching records.

Rather than some HORRIFIC IF statement, do a tedious one-time table of all values. Then just look up whatever combination you've got. The only catch then is to assure that in the Details table, you don't have any blanks or nulls in those columns.

Hope I didn't confuse you and hope it helps.


As a side note, a good Access DB will NEVER use special characters in a field name, as for example your [Pass/Fail] field. You will really confuse the issue if you ever leave off the brackets. Better to remove the special character and then just not have to type so much. Makes for fewer errors. Just an observation, not intended as a "dig."
 
Last edited:

Alinchains

New member
Local time
Today, 17:10
Joined
Nov 4, 2020
Messages
4
That is great. I will do some testing on this and see. I am sorry about being vague on the rules since there are 6 possible results for each of the 3 fields so there are a number of possible outcomes. If I can get an idea then I can build it as I go and update when a difference scenario comes into play. Much appreciated!
 

Users who are viewing this thread

Top Bottom