Training Database Design Help

[JGrots]

Hello All,



I'm working on training database for all employees. I have a form (single form) that shows all employee names. For each employee I want to be able to pick as many training categories as I want from a drop down combo box. Depending on the training categories I want the all topics to show up in another subform along with a DateTrained field so that I can record the date the employee was trained on that individual topic.

I created four tables: Employees Table, Categories Table, Topics Table and a Training Table



I'm having a problem getting the correct topics to display for the selected employee once I select my categories and I'm not able to enter in data in the DateTrained field. I don't want to have to select each topic one by one.

I have attached my database. Any help will be greatly appreciated. Thank you!Training Database Test.mdb

[June7]

Why is Employees table not included in the Relationships builder?

Why is Category related to both TrainingTable and TrainingTopics? I think category should be in TrainingTopics only.

Categories are not related to employees so having that subform does not make sense.

Need to set the Master/Child links for the TrainingTopics subform. Review http://office.microsoft.com/en-us/ac...010098674.aspx

[JGrots]

Thanks, June7 for the help.

I don't want to pick each TrainingTopic individually for each employee. I want to be able to pick the Category which then pulls in all the training topics that are associated with it. At that point then I can record the training date for each topic. How do I do this without using a categories subform?

[orange]

You could use Cascading combo where list of categories is displayed.
You pick the one of interest, then next combo shows only the topics for the selected Category.

research Cascading Comboboxes MsAccess

[June7]

What orange suggests is a way to limit the training topics combobox list but you still have to select desired topic and create the record.

If you want to batch create a set of training topic records and then just manually fill in the date, that will require code running an INSERT SELECT sql action.

[JGrots]

June7,

Is there anyway you can elaborate on the INSERT SELECT coding or provide an example?

[June7]

In VBA, like:

CurrentDb.Execute "INSERT INTO TrainingTable (EmployeeID, CategoryID, TopicsID) SELECT " & Me.EmployeeID & " AS EmployeeID, CategoryID, TopicsID FROM TrainingTopics WHERE [Required Training] <> 'N/A'"

[JGrots]

I have a screenshot of how I want to get all the topics to input into the TrainingTopics Table.


For each employee, I want to highlight a category then click on the execute button and have all the topics for that category input into the TrainingTopics Table.
Here is the code I have thus far, but it's not working:

CurrentDb.Execute "INSERT INTO TrainingTable (EmployeeID, CategoryID, TopicsID) SELECT " & Me.EmployeeID & " AS EmployeeID, " & Me.CategoryName & " AS CategoryID, TopicsID FROM TrainingTopics WHERE [Required Training] <> 'N/A'", [CategoryID] = " & Me.CategoryName & "

[June7]

Is the value of the listbox actually the text description or is there an ID field?

Try:

WHERE [Required Training] <> 'N/A' AND [CategoryID] = '" & Me.CategoryName & "'"

[JGrots]

It is bound by the ID field. I tried your changes but I keep getting an error: Run-time error '3061' Too few parameters. Expected 2.

I've reattached an updated version of the DB.

Training Database Test.mdb

[JGrots]

I was able to get it to work. Here is the updated code:

CurrentDb.Execute ("INSERT INTO TrainingTable (EmployeeID, TopicsID) SELECT " & Me.EmployeeID & " AS EmployeeID, TopicsID FROM TrainingTopics WHERE CategoryID = " & Me.CategoryName)

I have another question to ask now. What code do I use so that a topic and can only be added to the TrainingTable for a given employee once? I don't want there to be any duplicates for the same employee.

[June7]

CategoryID is not a field in TrainingTable.

The value of listbox is number ID so remove the apostrophes.

Remove the s from TopicsID in the SELECT statement. There is an s in TopicsID for TrainingTable, but not for TrainingTopics.

The actual name of RequiredTraining field does not have a space. The caption has a space. This is why I never set these properties in table. They just add confusion.

CurrentDb.Execute "INSERT INTO TrainingTable (EmployeeID, TopicsID) " & _
"SELECT " & Me.EmployeeID & " AS EmployeeID, TopicID " & _
"FROM TrainingTopics WHERE RequiredTraining <> 'N/A' AND [CategoryID] = " & Me.CategoryName

[JGrots]

Thanks June7. I realized the TopicsID were different so I changed the TopicID in the TrainingTopics to TopicsID.

I'm trying to make sure that multiple copies of a category/topic can't be inputting for the same employee. Here is the code I have but I keep getting a runtime error 424 object required.

If Query1.EmployeeID <> Me.EmployeeID And Query1.CategoryID <> Me.CategoryName Then

CurrentDb.Execute ("INSERT INTO TrainingTable (EmployeeID, TopicsID) SELECT " & Me.EmployeeID & " AS EmployeeID, TopicsID FROM TrainingTopics WHERE CategoryID = " & Me.CategoryName)

End If

[June7]

Can't refer to a table or query directly like that. Have to open a recordset filtered to the values and check the recordset content or use DLookup. However, you would have to check every single EmpID/TopicsID pairing. Not practical with this batch INSERT.

Remove the outer parens from the Execute code.

Why did you remove the RequiredTraining parameter?

[orange]

I have another question to ask now. What code do I use so that a topic and can only be added to the TrainingTable for a given employee once? I don't want there to be any duplicates for the same employee.

You create a unique composite index of the fields whose combination you only want 1 time.

Sounds like TopicId,EmployeeID would be the fields in your unique composite index.

research "unique composite index" with search.