Is there a good way to create a "table of conditions"?
I have a huge table, where 4-5 columns are considered in a set of rules, and based on those columns default values must appear in other columns. The update part, I can do.
I would like for the whole thing to be user maintainable: they should be able to open a table and change, delete or add rules. They can do it with a table, but they can't edit a code.
A simplified version would be something like this:
If ColumnA = 1 AND ColumnB = 2 THEN X
If ColumnA = 1 AND ColumnB IS empty (NULL) THEN Y
If Column A = 3 AND ColumnB IS anything THEN Z
Is there a nice way to do this? I can programmatically loop through the rows and construct update queries, but it will be desperately slow, and will run as many updates as many condition rows there are. Seems wasteful.
I came up with a token for "anything": (*), and I can use this in a horrid SQL, but I was wondering if there was a better way to do this.
SELECT M.* FROM MainData M INNER JOIN Rules R ON ((M.Check2 = R.C2) OR R.C2="(*)" OR (R.C2 IS NULL AND M.Check2 IS NULL)) AND ((M.Check1 = R.C1) OR R.C1="(*)" OR (R.C1 IS NULL AND M.Check1 IS NULL))
To make matters worse this is MS Access, but I'm actually interested in the concept at this point.