[X]My Assistant

[Psycoperl]

Good Afternoon,

I have having a problem with a query not working as intended and need some guidance.

I am trying to make a user friendly query that I can put in a spreadsheet of a students test scores. Since the student can test at more than one location (CU_TEST_SITE1 is the code, tblstudent.TestSite is the Display Name)

The query works well when a student has their test(s) on one date and at one site. When a student takes a test part at more than one location or on more than one date, the records are merging.

The idea output would be

CODE

EMPLID    TestDate    MathScore1    ReadingScore1    SentenceSkillsScore1    System    TestSite
STU00011    12/21/2016    117    67    39    SYS01    7858A
STU00022    1/6/2017            120    101    77    SYS01     1004Q
STU00113    11/17/2016    102    27    101    SYS01    8520S
STU00113    12/7/2016    0    39    0    SYS01    9850Z
STU00522    12/16/2016    0    40    0    SYS01      7858A
STU00522    1/4/2017            55    0    70    SYS01    7858A

What I am getting is:

CODE

EMPLID    TestDate    MathScore1    ReadingScore1    SentenceSkillsScore1    System    TestSite
STU00011    12/21/2016    117    67    39    SYS01    7858A
STU00022    1/6/2017            120    101    77    SYS01     1004Q
STU00113    11/17/2016    102    39    101    SYS01    8520S
STU00113    11/17/2016    102    39    101    SYS01    9850Z
STU00113    12/7/2016    0    39    0    SYS01    9850Z
STU00113    12/7/2016    0    39    0    SYS01    8520S

SQL

SELECT StuList.EMPLID, StuList.TestDate, Nz([MathScore],0) AS MathScore1, Nz([ReadingScore],0) AS ReadingScore1, Nz([SentenceSkillsScore],0) AS SentenceSkillsScore1, StuList.System, StuList.TestSite
FROM zqry_NonBAccuResultsCF_SenSkills AS SenSkills RIGHT JOIN (zqry_NonBAccuResultsCF_Reading AS Reading RIGHT JOIN (zqry_NonBAccuResultsCF_Math AS Math RIGHT JOIN zqry_NonBAccuResultsCF_StuList AS StuList ON Math.EMPLID = StuList.EMPLID) ON Reading.EMPLID = StuList.EMPLID) ON SenSkills.EMPLID = StuList.EMPLID
WHERE (((StuList.TestDate)=[Math].[MathTestDate]) AND ((StuList.CU_TEST_SITE1)=[Math].[CU_TEST_SITE1])) OR (((StuList.TestDate)=[Reading].[ReadTestDate] Or (StuList.TestDate) Is Null) AND ((StuList.CU_TEST_SITE1)=[Reading].[CU_TEST_SITE1] Or (StuList.CU_TEST_SITE1) Is Null)) OR (((StuList.TestDate)=[SenSkills].[SentenceSkillsTestDate] Or (StuList.TestDate) Is Null) AND ((StuList.CU_TEST_SITE1)=[SenSkills].[CU_TEST_SITE1] Or (StuList.CU_TEST_SITE1) Is Null));

[ranman256]

Make 1 more query using the one shown above.
Select * from query

Set the property UNIQUE VALUES = true

No more duplicates.