Access – fltering records (SR97 )

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

In(1,3,5)

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote