Okay, I hope I can successfully communicate what I need help with. I have a form with list boxes to indicate if a metric element is MET (compliant) - choices are N/A, NO, and YES. One of the elements has nine sub-elements. All sub-elements must be MET (YES) for the main element to be considered MET. If any one is a NO, then the main element should be NO. Is there a way to set up that main element list box to automatically show a NO if any of the sub-elements have a NO? Hope this makes sense.
One field dependent on answers to "sub-fields"
- Thread starter Kblack
- Start date
You posted this in tables, but talked about forms, so kind of confused about how you are doing this. This issue is 100% dependent on your table structure being set up properly--and I have reservations about that.
Without knowing about your tables I can only help in a general sense. Because the values are Yes, No, N/A you can run a query to get the Minumum and maximum values for an Element's subelements and if the Minimum and the Maximum value of all its subelements are 'Yes" then they are all Yes and that is how you can classify your elements.
If you would like to post a sample database I can give more specific advice.
Without knowing about your tables I can only help in a general sense. Because the values are Yes, No, N/A you can run a query to get the Minumum and maximum values for an Element's subelements and if the Minimum and the Maximum value of all its subelements are 'Yes" then they are all Yes and that is how you can classify your elements.
If you would like to post a sample database I can give more specific advice.
- Local time
- Today, 07:45
- Joined
- Oct 29, 2018
- Messages
- 22,495
Also, you said you're using a Listbox. If so, all options/choices are already "shown," so I'm still not sure I understand what you mean. Can you maybe post a screenshot?
I'm sorry for the confusion! I have not created my form yet. I'm still working on the table that I will use to create my form. I want to make sure that I get the table set up properly before the form is created.
Okay -- the sub-elements are those boxed off in RED. The main element is the highlighted one. What I want to do (to ensure data accuracy) is to use the sub-element ListBox selections (choices N/A, No, Yes) to determine what would populate in the main element field. If NO appears at all in the sub-element selections, the main element will be NO. The compliance for this metric is an all-or-none -- either 100% or 0%.
- Local time
- Today, 07:45
- Joined
- Oct 29, 2018
- Messages
- 22,495
Hi. Thanks for the additional information. You keep using the term "ListBox," but I think we are not understanding you because a Listbox means something else to us. This is what a Listbox in Access looks like:
And if you select an item in the Listbox, this is what it looks like:
That's what I feared--that screenshot is of your table, correct? If so, that's not a properly structured table and you need to fix it. I advise you read up on normalization:
en.wikipedia.org
Then give it a shot with your data. Databases take a whole different mindset to build than spreadsheets. You don't just lay out columns and rows, you must set up relationships and think in terms of generic fields.
The table to hold all those Yes/No values is going to have a lot less fields and a lot more rows. Instead of a column for every Yes/No category, you would have a Category field and then another field to hold the Yes/No value:
tblAuditItems
Category, Presence
Medication, Yes
Initials, No
Expiration, Yes
etc.
Instead of a column for every category, you simply add a row to that table. Again, that's just an example, you need to read up on normalization and apply what you learn to your data.
Database normalization - Wikipedia
Then give it a shot with your data. Databases take a whole different mindset to build than spreadsheets. You don't just lay out columns and rows, you must set up relationships and think in terms of generic fields.
The table to hold all those Yes/No values is going to have a lot less fields and a lot more rows. Instead of a column for every Yes/No category, you would have a Category field and then another field to hold the Yes/No value:
tblAuditItems
Category, Presence
Medication, Yes
Initials, No
Expiration, Yes
etc.
Instead of a column for every category, you simply add a row to that table. Again, that's just an example, you need to read up on normalization and apply what you learn to your data.
Similar threads
