G
Guest
I’ve created the tables below. The EmployeeLookup table has a list of the
employees in a department. It is used in a combo/box to drop down and fill a
field when creating a record. The same is true with the ProjectsLookup table.
The rest of the information, WeekNo and HoursDedicated are entered by the
employee. The record is created in ProjectsDetails table.
I’m trying to build a query that will place in order by weeks (ascending),
the employee name (ascending) with a list of the projects (ascending) the
employee worked during the week, the sum of the hours worked, and a
percentage on a per project per the total hours of the week dedicated.
Example:
Week No Employee Name Project Name Hours Sum of Hours Week Percentage
1 Joe Leon Project_1 8
18.6
1 Joe Leon Project_2 4
9.3
1 Joe Leon Project_3 12
27.9
1 Joe Leon Project_4 9
20.9
1 Joe Leon Project_5 6
14.0
1 Joe Leon Project_6 4 43
9.3
2 Joe Leon Project_1 12
27.9
2 Joe Leon Project_2 8
18.6
2 Joe Leon Project_3 4
9.3
2 Joe Leon Project_4 4
9.3
2 Joe Leon Project_5 9
20.9
2 Joe Leon Project_6 6 43
14.0
EmployeeLookup: Table
EmployeeLookupID Text (Primary Key)
ProjectsLookupID Number
EmployeeLookup Text
Employees: Table
EmployeeID AutoNumber (Primary Key)
EmployeeLookup Text
EmployeeLookupID Text
Projects: Table
ProjectsID AutoNumber (Primary Key)
EmployeeID Number
ProjectsLookupID Number
ProjectDescription Memo
ProjectsDetails: Table
ProjectsDetail AutoNumber (Primary Key)
EmployeeID Number
ProjectsID Number
EmployeeLookupID Text
ProjectsLookupID Number
WeekNo Number
HoursDecicated Number
PercentTimeDedicated Number
ProjectsLookup: Table
ProjectsLookupID AutoNumber (Primary Key)
ProjectsLookup Text
ProjectDescription Memo
WeekDetails: Table
WeekDetailsID AutoNumber (Primary Key)
WeekNo Number
EmployeeID Number
ProjectsID Number
EmployeeLookupID Text
ProjectsLookupID Number
HoursDecicated Number
employees in a department. It is used in a combo/box to drop down and fill a
field when creating a record. The same is true with the ProjectsLookup table.
The rest of the information, WeekNo and HoursDedicated are entered by the
employee. The record is created in ProjectsDetails table.
I’m trying to build a query that will place in order by weeks (ascending),
the employee name (ascending) with a list of the projects (ascending) the
employee worked during the week, the sum of the hours worked, and a
percentage on a per project per the total hours of the week dedicated.
Example:
Week No Employee Name Project Name Hours Sum of Hours Week Percentage
1 Joe Leon Project_1 8
18.6
1 Joe Leon Project_2 4
9.3
1 Joe Leon Project_3 12
27.9
1 Joe Leon Project_4 9
20.9
1 Joe Leon Project_5 6
14.0
1 Joe Leon Project_6 4 43
9.3
2 Joe Leon Project_1 12
27.9
2 Joe Leon Project_2 8
18.6
2 Joe Leon Project_3 4
9.3
2 Joe Leon Project_4 4
9.3
2 Joe Leon Project_5 9
20.9
2 Joe Leon Project_6 6 43
14.0
EmployeeLookup: Table
EmployeeLookupID Text (Primary Key)
ProjectsLookupID Number
EmployeeLookup Text
Employees: Table
EmployeeID AutoNumber (Primary Key)
EmployeeLookup Text
EmployeeLookupID Text
Projects: Table
ProjectsID AutoNumber (Primary Key)
EmployeeID Number
ProjectsLookupID Number
ProjectDescription Memo
ProjectsDetails: Table
ProjectsDetail AutoNumber (Primary Key)
EmployeeID Number
ProjectsID Number
EmployeeLookupID Text
ProjectsLookupID Number
WeekNo Number
HoursDecicated Number
PercentTimeDedicated Number
ProjectsLookup: Table
ProjectsLookupID AutoNumber (Primary Key)
ProjectsLookup Text
ProjectDescription Memo
WeekDetails: Table
WeekDetailsID AutoNumber (Primary Key)
WeekNo Number
EmployeeID Number
ProjectsID Number
EmployeeLookupID Text
ProjectsLookupID Number
HoursDecicated Number