marshymell0
New member
- Local time
- Today, 18:34
- Joined
- Dec 20, 2012
- Messages
- 3
Hi,
I currently have an access database set up linking a couple of tables. It's pretty simple, 3 main fields/columns I am interested in manipulating:
Field A: Department #
Field B: Employee Name
Field C: Hours
I would like to write a query where the number of hours is less than or equal to a certain number, say 20 hours.
Writing this query if Employee Name is a foreign Key would be fine (I would go to design view and put Criteria <20 under the Hours Field) but the problem I am having is when an employee (field B) can be in more than one department (field A).
I would like to write a query that shows all employees and their department that had TOTAL hours less than 20 hours.
eg.
Employee Jane works for Department A and Department B. She has worked 10 hours in Department A and 5 hours in Department B for a total of 15 hours. The query should show Department A and B, Jane, and the Total hours she has worked (15 hours).
Employee John works for Department A and Department B. He has worked 15 hours in Department A and 20 hours in Department B. Therefore his total hours worked is 35 hours, and thus he would NOT be shown in the resultant query table.
Employee Sam works for Department C. He has worked 20 hours. The query would show Sam, Department C, and 20 total hours worked.
What I then tried to do was create a REPORT (using the wizard) created off a query that linked a couple of tables. This worked out fine...as I was able to show exactly what I wanted...(the employee, then any departments he/she worked for, how many hours worked in that department, and then a total for that employee using the TOTAL button)...but now I am unable to filter to only show those totals under 20.
Anyone have any suggestions/help?
I currently have an access database set up linking a couple of tables. It's pretty simple, 3 main fields/columns I am interested in manipulating:
Field A: Department #
Field B: Employee Name
Field C: Hours
I would like to write a query where the number of hours is less than or equal to a certain number, say 20 hours.
Writing this query if Employee Name is a foreign Key would be fine (I would go to design view and put Criteria <20 under the Hours Field) but the problem I am having is when an employee (field B) can be in more than one department (field A).
I would like to write a query that shows all employees and their department that had TOTAL hours less than 20 hours.
eg.
Employee Jane works for Department A and Department B. She has worked 10 hours in Department A and 5 hours in Department B for a total of 15 hours. The query should show Department A and B, Jane, and the Total hours she has worked (15 hours).
Employee John works for Department A and Department B. He has worked 15 hours in Department A and 20 hours in Department B. Therefore his total hours worked is 35 hours, and thus he would NOT be shown in the resultant query table.
Employee Sam works for Department C. He has worked 20 hours. The query would show Sam, Department C, and 20 total hours worked.
What I then tried to do was create a REPORT (using the wizard) created off a query that linked a couple of tables. This worked out fine...as I was able to show exactly what I wanted...(the employee, then any departments he/she worked for, how many hours worked in that department, and then a total for that employee using the TOTAL button)...but now I am unable to filter to only show those totals under 20.
Anyone have any suggestions/help?
