Sub forms or sub tables? (1 Viewer)

[pheidlauf]

I'm (re)normalizing a database that I designed with one mistake last summer, and has had many mistakes implemented since I last worked on it. I've normalized the back end of the database, but I need to update and revise the front end to properly access and display the reorganized information to the database user (not me).

I've attached a snapshot of the database relationships in the back end. Some redundant data has been left in the reorganized tables to allow me to finish connecting the data that has been drastically restructured.

Here's what I would love some help with: I have a form, "EditEmployeeProficiencies" that shows a single employees record (their ID number, name, department, status, and what not). Each employee has at least one (if not several) related records in the table "DepartmentProficiencies", each of which shows all of the training proficiencies the employee has received in that area.

I would like a combo box (or sub form, or sub table) with which to select from the current employee's available "DepartmentProficiencies". Once that is selected, I would like the ProficienciesCompleted table to be shown and editable in the form. I've attached the table view of the general information I would like to see on the form called "Sample_Data"

Any advice on how to achieve this would be phenomenal. Please let me know what additional information might be needed.

 

[MarkK]

I don't see why you need a DepartmentProficiencies table, AND a ProficienciesCompleted table.

You have three concrete things, right? Employee, Department, And Proficiency. Now lets say an employee gets trained. That's one event, one thing, should go in one table, and looking at what you have, I would expect that one training event should link back to Employee, Department, and Proficiency.

Hope that helps,

 

[pheidlauf]

I don't see why you need a DepartmentProficiencies table, AND a ProficienciesCompleted table.

You have three concrete things, right? Employee, Department, And Proficiency. Now lets say an employee gets trained. That's one event, one thing, should go in one table, and looking at what you have, I would expect that one training event should link back to Employee, Department, and Proficiency.

Hope that helps,

The reason that I have both DepartmentProficiencies and ProficienciesCompleted is that all of the ProficienciesCompleted for an employee within a given department have one date associated with them. There are maybe 10-15 different proficiencies they pass or fail, but all of those 10-15 happen on the same date. Should I have the same date copied 10-15 times in each record?

Thanks!

 

[MarkK]

I would not add a whole table to keep track of one dimension (field) of data, no. See how your ProficienciesCompleted table must support a foreign key, DepProfID, in order to link back to DepartmentProficiencies in order to get the value in one field? Well, just replace that foreign key with the actual data, and save yourself the overhead.

My 2c,