Multiple Many to Many Relationships design causing form issues.

[Dpaterson]

Hi All,

I have been tasked with creating an employee database, that reflects their experience, their certifications, if they have taken suggested training and any other skills they may have. I have created four many to many relationships around the main table of Active Employees. I have attached a copy of what the relationships look like.

The management wants to be able to go in and click on a button for each criteria (experience, certification, training, other) to see the employees that have that specific criteria. I seem to have accomplished this, based on the relationships I have set so far, through a navigation pane with buttons that pull from the correct queries.



My problem is management wants a simple form for the employees to each individually go in and check off the criteria they have. I provided them a combo box form, but they didn't like it. They only want a list of all of the criteria (or rather each criteria on its own separate page) with a checkbox next to each that will update the underlying employee tables.

I have racked my brains over this one and I can't seem to find the solution. Can someone please point me in the right direction?

Thanks,

DPat

[June7]

Sounds like management is asking for a non-normalized data entry (example: a check box for each possible certification title) for a normalized data structure.

The only way I know to accommodate them would be with unbound forms and a lot of VBA code to save/delete records as well as a lot of code to display existing data in the non-normalized forms.

[Dpaterson]

Thank you for your reply. Any suggestions on a good place to learn VBA code? After this project, I think I may have moved up from a novice to Advance Beginner.

[June7]

I don't have it but this looks interesting: Access™ 2007 Programming by Example with VBA, XML, and ASP' by Julitta Korol, Wordware Publishing, Inc.

Go to Amazon and see how many second-hand books you can get real cheap. I once bought 5 VB6 programming references for less than $15. I used one regularly for several months while learning.

Search web, lots of on-line free and for cost tutorial sites. Start with http://office.microsoft.com/en-us/ac...010341717.aspx

[Dpaterson]

Once again - Thank you! Your help has been greatly appreciated.

[NTC]

I am starting with your comment " a combo box form, but they didn't like it"...to believe you have everything structured right - and if they liked the combo box form then you would not have any issue.... So that one is really discussing a user interface scheme that will present what they want and behind the scenes accomplish the equivalent of your combo box form.

They seem biased toward checkboxes; You can set up an unbound form with a check box for each training (as example) and then with an 'Enter' button you can trigger an Update Query to the actual table. A big downside to this is that an Access developer must modify that form each time a training class is added, removed, changed. The superior approach of your combobox method would not have that issue (presuming the combobox is table driven content and that a non technical administrator is managing the training class table).

[Dpaterson]

Thanks for your reply.

Yes, that is correct. The combo box form was updating the table correctly; this is purely an appearance thing. As one of the combos contains about 32 choices, they feel it is too long to scroll through. They want to make multiple choices and they don't like moving down to the next line to make another choice, hence the checkboxes.