Sorting and formatting a report based off of values and user input? (1 Viewer)

[pheidlauf]

Hello. I'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables (as I'm still in the design phase and am not experienced in forms or reports). Here's a quick outline of my general data organization:

Department:
DepartmentID(PK)
DepartmentName

Employee:
EmployeeID(PK)
EmployeeName
DepartmentID(FK)
Needs_Work (check box)

Competency:
CompetencyID(PK)
CompetencyName
DepartmentID(FK)

CompetenciesCompleted:
CompetencyID(PK)
EmployeeID(FK)
Pass (True/False check box)

Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked (a percentage would be great).

How can I apply those sorts within the report?
Thanks for any help!

 

[rzw0wr]

Change your tables a little as follows.
An employee can work in many departments bur they can only be assigned to 1 time to any department.
I assume the competency is about an employee and not a department.

Department:
DepartmentID(PK) autonumber
DepartmentName
EmployeeID (FK)

Employee:
EmployeeID(PK) autonumber
EmployeeName
Needs_Work (check box)

Competency:
CompetencyID(PK) autonumber
CompetencyName
Employee (FK)

CompCompleted:
CompCompletedID(PK) autonumber
Competency_ID(FK)
Pass (True/False check box)

Dale

 

[pheidlauf]

Actually, in the structure I'm trying to model, an employee can work for multiple departments in the same day depending on the needs of the plant. Some of the competencies are general and standardized throughout all departments, such as safety training, but some are specific to departments (like cleaning or inspection). Not all employees are in multiple departments, though a sizable amount are. How would you suggest handling this?