Query problem with duplicate entries

[rasher]

I have a skill/certification/training input system that revolves around one form which inputs any combination of a skill/certification or training that an employee might have. It records this in an EmployeeDetails table in the following fields : EmployeeID, SkillID, CertificationID, and TrainingID.

My problem is that when data is entered, I allow the user to input any combination of skills, certifications and training, meaning that some records are completely full, some 2/3 full, and some 1/3 full (with just a skill, a skill and a training, etc). This leaves null values in the spaces where data was not entered.

Using this SQL,

SELECT [Names Query].Name, tblCertifications.Certification_Name, tblSkills.SkillName, tblTraining.Training_Name
FROM [Names Query] RIGHT JOIN ((([tblEmployeeDetails Query] LEFT JOIN tblCertifications ON [tblEmployeeDetails Query].Certification_ID = tblCertifications.Certification_ID) LEFT JOIN tblSkills ON [tblEmployeeDetails Query].Skill_ID = tblSkills.Skill_ID) LEFT JOIN tblTraining ON [tblEmployeeDetails Query].Training_ID = tblTraining.Training_ID) ON [Names Query].Employee_ID = [tblEmployeeDetails Query].Employee_ID;

I am able to create a query which pulls the actual names of each Skill, Cert, or training, as well as the employee name, and display it in a report.

However, the report, even with HideDuplicates and CanShrink set at Yes, still shows blank lines where, I guess, the null values from the table are present. I completely understand WHY Access won't let me eliminate those values, but I don't know what I should do to accomplish my goal, which is having a report that is easy to read. I figure if I can eliminate the empty spaces in the query, I can eliminate them in the report.

Any ideas?

 

[jzwp22]

You said that in each record you have a skill, a certification and training. Are all three items related to one another? For example, is the certification for the skill that is listed? Since you said that not all of the fields are filled in, it implies that the 3 are not related. If that is the case then they should be in separate tables

tblEmployeeSkills
-pkEmpSkillID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkSkillID foreign key to tblSkills

tblEmployeeCertifications
-pkEmpCertID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkCertID foreign key to tblCertifications

tblEmployeeTraining
-pkEmpTrainID primary key, autonumber
-fkEmpID foreign key to tblEmployees
-fkTrainingID foreign key to tblTraining

Sorry I didn't use use exact field names.

 

[rasher]

They are not related, only by the employee that has them.

Do you think I should normalize the EmployeeDetails table into what you said? I still want to be able to enter data all on one form, but I think I could do that with a combination of buttons and record sources.

Just to make sure you're thinking of the same report, I need to be able to query each employee and see all of the skills, certifications and training that they have. For example:

Employee Name:
Skill name - Certification Name - Training name
Accounts payable - CISSP - Microsoft Training
SQL - nothing - nothing

Whereas right now, it looks more like:

Employee Name:
Skill Name - Certification Name - Training name
Accounts Payable - CISSP - Microsoft Training
SQL - CISSP - Microsoft Training
Accounts payable - nothing - Microsoft Training
SQL - nothing - Microsoft Training
etc.

I know why it looks that way (because I'm entering values as a three-field transaction, regardless of null values), I just want to make sure that we're on the same page and you think that I should normalize the EmployeeDetails table.

 

[jzwp22]

I would definitely go with the normalized approach. You can use 1 main form with 3 subforms for the data entry. To give the form a more organized look, you can use a tab control and then have each subform on its own tab.

 

[rasher]

Thank you very much. I am building a test database to make sure that approach will allow me to create the report correctly before I implement it in the main system.

Logically, I think keeping track of them seperately will allow for the query, but we'll have to wait and find out.

Either way, thank you for your help and I will be sure to post the results of my re-design later today.

 

[jzwp22]

Attached is a quick sample database I put together.

 

[rasher]

That's exactly how I redesigned it, and it works!

I just need to figure out how I'm going to represent the entry on one form (too much of a pain to use seperate ones), but that's not a big deal. I'll probably use tabs or an unbound form that writes to the table.

Thanks alot!

 

[rasher]

Okay, so even with the redesign, when one employee has a different combination of skills, certifications and training, you end up with the same report issue where a person with 1 skill, two certifications and three types of training gives repeat values in the query where there would have been a blank space. For example:

Person 1 has SkillIDs 1, 2 and 3. They have TrainingID 4 and 5, and CertificationID 6. The "resume" query that pulls all of the information from each person gives this result:

EmployeeID - SkillID - TrainingID - CertificationID
1 1 4 6
1 1 5 6
1 2 4 6
1 2 5 6
1 3 4 6
1 3 5 6

That is with LEFT joins in SQL.

Now the OLD way that I had it (using the EmployeeDetails fact table) generates the correct data and is easy to query, but the problem is that the blank values (that would be where the repetitions are in the above table) stay there when you go to report, making the report be formatted incorrectly.

Even with HideDuplicates and CanShrink, Access doesn't make the lines add up. I know this is all confusing but I know someone has had this problem before.

 

[jzwp22]

Did you check out the report rptEmployees in the database I attached earlier?

 

[rasher]

That helped alot! I was able to create subreports for each one using your format, and it works great! Thanks so much.

I guess I was just thinking too far into it.

 

[jzwp22]

You're welcome. Glad we could help out.