Qry on 2 tables using ~15 check boxes in each table

WalterInOz

Registered User.
Local time
Tomorrow, 03:37
Joined
Apr 11, 2006
Messages
93
I have 2 tabels, Docs and People
Each table has about 15 check boxes to further specify the doc/user.
-in tblDocs which groups of users this document is intended for and
-in tblPeople which document the user should be trained in and a tickbox if training has been completed.

e.g. if tblDocs chkA is ticked then all people that have there chkA ticked should be trained in that procedure, if tblDocs chkS and chkE are ticked, all people that have either chkA or chkB in tblPersons ticked must be trained.

What I'm looking for is a query that can can check this on a form.

I know how to set it up so that all values from a form with all details of a person are transferred into a query, but I just cannot work out how I then check the tblDocs for documents that do NOT have the corresponding boxes ticked. In other words, I'd like to see if people have been trained in all documents they should have been trained in.

I have no idea how to handle that.
 
One of the basic construction rules when you are working in MS Access is to look for "entities" and that's just a posh name for saying that all addresses should be in one table, all medicines should be in another table, in other words "like things" should be in a single table. In your case if you have a table that has multiple check boxes all in separate fields, then this indicates to me it is a likely candidate to go into a separate table.

I realize that on the face of it, this sounds like complicating the matter, and right at the beginning of your project it is inconvenient and complicated. However if you put the effort into getting the design right in the beginning, you can benefit no end from simplicity later on in your project.

The other benefit is that your design will be familiar to other database programmers so that when you ask a question, there is generally already in existence a tried and tested Solution.

Of course you are entitled to construct your database in any way you see fit, however your construction will probably result in creating problems that have not been solved before, meaning that you will probably end up having to solve them yourself.
 
Thanks for your reply Uncle Gizmo

I think I'm actually doing what your suggesting, but then again, maybe not quite. I'll try to explain a bit more clearly.
You say:
One of the basic construction rules when you are working in MS Access is to look for "entities" and that's just a posh name for saying that all addresses should be in one table, all medicines should be in another table, in other words "like things" should be in a single table. In your case if you have a table that has multiple check boxes all in separate fields, then this indicates to me it is a likely candidate to go into a separate table.

OK, I hear you but I don't see how, nor am I convinced that you completely understand what I'm trying to achieve. I don't doubt your skill, I put it down to my poor explanation. I'll try again and please let me know if you still think I'm making a fundamental error.

My "entities" here are Documents and People
The people need to be trained in following the correct procedure according to GLP (= Good Laboratory Practice). There are documents that describe procedures that have mainly to do with e.g lab personnel, or Quality, or Admin etc. Each of these main groups consists of several subgroups (e.g. for R&D there are the subgroups everyone, protein purification, antibody production, maintenance, dispatch and there will be a few more in the future).

I've started with having different tables for each group (Docs-> Group->subgroup) but the problem with that was that I need the flexibility to choose any number of groups and subgroups as some docs apply to more than one group.
It gets even worse because as we are a small company of about 25 people we all wear different hats at different times so one person will most likely have to be trained on documents that go across groups as well.

The only solution I saw was to specify groups and subgroups for each individual document and to me check boxes are the most flexible option. The only alternative I saw was to have a combobox with each option in a different field and that gets a bit exhausting. Cascading comboboxes wouldn't do it as I need to be able to tick multiple boxes.

I realize that on the face of it, this sounds like complicating the matter, and right at the beginning of your project it is inconvenient and complicated. However if you put the effort into getting the design right in the beginning, you can benefit no end from simplicity later on in your project.

I'd love to get the design right!! At first glance the checkboxes indeed represent similar things. However, in one case they're linked to a document, and in the other to a person. They're not necessary the same. How can I have them in a single table when >90% must have multiple boxes ticked?
I have thought about this a great deal as everybody always stresses to importance of normalization but I haven't been able to come up with an alternative approach.

The other benefit is that your design will be familiar to other database programmers so that when you ask a question, there is generally already in existence a tried and tested Solution.

Of course you are entitled to construct your database in any way you see fit, however your construction will probably result in creating problems that have not been solved before, meaning that you will probably end up having to solve them yourself.

Yes, good point. That's Why I ask for help here cause on my own I haven't been able to come up with something that works.

Further advice would be appreciated.
Thanks,
Walter
 
Hi Walter,

This a the usual case of insufficient information to built a database. A common one indeed.

Start by asking "What is Document?" and "What is People?" Then, "What does each column in the table represent?"

Here's a Link for a start. Try the Student Assessment Model.

I have 2 tabels, Docs and People
Each table has about 15 check boxes to further specify the doc/user.
-in tblDocs which groups of users this document is intended for and
-in tblPeople which document the user should be trained in and a tickbox if training has been completed. snip.....
I have no idea how to handle that.
 

Users who are viewing this thread

Back
Top Bottom