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!
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!
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?
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.
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.
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."
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!