Building a staff training database. How to query a field with multiple values?

[Hikari Kisugi]

Hi,
I am trying to build a small access database to allow us to keep track of our employees training.
We are a small healthcare company, and I have thus far been able to modify a MS template to allow us to enter staff personal details.
I have then created a second table for training information.
The training table has field for unique ID, title of course, trainer, date, duration and who attended.
The who attended field allow for multiple contacts to be selected with a checkbox style box appearing, as many people can attend one training session.

What I am trying to achieve is a method of adding an individuals training record to a form which contains their contact details, preferably as a second page.
I thought I would be able to achieve this with a subform, unfortunately the subform query system doesn't seem to allow me to investigate the 'values' contained within a field which has multiple entries.
I can't see any way around this but I am very new to access.
Is there a way around it?


Something obvious I have missed?

Thanks for advice in advance.
Anyone help me on a process to achieve what I need?


Access 2007 on Win7/Win8 System

[June7]

I NEVER use multi-value fields. Instead, use a 'junction table' that relates staff with course. Bind a form to the junction table and place it on a form bound to Courses table. Select employee from combobox in the subform to create a record for each staff attending the course displayed on the main form.

Why would you need to show staff contact info on this data entry form? Consider a report instead.

[Hikari Kisugi]

Thanks for replying so swiftly.
The multi-value field in question was a box containing everyone's name. I can remove that, only test data has been added to this point.
Staff contact info is on the staff data entry form for the contacts tables.

With your method when I am generating each course for the training table, and saying who was present, does it involve an individual entry for each staff member?
I am a little confused regarding the binding process you are suggesting.

[June7]

Data entry with form/subform arrangement is basic Access functionality. Review http://office.microsoft.com/en-us/ac...010098674.aspx

[Hikari Kisugi]

Thanks again.
Yes thanks I had been reading that earlier while working through some of the access tutorials.
What I am asking is if with say 45 employees, 20 of them attend a course, is it possible, without using a multi-value field, use a subform to enter all their names at once, in a way that is searchable using an individuals name?

I think I am clearly setting out the wrong fields, or not planning the database correctly, but I can't see how I can change the structure to make it useful in exactly the way I need.

[June7]

Not really. The subform method is creating a record for each attendee. Select one employee from combobox. Move to new record row, select another employee from combobox.

It would be possible to select multiple employees in a multi-select listbox then use VBA code to save a record for each selected employee.

[Hikari Kisugi]

Ahh, now that makes sense to me.
That would leave things searchable and displayable as I would like.
I'll have to look into the options and then decide exactly what is best.
The subform method would work fine, but with 45 staffs and approx. 50 different events over the past year, I can't bear the thought of 2000 separate entries with a similar amount in the coming year, and so on, considering their is such mass duplication of people and events.

[June7]

Bear it or not, that's the nature of relational database.