AND Query

[snoko]

I have a small problem with a query. Example, I have three tables, tblStaff, tblCourse and the joining table tblCourseAttend.

I can return records showing which staff have done either course 1 OR course 2 OR course 3 etc but I can't seem to return which staff have done course 1 AND course 2 AND Course 3.

I'm using a form with a list box to list all the courses, the users then selects the courses they require from the list and a commamd button will produce a report listing the staff who have attended.

The OR functionality works fine but I can't get the AND aspect to work.
The users can select any number of courses to query.

Anybody help on this one?

 

[Mile-O]

Sorry, don't have my psychic hat on today.

Can you post your SQL?

 

[snoko]

No sorry it's on a seperate system. All I want to do is return everyone that has completed a set of courses. Not including staff who have only done some of the courses.

 

[KenHigg]

No sorry it's on a seperate system...

 

[stephen81]

I have quite a messy solution and I'm sure someone will come up with a better solution.

You need two queries and an extra field (CourseValue) in tblCourses.

tblCourses
CourseID - PK
CourseName
CourseValue

tblStaff
StaffID - pk
Name

tblCourseAttend
CourseID
StaffID

The idea is that you assign a value to each course. You could just count the courses each person has attended but that would only allow you to pick people that had attended for example two out of four courses etc but not a specific two courses out of 4. If you assign a different value to each course, you can pick which courses you want to include in your AND query.
eg

Course Value
Course1 10
Course2 5
Course3 2.5
Course4 1.25

By assigning the values like this, you can sum the values for any particular person and work out from that which courses they've attended.
So for example, if you wanted to list the people that have attended all four courses, you simply write a query that sums the values for each person and checks if the sum equals 18.75. You can identify any combination of courses with a different number.

There is a problem with this however, in that if one person has done a specific course more than once, their total will obviously be more than the sum you're looking for. To get around this, you can have one query as follows

SELECT DISTINCTROW tblStaff.name, tblCourse.Value
FROM tblStaff RIGHT JOIN (tblCourse RIGHT JOIN tblCourseAttend ON tblCourse.ID = tblCourseAttend.course) ON tblStaff.ID = tblCourseAttend.staff;

(the DISTINCTROW will only return one instance of a course for each person)

you can then base another query on this query to sum the values and apply your criteria

SELECT name, Sum(Value) AS SumOfValue
FROM Query1
GROUP BY Query1.name
HAVING (((Sum(Query1.Value))=18.75));

 

[stephen81]

There is a problem with this however, in that if one person has done a specific course more than once, their total will obviously be more than the sum you're looking for.

The other problem I realised as I was typing that is that if you want to return everyone who has completed for example two specific courses out of four, anyone who has completed the two specific courses as well as some others, won't be returned. You may be able to get around that by revising the values you give the courses or something similar, but I'm going home now so won't have time to look at it any further today.

I look forward to seeing a much more elegant and efficient solution that I'm sure will get posted soon.

 

[Pat Hartman]

Create a regular select query with the OR criteria:
Select StaffID
From tblStaff Inner Join tblCourseAttend on tblStaff.StaffID = tblCourseAttend.StaffID
Where x In("Class1", "Class2", "Class3");

Then create a totals query that counts the number of class instances for an employee.

Select StaffID, Count(*) As ClassCount
From Query1
Having Count(*) = 3;

Then you can take this query and join it back to the Staff table to obtain other information about the employee.

 

[snoko]

Thanks Pat I knew you'd have the answer. Is there any way of linking the count query back to the CourseAttended Table to return the Course Titles and Dates attended.

If not I could just put the selection criteria ie course titles at the top of the report.

Thanks again

Snoko

 

[Pat Hartman]

Then you can take this query and join it back to the Staff table to obtain other information about the employee.

- or you could join it back to the tblCourseAttend and tblCourse to get course data.

BTW, you should have an AttendDt in the relation table and also a grade if the courses are graded or maybe just a complete/incomplete flag.

 

[snoko]

Cheers Pat,

I've got it all the logic sorted now - excellent.
BTW, I've got a Start date and a Finish date. The finish date also being the completed flag.

Should now be able to create variables to assign the course codes and pass them into a SQL string to return all the courses attended dynamically using either the 'OR' or 'AND' operators from the list box on the form.

Many thanks for the assistance.

Snoko