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

    Seeing double (Access03)

    I have a situation where I am getting double results with sql code. I can't find the bug. I know I didn't runSQL for strSQL1 more than once, I checked using the Find function. But if the person took a class that class is showing up twice in the table the information is being inserted into. I used the same here that I did with the RN coding and I have no problem with the code for the RNs. Any help would be appreciated. I am sure it is a little nit or period in the wrong place. Thank you. Fay




    'SQL from append-table query to pull selected as base for Aide 2007 Competency Results
    strSQL1 = "INSERT INTO tblCompetencyResultYear ( LearnerID, ClassID, LastName, Nickname, Inactive, Credential, Status, ClassName, " & _
    "ISDateOfClassStart, Grade, ProficiencyGrade ) SELECT tblLearners.LearnerID, tblClasses.ClassID, tblLearners.LastName, tblLearners.Nickname, tblLearners.Inactive, " & _
    "tblLearners.Credential, tblLearnerDepartments.Status, tblClasses.ClassName, tblRegIndepStudyLearner.ISDateOfClassStart, " & _
    "tblRegIndepStudyLearner.Grade, tblRegIndepStudyLearner.ProficiencyGrade " & _
    "FROM tblClasses INNER JOIN ((tblLearners INNER JOIN tblLearnerDepartments " & _
    "ON tblLearners.LearnerID = tblLearnerDepartments.LearnerID) INNER JOIN tblRegIndepStudyLearner " & _
    "ON tblLearners.LearnerID = tblRegIndepStudyLearner.LearnerID) ON tblClasses.ClassID = tblRegIndepStudyLearner.ClassID " & _
    "WHERE (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)=""Nursing Assistant"") " & _
    "AND ((tblLearnerDepartments.Status)=""Perdiem"" Or (tblLearnerDepartments.Status)=""Perdiem Option"" " & _
    "Or (tblLearnerDepartments.Status) Like ""Inhouse*"") AND ((tblClasses.ClassName) Like ""Accu-chek*"") " & _
    "AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#) " & _
    "OR (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)=""Nursing Assistant"") " & _
    "AND ((tblClasses.ClassName) Like ""Code Blue*"") AND ((tblRegIndepStudyLearner.ISDateOfClassStart) " & _
    "Between #12/31/2006# And #1/1/2008#)) OR (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)=""Nursing Assistant"") " & _
    "AND ((tblLearnerDepartments.Status)=""Perdiem"" Or (tblLearnerDepartments.Status)=""Perdiem Option"" " & _
    "Or (tblLearnerDepartments.Status) Like ""Inhouse*"") AND ((tblClasses.ClassName) Like ""Perdiem Packet*"") " & _
    "AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #3/7/2007# And #1/1/2008#)) OR (((tblLearners.Inactive)=0) " & _
    "AND ((tblLearners.Credential)=""Nursing Assistant"") AND ((tblClasses.ClassName) Like ""Pressure Ulcer*"") " & _
    "AND ((tblRegIndepStudyLearner.ISDateOfClassStart) " & _
    "Between #12/31/2006# And #1/1/2008#)) OR (((tblLearners.Inactive)=0) " & _
    "AND ((tblLearners.Credential)=""Nursing Assistant"") AND ((tblLearnerDepartments.Status)=""Perdiem"" " & _
    "Or (tblLearnerDepartments.Status)=""Perdiem Option"" Or (tblLearnerDepartments.Status) Like ""Inhouse*"") " & _
    "AND ((tblClasses.ClassName)=""Safety Fair Make-up"") AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#)) " & _
    "AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#)) OR (((tblLearners.Inactive)=0) AND ((tblLearners.Credential)=""Nursing Assistant"") " & _
    "AND ((tblClasses.ClassName) Like ""Competency Packet*"") AND ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2006# And #1/1/2008#));"

    ' Execute it
    DoCmd.RunSQL strSQL1

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

    Re: Seeing double (Access03)

    Fay,

    This is far too complicated to analyze by just viewing the SQL. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Seeing double (Access03)

    Fay

    As Hans states, a cut down database would be easier on this as it is very complex, however I do note that there is a date "thing" going on which may be intended


    ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #3/7/2007# And #1/1/2008#))


    where the rest of the dates say

    ((tblRegIndepStudyLearner.ISDateOfClassStart) Between #12/31/2007# And #1/1/2008#))

    Just a thought while we wait for the DB
    Jerry

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

    Re: Seeing double (Access03)

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

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

    Re: Seeing double (Access03)

    Emily Allen is selected (and hence added to tblCompetencyResultYear) in strSQL since she has Inactive=0, ClassName="Pressure Ulcers - Aides - 2007" and IS Date Completed=01/24/2007.
    She is also selected and added in strSQL1 since she has Inactive=0, Credential="Nursing Assistant", ClassName="Pressure Ulcers - Aides - 2007" and IS Date Completed=01/24/2007.

    If you want to suppress duplicates, you'd have to create a union query. You can then create a make-table query based on the union query. It is not possible to combine a union query and make-table query in one.
    Create a query qryDummy in the database, it can be empty. It'll be modified by the code.

    See attached version.
    Attached Files Attached Files

Posting Permissions

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