Unanswered: building a complex query in Access 2007
Hi folks - database noob here, please be patient! I'm building my first "real" Access 2007 application and I'm stuck. Because I'm new, I have no idea whether I am missing something simple, or trying to do something really hard or even impossible. I don't really know what to search for, either. Experienced words would be very welcome!
The application is essentially about managing people doing shifts. My two main tables, surprisingly, are "people" (person ID plus sundry attributes) and "shifts" (date, time, type and person ID).
I've got a number of queries, forms and reports which are working fine, but I'm stuck on a particular query I want to make... I want a list of people who are eligible to do a particular type of shift, together with the last date they did one (may be never).
The first part is easy - just a select query on my people table with the right criteria and there it is, a list of people eligible to do that type of shift.
Then it gets harder: I have a select query on the "shifts" table which gives me a list of all the shifts of that type together with who did it. This is ok, but of course a particular person may have done that shift several times, so how do I find the latest one for each person? Also, the people in this query aren't necessarily the same as the ones in the first ... there will be people in the first query who have never done that kind of shift before, and there will also be people in the second query who aren't valid any more (perhaps they've left).
So, any clues please? How can I combine these pieces of information to get the result I want? Something like:
Ceejay, the query in the attached database should help you in writing your query to display all employees, the type of their latest shift worked and the date worked, plus display "Never" if there is no record of a shift worked.
There are two tables, "Employees" and "Employee Shifts", and it is assumed that the Employees table contains every employee in the shifts table. In query builder, the two tables are joined by employee name (for simplification in my example) and the join properties are set to: "Include ALL records from 'Employees' and only those records from 'Employee Shifts' where the joined fields are equal."
Code from the query SQL view:
SELECT Employees.[Employee Name], Employees.Gender, Employees.[Date Hired], Max([Employee Shifts].[Date Worked]) AS [Last Date Worked], Max(IIf(Len(Nz([Shift]))=0,"Never",[Shift])) AS [Last Shift Worked]
FROM Employees LEFT JOIN [Employee Shifts] ON Employees.[Employee Name] = [Employee Shifts].[Employee Name]
GROUP BY Employees.[Employee Name],
Employees.Gender, Employees.[Date Hired];
Many thanks for taking the trouble to reply, I really appreciate it.
I downloaded your sample database and I can indeed make it work. It wasn't exactly what I wanted, but a few tweaks and I was there. Here is what I ended up with:
SELECT Employees.[Employee Name], Employees.Gender, Employees.[Date Hired], Max(GraveShifts.[Date Worked]) AS [Last Date Worked]
FROM Employees LEFT JOIN GraveShifts ON Employees.[Employee Name] = GraveShifts.[Employee Name]
GROUP BY Employees.[Employee Name], Employees.Gender, Employees.[Date Hired]
ORDER BY Max(GraveShifts.[Date Worked]);
"GraveShifts" is a new query that selects just the "Grave" shifts from the table ... this is because what I wanted was to see the latest shift of a specific type (not the latest shift and its type). I took the "never" code out to simplify, I can put this back later.
So I've tried to recreate the same query in my real database and I'm getting the message "Invalid use of Null". I'm really struggling to see the significant difference between the SQL in the simplified example which works and the real one which doesn't.
I've looked in the output of the base queries and there aren't any empty fields that I can see.
Verify that the fields you are linking in the query are the same data type.
Try putting Is Not Null in the first field of the query and run it. If it runs, remove that and do the same in the second field, and so on. If you identify the offending field with this test, run a group-by select query on this field and see if there is a Null in the list. If nulls are found, determine if your report will be accurate if you exclude the nulls.
Try filtering to get only sets of records, such as a date range. Do some sets of records run while some, or hopefully one set to narrow the search, do not? If only one set of dates fails, narrow the date range to zero in on the problem.
If the above does not identify the problem, try rebuilding the query and run it along each stage of the way.
If the query fails at a certain point, then try to figure out why "Invalid use of Null" pops up when a field is added to your query. Do a group-by select query on the field and see if the query results include a blank.
If you can identify the field, see if the Nz function corrects the problem. In your query grid you would rename the field, for example, like this:
Name of Employee: Nz([Employee Name])
Maybe someone else on the forum has some other troubleshooting tips.