Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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;

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    Have you tried a union query?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •