Replies

Here is the stripped down version of the file. Click the 2007 button and then check the tblCompetencyResultYear table you will see that Emily Allen has Pressure Ulcer class listed twice, even though she only did it once. If you look at the strSQL query I put the SQL in question on a query grid so you could see the basics. Thank you for your help. This is driving me crazy.

Fay.

Thanks that was what I figured you would say. Fay

Hi Jerry thanks for responding. I am so shaky with SQL as it is. So trying to learn another version/approach is more than I have time for with my other responsibilities. But I do appreciate your response. Fay

Got the above problem working. Thank you very much. I now want to covert several queries to SQL, which I did, but… Two of them are select queries which the computer quickly responded with a Run-time error 2342. So I now know I can’t run a straight Select query, I need an action query. Here are the three different queries that I was trying to run. Because of the 2342 error message I tried to combine them, but that isn’t going to work. Because of trying to call strSQL2 in the strSQL3 query. Can you provide an approach? Other wise I will remain with the query grid and continue to clutter the database with queries. Thank you. Fay

strSQL1 = “SELECT tblClasses.ClassID, tblLearners.LearnerID FROM tblClasses, tblLearners ” & _
“WHERE (((tblClasses.ClassID)=116 Or (tblClasses.ClassID)=118 Or (tblClasses.ClassID)=123 ” & _
“Or (tblClasses.ClassID)=126 Or (tblClasses.ClassID)=133 Or (tblClasses.ClassID)=134 ” & _
“Or (tblClasses.ClassID)=135)) ”

strSQL2 = “SELECT tblLearners.LearnerID, tblLearners.LastName, tblLearners.Nickname, ” & _
“tblLearners.Inactive, tblLearners.Credential, tblLearnerDepartments.PerDiem2Unit, ” & _
“tblLearnerDepartments.Status, tblLearnerDepartments.StartDate ” & _
“FROM tblLearners INNER JOIN tblLearnerDepartments ” & _
“ON tblLearners.LearnerID = tblLearnerDepartments.LearnerID ” & _
“WHERE (((tblLearners.Inactive)=0) AND ((tblLearnerDepartments.StartDate)<#1/1/2007#)) "

strSQL3 = "SELECT strSQL1.LearnerID, strSQL1.ClassID, " & _
"strSQL2.LastName, strSQL2.Nickname, strSQL2.Inactive, strSQL2.Credential, " & _
"strSQL2.Status, tblClasses.ClassName, tblCompetencyResultYear.ISDateOfClassStart, " & _
"tblCompetencyResultYear.Grade, tblCompetencyResultYear.ProficiencyGrade " & _
"INTO tblCompetencyResultYear1 " & _
"FROM ((qryCompetenciesResult LEFT JOIN tblCompetencyResultYear " & _
"ON (qryCompetenciesResult.LearnerID = tblCompetencyResultYear.LearnerID) " & _
"AND (qryCompetenciesResult.ClassID = tblCompetencyResultYear.ClassID)) " & _
"INNER JOIN tblClasses ON qryCompetenciesResult.ClassID = tblClasses.ClassID) " & _
"INNER JOIN qryCompetencyResultsA ON qryCompetenciesResult.LearnerID = qryCompetencyResultsA.LearnerID " & _
"WHERE (((qryCompetencyResultsA.Inactive)=0) AND ((qryCompetencyResultsA.Credential)=""RN"" " & _
"Or (qryCompetencyResultsA.Credential)=""LPN""));"

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

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

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”));

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

You were correct. Thank you. Fay

I will check tomorrow I am off site the rest of today. It is a possibility because the Control Toolbox has been on screen. Thanks. Fay

Here is the form. I am currently working with it on my laptop and it works. But the desktop doesn’t give me access to the protect button. Thanks for looking at it.

From the Forms toolbar.

Thank you. Fay

I was kind of expecting some kind of answer like that. Back to the drawing board. At least I am just getting started with it. How are you envisioning the combo number for department and credentials. Create a separate table where I create the numbers for the department/credential combinations. Then link it to where I input learners. Is what I am hearing. Thanks for the information. Fay

Here is a stripped down version. It will come up showing the form where I input the different competencies and then assign to the department and to the credential levels. I started the query qryCompetenciesRequired, but it just selects the departments and names so far.

I need to show by departments (tblLearnerDepartment) assigned staff by name (tblLearner). Showing required competencies for each person based on their department and and credential level. Eventually I will then need to show their grade and date tested, but I need help on the step above.

tblCompetenciesDept holds the information from the subform.
tblRegIndepStudyLearner really addresses the last step, so I guess not needed here.

Looking at the form you see that required for a credential level are marked with a check mark. If you look at the tblLearnerDept you will see that credentials is in a text field. I have to marry up the these two a yes for an RN should show up under all RN names.

I hope this helps. Thank you for looking at this. Fay