Filtering with checkboxes in a form

[MSVille]

Hi!



I've been trying to find a solution to my problem from several forums with no luck, so I decided to post my first thread/question...

I have database of personnel with different qualifications, let's call them Qualification A through G. My Table has the name of the person in the first column and title, contact details etc. in the following 4 columns. In columns 6-12 I have the titles of Qualification A through G with Yes/No as a row value.

I have a form with 7 checkboxes with titles Qualification A through G. There is also a listbox in the form. What I need is for the listbox to show only personnel that has "Yes" in the qualification selected in the checkboxes not considering which value the other qualifications have.
Eg. I check Qualification A, Qualification D, Qualification E and the list box will show me 5 names out of 50 names in the table, let's call them Mary, Ann, Jeff, Rick and Susan. They all have at least the qualification A/D/E. Mary also has B and Jeff has C qualification, so If I would also now check B qualification checkbox, only Mary would be in the listbox. If I would check both B and C checkboxes, the listbox would be empty because no-one has A/B/C/D/E qualification. Unchecking checkbox A and B (now C/D/E enabled), the listbox would show only Jeff's name from the previous 5 (being the only one with C qualification) and adding from the table everyone from the remaining 45 people with C/D/E qualifications.

Hope this makes sense of what I am trying to achieve. I would love to use checkboxes to select the qualifications needed, but if this is not possible, also multiselect listbox with the qualifications would be acceptable...

Thanks for your consideration and support!!!

[Minty]

Unfortunately your table design is wrong for a database to work correctly.
You need two tables or possibly three tables tbPerson , tbQualifications, tbPersonQualifications.

Each will have a unique ID PersonID, QualificationID, PersonQualificationsID.

tbPersonelQualifications would record the Qualification ID and PersonID.

This way when you add Qualification number 8 or 9 or 23 you don't need to redesign your forms and tables, you just add data.
Your multiselect list box is then driven by a table not a field.

[MSVille]

Unfortunately your table design is wrong for a database to work correctly.
You need two tables or possibly three tables tbPerson , tbQualifications, tbPersonQualifications.

Each will have a unique ID PersonID, QualificationID, PersonQualificationsID.

tbPersonelQualifications would record the Qualification ID and PersonID.

This way when you add Qualification number 8 or 9 or 23 you don't need to redesign your forms and tables, you just add data.
Your multiselect list box is then driven by a table not a field.

Thanks for the reply! I will look into this database issue, but I am not really worried about adding qualifications at this point. Is this configuration necessary to enable what I am trying to do with the filtering?

[Minty]

Without a proper normalised data structure anything you try and do with a database will become very tortuous.
Effectively you will be "Band Aiding" any solutions and forms to accommodate a poor data design, and eventually you will come up against something that will be virtually impossible to "Work around" (e.g. bodge)

I would recommend getting your tables correct first then look at building forms and processes around that.

You have committed what is sometime referred to as "Spreadsheetitis", that is to say simply moved a spreadsheet list into a table.
I guarantee you will regret not getting your tables right at the outset.

Google Normalisation and look here http://www.rogersaccesslibrary.com/f...7eec5982638889
Read through the examples and spend a few hours doing the lessons/reading the course notes.
It will save you hours of frustration.

[MSVille]

Thanks! I will take your advice and restructure my database. But I still need help with the original problem. Let’s say I know have the 3 tables you suggested in your first reply, what’s next?

[Minty]

As basic description I would have a multiselect listbox with your qualifications listed.
After selecting the qualifications, a command button would then run some code to populate another list box or continuous form to list the people with the selected qualifications.

This will be quite simple to implement.