Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    I have a table containing employee job records that includes a JobDate field. I have a query that lists employees whose last job was more than 14 days ago. The query includes the JobDate field sorted ascending with "Max" in the total row (to show the last job date) and "<Date()-14" in the criteria row (to select the required employees), and a "Days since last job" field with the expression "Date()-[MaxOfJobDate]". This works for employees whose JobDate field is not null but overlooks recent employees whose JobDate field is null because they have not yet done any jobs. How can I include these - do I need to use something like the Nz function somewhere?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Can you post a screen capture of the query design grid for this query?

    From your description, you are only selecting employees where it is exactly 14 days since the last job. Is that right?
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply - sorry, the description in my original query was wrong; I have now edited it.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I suggested a screen capture because I thought it might help avoid further questions.

    Is there an employees table and a job records table? or just one table?

    I am still not clear. Won't there be three groups of employees under the revised query.
    • last work record more 14 days old - returned by query
    • last work record less than 14 days old - not returned
    • no work records - not returned.
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Sorry, I may not have provided enough detail. Here is a screen image of the design grid. There are actually three date fields on each job record: AssignedDate, AcceptedDate and CompletedDate. Yes, there are separate employee and job tables, but the problem is not that some employees may have no job records (I understand that in that case, I would need a pre-query to list all employees and then join that to the job table in the main query), as here I am only concerned with employees who do have job records. Rather, the problem is that some employees may have jobs with values in the AssignedDate and AcceptedDate fields but no values in the CompletedDate field with the query criterion (e.g., they have not accepted any jobs or they have accepted some but not completed any), and I want to include these employees in this list. In other words, it should be a list of employees whose last completed job was more than 14 days ago *or* who have no completed jobs (and possibly no accepted jobs).
    Attached Images Attached Images

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    On the left hand side of the query grid, under the word Criteria you see the word OR. So you can put muliple criteria on different lines and they provide alternative criteria. NB The OR does not act on just one field, All the criteria on one line are one set, those on another line are another set.

    So if you put Null under JobCompletedDateTime that would add employee where Max(CompletedDateTime) is null. I am not sure what the criteria for AcceptedJobDateTime would be?
    You could have a third line where AcceptedJobDatetime is itself null.
    Regards
    John



  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Of course - yes that works, thanks.

Posting Permissions

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