Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access - fltering records (SR97 )

    Good morning!

    I am working on a project and I am having some problems. The main problem is my logic is not working.

    I have a database that contains employee information and all courses they have taken over a period of 5 years. I have a need to extract on the employees who HAVE NOT completed 3 required classes within a certain date range.

    I can get the people who have compeleted it but who do not fall within the time frame so they are delinquent. The problem is getting the people who have never taken the class. They don't have a record.

    Can anyone give me some suggestions? It really doesn's sound that difficult. Every person has to have 3 records, if not display the record they are missing and give a projected date to complete.

    Thanks,
    Deborah

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access - fltering records (SR97 )

    You need two queries.

    The first query returns all employees who have completed 3 courses within the specified date range - this is a Totals query that groups on the employee and counts the number of courses. Criteria are that the completion date falls within the specified date range, and that the count is at least 3. Save this query.

    The second query can be created using the Find Unmatched Query Wizard - specify the employee table in the first step, and the query you just saved in the second step.

    See attached very simple example, if necessary.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - fltering records (SR97 )

    I do understand your logic and the sample was perfect. The problem I am having is that an employee could have 50 classes and I am looking specifically for 3 of them by title. I can pull the ones who have the title but I can find the ones who have never taken the course or they took the course last year and not this year.

    I really think I am making this harder than I should but my queries are not working.

    Deborah

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access - fltering records (SR97 )

    In the example I posted, you can add an extra condition to qryPass. Let's say that you want to look at courses # 1, 3 and 5.
    Add a second instance of the CourseID field to the query grid, set the Total option to Where, and set the first Criteria line to

    <code>In(1,3,5)</code>

    qryPass will now only return the employees who have completed 3 specified courses within the time range. qryFail will return all other employees, i.e. those who didn't complete any courses, or the wrong combination of courses, or the correct ones but not within the time range, etc.

    (Modified version attached)

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - fltering records (SR97 )

    Ok. This is super. One more question, how can I display the courses they did not take?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access - fltering records (SR97 )

    Do you mean among the required courses, or among all courses?

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - fltering records (SR97 )

    Just the required classes. If they missed one of the required training (3 classes) or the training was not in the required date range, how do I print out a report with the name of the course they are missing.

    I really appreciate your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access - fltering records (SR97 )

    I have added another two queries:

    qryCompleted lists all required courses completed within the time range.
    qryMissed lists all required courses NOT completed within the time range.

    Note: I added a Yes/No field Required to the courses table, that makes it slightly easier to specift required courses.

Posting Permissions

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