Design theory/problems with audit db, lots of checkboxes...

[Montez659]

Well, that may sum up most of what the problem is right there, but allow me to elaborate.

I am revamping (recreating) a db that my company uses for auditing. The previous db had about 85 check boxes (Yikes!) for each record, on top of about twenty other fields for other general information.

Well, good database practice teaches us that this is horribly wrong on soooo many levels. So what's the alternative? Well, the alternative is to use Junction/Joined tables. This will turn out for the better and allow for more detailed reporting as well as being more normalized.

Here is the catch. After much discussion with the person that will be the primary user of this db, they want to keep the checkbox type interface. So I guess what I was thinking was that I will pretty much have to have countless Events triggered On Click for about 20-25 checkboxes (hopefully after the 85 get condensed) that will manually update via VBA to the Junction table.

Following that thought is, what if an item is clicked that didn't intend to be? A record would be created, but then would I need to search to delete that record? As we all know, deleting records is probably not the best way to go about this, so I am hesitant to do so.

So, in summary:
1.) What am I missing that would make all of this simpler?
2.) If the On Click really is the only way to go, would the record be deleted if it got unclicked, or should there be a field that marks it as deleted without actually deleting the record?
3.) Anything else you can think of will, as always, be quite helpful and appreciated.

 

[nancykhan]

I can not understand

 

[xenou]

Okay.
So what's the problem again ... ?

 

[Montez659]

Well, I guess the question now is, with this many fields, would I want a junction table in place of the 85 (soon to be less I hope) yes/no fields or is it ok to keep that many yes/no fields per each record?

Even if it is a junction table, the user wants to keep the chekbox interface (not my first choice but i work for them). My guess is that I would have to set up several On Click events to trigger an update to the junction table. Also, if a checkbox is checked for no reason, and then unchecked, is it ok to delete that junction table record, or just trigger a separate field to mark as deleted?

Hope that clearer than mud!

 

[xenou]

Personally I see no reason for a junction table. The form is (presumably) already bound to the table that holds the data. So no need for click events either ... I'd leave well enough alone. 85 fields isn't too awful, considering the purpose (with so many items to make sure are checked off, that's the whole point of it - to have a checklist of the 85 items that can be marked off as they are completed). Better than trying to keep track of it on post-its.

 

[Montez659]

Better than trying to keep track of it on post-its.

Lol, no kidding. Ok, even if 85 fields are ok, here is the next question that has to do with reports. Since there are conceivably 85 items that could have exceptions, what would I be looking at for reporting only the ones that are True?

What they eventually want is a summary sheet that would show only what the exceptions are, not necessarily all 85 items that were audited.

 

[Montez659]

I am thinking somehow loop through all of the fields in the record and look to see if it is a Yes/No field (or I might do it by field name, as I plan on renaming all of the fields so that there are no spaces). Then if it recognizes that it is something that should be reported on, it will check to see if a True exists in that field, and if so, I can pull from another table the text that I want displayed.

If I am on the right direction with this concept, let me know. This will take me beyond my current knowledge, but I can research it and get it done.

 

[xenou]

Okay, the lights came on driving home. I think this is how you want to use a junction table here (from a table design perspective):

<img alt="tables diagram" src="http://northernocean.net/etc/mrexcel/20110622_rels.png" />

But I'm not sure how you'd implement this in a form. Possibly you'd need to plug in all the items when a new audit is created (as part of the procedure to start a new audit). Then everything's there and only needs to be checked on/off. Otherwise, you'd probably be adding only the yes's as you go, and would need to left join back to the Items table to get the items that are missing... Not sure. At any rate, now you could fill a list box or subform with the Items and goodbye checkboxes (well, strictly speaking they could still be checkboxes if you really wanted).

I don't like to promote bad designs but yes, you could take each checkbox on the existing form and use it's click event to update the linking table. The only good thing about that is its very simple to do. You can also by the way, populate the linking table with all items pretty quickly with a cartesian join on the first update. You will definitely want to have some kind of "active/inactive" flag for these so you can skip items without trouble, or leave them out of forms and reports if they get obsolete.

 

[xenou]

How much data are we talking about with the "audits" ... ? Dozens? Hundreds? Thousands? More ... ?

 

[Montez659]

We are talking about maybe 100 audits a month, but that number fluctuates depending on new hires (who take more) and others.

Ironically, late yesterday afternoon, I got the go-ahead to skip using checkboxes (Amen!) and so what I plan on doing now is having toggle switches for different categories that will then fill a listbox based on the toggle. Then they can click to add these to another listbox that will eventually cycle through to update a junction table, thus reducing the "bad" data being stored (the extra fields that were checked in error).

And it turns out that it is unlikely that the items will be reduced much beyond 60-70, which is why I am dividing them up into sub-categories.

I hate checkboxes with a passion...

Thanks for your thoughts and help on this!