Trouble Designing a Query

[marshymell0]

Hi,

I currently have an access database set up. It's pretty simple, 3 fields/columns:

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 if 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.

[June7]

Sounds like a GROUP BY (Totals) aggregate query. In query designer, click the Totals button on the ribbon. Access Help has guidelines on designing this query.

The resulting SQL statement would be like:

SELECT [Employee Name], Sum(Hours) As SumHours FROM tablename GROUP BY [Employee Name] HAVING Sum(Hours) < 20;

If you want to show detail records (such as the department IDs), then build a report. Use report Grouping & Sorting with aggregate calcs in group footers. Again, Access Help has guidelines for building this report.

[marshymell0]

Sounds like a GROUP BY (Totals) aggregate query. In query designer, click the Totals button on the ribbon. Access Help has guidelines on designing this query.

The resulting SQL statement would be like:

SELECT [Employee Name], Sum(Hours) As SumHours FROM tablename GROUP BY [Employee Name] HAVING Sum(Hours) < 20;

If you want to show detail records (such as the department IDs), then build a report. Use report Grouping & Sorting with aggregate calcs in group footers. Again, Access Help has guidelines for building this report.

Hi,

Thanks for the help. I was able to use your second solution to build the report but I am unable to filter by the total hours calculated (it is greyed out) when I try to filter by the total, but it works if I want to filter by the hours that make up that total. I have attached a screenshot.

[June7]

That report looks like the RecordSource is GROUP BY query, not the detail query. If it is GROUP BY, did you include the filter criteria? Instead of HAVING I should have used WHERE.

Two options to filter report showing detail:

1. multiple queries

Query1
SELECT EmployeeName, Sum(Hours) AS SumHours
FROM Table1
GROUP BY EmployeeName;

Query2 (RecordSource for the report)
SELECT Table1.Department, Table1.EmployeeName, Table1.Hours, Query1.SumHours
FROM Table1 INNER JOIN Query1 ON Table1.EmployeeName = Query1.EmployeeName
WHERE (((Query1.SumHours)<20));

The above 2 as an all in one (RecordSource for the report)
SELECT Table1.Department, Table1.EmployeeName, Table1.Hours, Query1.SumHours
FROM Table1 INNER JOIN
(SELECT EmployeeName, Sum(Hours) AS SumHours
FROM Table1
GROUP BY EmployeeName) As Query1 ON Table1.EmployeeName = Query1.EmployeeName
WHERE Query1.SumHours<20);

You might find this of interest http://allenbrowne.com/subquery-01.html

2. create field in query with DSum expression and apply <20 criteria to that constructed field
SELECT * FROM tablename WHERE DSum("Hours","tablename","EmployeeName='" & [EmployeeName] & "'")<20

The alternative is just to report all and let the reader overlook the groups that exceed 20.

[marshymell0]

I tried to export the table to Excel and filter on there...but when I export the Report, Excel treats it all weird (it starts to inproperly group rows together/doesn't group the name)

[June7]

True, export of reports not always nice.