Query, question (sorry about that) (Access 03)

Topic

New Reply

I have the following query and so far it does what I want. But now I want a line for each of the classes listed even if there is no results. That way I can find when a test isn’t taken. I tried “Is Null AND Is Not Null” and it didn’t work. Thank you. Fay

SELECT tblLearnerDepartments.PerDiem2Unit, tblRegIndepStudyLearner.LearnerID, tblLearners.LastName, tblLearners.Nickname, tblLearners.Credential, tblClasses.ClassName, tblRegIndepStudyLearner.ISDateOfClassStart, tblRegIndepStudyLearner.Grade, tblRegIndepStudyLearner.ProficiencyGrade, tblRegIndepStudyLearner.ProficiencyMethod

FROM ((tblClasses INNER JOIN tblRegIndepStudyLearner ON tblClasses.ClassID = tblRegIndepStudyLearner.ClassID) INNER JOIN tblLearners ON tblRegIndepStudyLearner.LearnerID = tblLearners.LearnerID) INNER JOIN tblLearnerDepartments ON tblLearners.LearnerID = tblLearnerDepartments.LearnerID

WHERE (((tblClasses.ClassName)=”Pressure Ulcer – RN/LPNs – 2007″) AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#)) OR (((tblClasses.ClassName)=”Accu-chek – 2007″) AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#)) OR (((tblClasses.ClassName)=”IM Injection Test”) AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#)) OR (((tblClasses.ClassName)=”Venipuncture Test and Proficiency”) AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/20/2006# And #1/1/2008#)) OR (((tblClasses.ClassName)=”Perdiem Packet – 2007″) AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #3/7/2007# And #1/1/2008#)) OR (((tblClasses.ClassName)=”Safety Fair Make-up”) AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#))

ORDER BY tblLearnerDepartments.PerDiem2Unit, tblLearners.LastName, tblLearners.Nickname, tblClasses.ClassName;

Reply | Quote

Replies

Have you tried a union query?

Reply | Quote

You could try the folllowing:

1) Add the ClassID field from tblClasses to the query.
2) Create a new query based on this one and on tblClasses.
3) Join them on ClassID.
4) Double click the join line and select the option to return *all* records from tblClasses.
5) Add ClassID and ClassName from tblClasses to the query grid, and the other fields from the “old” query except ClassID and ClassName.

Reply | Quote

Thank you Hans. This will get the job done.

I have not made major changes to this database for a long time and I think I lose all skills I have achieved from previous work. I appreciate the patience. Fay

Reply | Quote

This is basically giving me the same results I had with the original query. If there are five tests I need each person’s name to show up and the five tests to be listed. If they have taken the test then there should be a grade and date. If they haven’t taken the test the test name is there but there is no grade. Thank you. Fay

SELECT tblClasses.ClassID, qryCompetencyResults1.PerDiem2Unit, qryCompetencyResults1.LearnerID, qryCompetencyResults1.LastName, qryCompetencyResults1.Nickname, qryCompetencyResults1.Credential, qryCompetencyResults1.Status, tblClasses.ClassName, qryCompetencyResults1.ISDateOfClassStart, qryCompetencyResults1.Grade, qryCompetencyResults1.ProficiencyGrade, qryCompetencyResults1.ProficiencyMethod
FROM tblClasses LEFT JOIN qryCompetencyResults1 ON tblClasses.ClassID = qryCompetencyResults1.ClassID
WHERE (((tblClasses.ClassName)=”Accu-Chek – 2007″)) OR (((tblClasses.ClassName)=”Code BLue – 2007″)) OR (((tblClasses.ClassName)=”IM Injection Test”)) OR (((tblClasses.ClassName)=”Perdiem Packet – 2007″)) OR (((tblClasses.ClassName)=”Pressure Ulcer – RN/LPNs – 2007″)) OR (((tblClasses.ClassName)=”Safety Fair Make-up”)) OR (((tblClasses.ClassName)=”Venipuncture Test and Proficiency”));

Reply | Quote

I’d create a query qryCombinations based on tblClasses and tblLearners, with ClassID and LearnerID. Don’t join the tables. This query returns all possible combinations of a person and a class.
Next, create a query based in this one and on the tables or queries you need. Join qryCombinations to tblRegIndepStudyLearner on ClassID and LearnerID. Double click each of the join lines and select the option to return all records from qryCombinations.

I have attached a simplified example.

Reply | Quote

Hi Hans. Thanks for the help. I got the basics to work see qryCompetencyResults1. The problem is that I need to put limitations on the results based on status and date of class, see qryCompetencyResults1a, it doesn’t work. Any thoughts? I took qryCompetencyResults1 and created another query and then put the limitations there. That didn’t work either, it limited to just the people that had completed the tests. see qryCompetencyResults1b.

See the attached file.

Thank you. Fay

Reply | Quote

After playing with this all evening, I think I figured it out. I have to create a table. The make table query dumps the registrations files based on the limitations I set in the example in qryCompetencyResults1b. Then do what you told me to do from that new table.

Thank you. Fay

Reply | Quote

It could probably be done entirely with queries, but a temporary table may well be the simplest solution, since the conditions are rather confusing.

Reply | Quote