Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    88

    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:

    Code:
    Fred  never
    Mary  never
    Bill  2009-06-15
    John  2009-09-22
    Tony  2010-02-02
    TIA!

    Ceejay

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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:
    Code:
    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];
    Good Luck
    Jerry
    Attached Files Attached Files

  3. #3
    Join Date
    Mar 2010
    Posts
    88
    Jerry,

    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:

    Code:
    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.

    This is frustratingly close! Any clues for me?

    TIA
    Ceejay

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Ceejay, I struggle too, but never give up.

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

  5. #5
    Join Date
    Mar 2010
    Posts
    88
    Hi Jerry

    Thanks! It works!

    I think the "null" problem was to do with type-matching - I was doing some stuff with dates I probably shouldn't have. Did it another way and it all works - I now have exactly the query I wanted.

    Much appreciated.

    Ceejay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •