entry depends on value of another field

[pbuethe]

The situation is as follows (simplified):

The user selects a status code, enters other info which is in the same table. Depending on the status code, may have to enter Approved, Denied, Referred values. There are only 2 values of the status code out of ~20 choices for which the user needs to enter Approved, Denied, and Referred. Is it OK to put these in the same table or does that make it denormalized?

e.g. (LineNbr is the PK)
LineNbr Status Code Approved Denied Referred
1 5 18 50 1
2 6
3 8
4 7 120 140 18
etc. (status codes 5 and 7 are the only ones that can have Approved, Denied, Referred)

Thanks for your help.

 

[jfgambit]

I assume your users are entering the data from a Form, correct? If so, to ensure that they don't enter informtion into the fields that are not supported by the correct Status Codes you can add the following to the After Update event of the Status Code Combobox:

If Me.StatusCode = 7 or Me.StatusCode = 5 then
   Me.Approved.Enabled = True
   Me.Denied.Enabled = True
   Me.Referred.Enabled = True
Else
   Me.Approved.Enabled = False
   Me.Denied.Enabled = False
   Me.Referred.Enabled = False
End if

HTH...

 

[pbuethe]

OK, Gambit, that did not really answer my question, which was whether it was OK to put the ADR fields in the same table with the status code. I did try it and tried your code, but it did not work. I was able to go into those fields regardless of the value of the status code.

 

[pbuethe]

No, Approved, Denied, and Referred are not choices. They are 3 different fields which have numeric values. (Unfortunately the formatting of my example did not come out.) The choices are in the StatusCode field. Whether you enter or skip all 3 fields depends on the choice of StatusCode.

 

[The_Doc_Man]

To answer the more direct question, you really have FOUR values.

Approved, Denied, Referred
and
Not Applicable

Now, the NEXT question is, can one record have more than one of these values at the same time? If this cannot happen then having a "Request Disposition" field (or your other choice of name) does not denormalize the table at all. How you would define the list of possible dispositions for each status code is the thrust of Pat's question.

You could write a combo box that uses a separate little lookup table that has two columns - the code value and its name. Or if the names are all relatively short, you could make the state name also equal to the state value. (Not recommended but might be conceptually easier for folks not used to encoded values.)

Of greater concern is HOW you would decide in a graceful yet flexible way that a particular choice of disposition for each status. The easiest way I can think of that retains flexibility is a table that contains entries consisting of a status code and a disposition code that is legal for that code. Then, if the chosen disposition is legal for that particular code, it will appear in the table. If not legal, it won't be there. Now a DCount of records in the "Legality" table (with status and disposition codes as specified) suffices to tell you whether that particular combination of status and disposition is legal. And your drop-down box for disposition could be predicated on the contents of the status box. Search this forum for the topic "Cascading combo" to see LOTS of discussion on how to do this.

 

[pbuethe]

OK, it looks like my explanation was not clear.

First of all, my original question was as follows: whether it is undesirable (due to normalization or other issues) to have fields in a table such that they would be empty in many records. Or is it better to have them in a separate table. However I would rather keep them in the same table since that would simplify matters.

Anyway, keeping them in the same table, I did get the skipping working properly by adding code to the GotFocus event on intApproved, which would use GoToControl to go to the next control after the 3 numeric fields if it was a non-applicable status code. The 3 fields (using naming conventions which I should have done in the first place) are intApproved, intDenied, and intReferred and have numeric values (they are not codes). On the form is a combo box, cboStatusCode. Now I am getting what I wanted, that if the option selected in the combo box is 5 or 7, the user may enter values in the fields intApproved, intDenied, and intReferred, which denote the number of charts approved, denied, and referred. If the status is 5 or 7, all 3 fields or any combination (or none) may have a value. If any other status code is chosen, all 3 of the numeric fields are skipped.

As I said, I got it working. Thanks for your comments.