Query data not matching

[shosim85]

Hi,

I have a table that holds course information for students. I have a parameter query linked to this prompting the user to enter a course code thus displaying all students who have that course. However, I would like to do the opposite i.e prompt the user to enter a course code and then the result to be a list of students who do not have that course.

Any ideas please? :banghead:

Thanks

 

[CJ_London]

Can you post your current query - you probably only need to make a simple change to do what you require.

Better than us trying to guess

 

[shosim85]

Hi,

My query is this:

SELECT Staff.FullName, [Course Title ID].CourseTitle AS Title, UnionCourseCode.CCode, Course.CourseExpiry
FROM UnionCourseCode INNER JOIN (CourseCodeID INNER JOIN ([Course Title ID] INNER JOIN (Staff INNER JOIN Course ON Staff.FullName = Course.FullName) ON [Course Title ID].CourseTitleID = Course.CourseTitleID) ON CourseCodeID.CourseCodeID = Course.CourseCodeID) ON UnionCourseCode.CCode = CourseCodeID.CourseCode
WHERE (((UnionCourseCode.CCode)=[Please enter Course Code]) AND ((Course.CourseExpiry)>=Date()) AND ((Staff.LeavingDate) Is Null));
UNION ALL
SELECT Staff.FullName, [Small Tools ID].SmallTool AS Title, UnionCourseCode.CCode AS Code, SmallTools.SmallToolsExpiry
FROM UnionCourseCode INNER JOIN (Staff INNER JOIN ([Small Tools ID] INNER JOIN (SmallCodeID INNER JOIN SmallTools ON SmallCodeID.SmallCodeID = SmallTools.SmallToolsCodeID) ON [Small Tools ID].SmallToolsID = SmallTools.SmallToolsID) ON Staff.FullName = SmallTools.FullName) ON UnionCourseCode.CCode = SmallCodeID.SmallCode
WHERE (((UnionCourseCode.CCode)=[Please enter Course Code]) AND ((SmallTools.SmallToolsExpiry)>=Date()) AND ((Staff.LeavingDate) Is Null));

Hope it makes sense!

 

[CJ_London]

This seems to be two queries? Not sure what the ';' is doing just before the UNION ALL

 

[CJ_London]

Hi this give the answer to the first part of your union query - you should be able to apply the principle to the second part. Note that it will only return the Staff.fullname value since by definition the course details will be null

SELECT Staff.FullName
FROM Staff LEFT JOIN (SELECT Course.FullName, CourseCodeID.CourseCode FROM (Course LEFT JOIN CourseCodeID ON Course.CourseCodeID = CourseCodeID.CourseCodeID) LEFT JOIN [Course Title ID] ON Course.CourseTitleID = [Course Title ID].CourseTitleID WHERE (((Course.CourseExpiry)>=Date()) AND ((CourseCodeID.CourseCode)=[Please enter Course Code]))) AS C ON Staff.FullName = C.FullName
WHERE (((C.FullName) Is Null) AND ((Staff.LeavingDate) Is Null));

 

[shosim85]

Thanks CJ. I don't know how you worked that out but it works and I'm happy!

Thank you.

shosim85

 

[CJ_London]

Lots of experience Glad to help

 

[shosim85]

Thanks CJ,

Could you please help to do the same with this:

SELECT CourseInformationDoncaster.[Full Name], CourseInformationDoncaster.[Course Title], CourseInformationDoncaster.CourseCode, CourseInformationDoncaster.ExpiryDate
FROM UnionDonnyCodes INNER JOIN (Doncaster INNER JOIN CourseInformationDoncaster ON Doncaster.[Full Name] = CourseInformationDoncaster.[Full Name]) ON UnionDonnyCodes.DCode = CourseInformationDoncaster.CourseCode
WHERE (((Doncaster.[Leaving Date]) Is Null) AND ((Format([DCode],"mm/yy"))=[Please enter course code]))
UNION SELECT DonnySmalls.[Full Name], DonnySmalls.[Small Tools], DonnySmalls.SmallCode, DonnySmalls.SmallExpiry
FROM UnionDonnyCodes INNER JOIN (Doncaster INNER JOIN DonnySmalls ON Doncaster.[Full Name] = DonnySmalls.[Full Name]) ON UnionDonnyCodes.DCode = DonnySmalls.SmallCode
WHERE (((Doncaster.[Leaving Date]) Is Null) AND ((Format([DCode],"mm/yy"))=[Please enter course code]));

Please let me know the steps to working this out so I can try myself.

Thanks

shosim85

 

[CJ_London]

Glad you are prepared to give it a go - best way to learn

The two things that are relevant in this particular situation are Left Joins and Sub Queries.

We'll look at the first part of the union query again. The object of the exercise is to list employees who are not on a course.

The way to do this is to have a query between the Staff table and 'the other table' where there are records in the staff table, but not in the other table.

We'll look at the left join first

So first you create a left join which means that all the records will appear in the staff table and only those records in the other table where there is a match on the link - otherwise it returns null from the other table.

So if we only want to see staff who are not on a course then the field value in the other table is null - doesn't matter which field you use in the other table - they will all be null.

To get a left join in the query builder, create a relevant join between two tables (drawing from the Staff table) then right click on the line and follow your nose.

Note there are some limitations to left joins in more complex queries as you will find if you follow the above in your existing query. So to solve this we make use of a sub query.

Sub queries come in a number of forms - they can be used in place of a dlookup, dmax etc function (they are usually significantly faster), used as a criteria and in this case combine tables into a sub query that is used as another table which is very similar to having chained queries.

You cannot build these in the query builder and have to use SQL, but there is a fairly simple technique you can use.

From your initial query in the query builder remove the Staff table which will also eliminate the staff fields used in the query - you'll put them back later

Next go into SQL view and before the word SELECT type the following:
SELECT * FROM (

Then go to the end of the query, remove the semi colon and type
) AS C

You query is now surrounded by brackets

Now go back to the query builder and you will see you now have a table called C.

However what is often missing is the field you need to link to the Staff table - In this case Doncaster.[Full Name] so go back to the SQL and either edit the code or remove the additional bit you've just added, return to the query builder add it in then back to SQL view and add the top and tails.

Once done, go back to the query builder, add back your Staff table, do a left join to the sub query and add another criteria where somefield from the subquery is null - And don't forget to add back the leaving date criteria.

OK, that's the principle - go practice

PS, since you are only trying to determine sufficient information to determine the course, you may find you can simplify your subquery and drop a table or two - I did in the previous solution.