September 21, 2011 at 8:54 am
I'm supposed to write code that would give results of a truth table from a table in access. For example iif column_a <>"", column_b<>"", column_c<>"", column_d<>"", "1", Iif column_a<>"", column_b<>"",column_c<>"",column_d="","2", iif column_a <>"", column_b<>"", column_c="", column_d<>"","3", etc. There are 16 different combinations so I can't really do nested iifs. Would the switch function work. Or can I only switch 1 to 1 (there are 4 columns that I am checking values on).
September 21, 2011 at 9:37 am
Why not create the actual truth table as a table? You're in a database, tables is something it understands.
just make a table with 6 columns:
RuleRank,
ValueA,
ValueB,
ValueC,
ValueD,
Result
And join to it. No iif, or switch needed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 21, 2011 at 10:02 am
I just don't understand how to get the results? How many records are rank 1, how many are rank 2... how many are rank 16? I can create the Truth Table as a series of T/F values. I actually have it on paper so that I can figure out what factors I am looking for: iif(column_a<>"", column_b<>"",column_c<>"", column_d<>"", "1" ,iif(column_a<>"", column_b<>"", column_c<>"", column_d="","2", iif(column_a<>"", column_b<>"",column_c="", column_d<>"", "3". on through rank "16". But I don't have anything to join this truth table as a series of 4 columns and 16 rows of T & F to the original table where the columns are that would be blank or not.
September 21, 2011 at 11:08 am
Your example was showing duplicated combinations, so the ranking would have been there to "break the tie"
To get the result in using a table - join your original table to the truth table, matching columnA (from the source) to VAlueA (from the truth table) and ColumnB to ValueB, etc... The result that matches the join you made up is the "answer".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 21, 2011 at 12:19 pm
So the original table has like 20 columns. I only want to care about 4 of the columns. I was trying to create an output table that would have those 4 columns plus a 5th column that would say what the ranking is 1-16. I need to know if 4 columns from the original table have null or "" values (should be "" not NULL because they are text fields). The columns I want are SSN, Insurance #, Phone, Address. My associate wants to know how many are each of the 16 possible combinations of answers. "1" would be all four fields are not null or "". "2" would be all fields are null or "" except address. "3" would be all are null or "" except Phone. "4" would be all are null or"" except Insurance #, "5" would be all are null or "" except SSN. "6" would be values for SSN & Phone but null or "" for Insurance # & Address. on through 16 possible combinations. Then I can sort the output table to see how many are each of the 16 possible combinations of choices. Does that make sense? I don't see how I could link the truth table to the original table. For instance my SSN column on the truth table would be all T or F. And in the original table every answer that is not null or "" would be T or F. Does that make sense?
September 21, 2011 at 7:48 pm
Then Create a view on the table which looks something like
select (nz(ColumnA,"")="") as NoValueA, (nz(ColumnB,"")="") as NoValueB, (nz(ColumnC,"")="") as NoValueC, (nz(ColumnD,"")="") as NoValueD from MyTable
Then your Truth table is a series of True or false (i.e. boolean) columns, followed by your return value (the query below would emulate your truth table
select -1 as NoValueA, -1 as novalueB, -1 as novalueC, -1 as noValueD, 1 as MyReturn
union
select 0 as NoValueA, -1 as novalueB, -1 as novalueC, -1 as noValueD, 2 as MyReturn
union
select -1 as NoValueA, 0 as novalueB, -1 as novalueC, -1 as noValueD, 3 as MyReturn
etc....
join the truth table to the query and you'll have your returnset
select MyReturn, Count(myReturn) as IncidenceCount from
MyQuery q inner join MyTruthTable T on q.novalueA=T.noValueA and q.novalueB=T.noValueB and q.novalueC=T.noValueC and q.novalueD=T.noValueD
group by MyReturn
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply