My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 67 Joined: 11-March 15 ![]() | 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)); |
![]() Post#2 | |
![]() Posts: 856 Joined: 25-April 14 ![]() | Make 1 more query using the one shown above. Select * from query Set the property UNIQUE VALUES = true No more duplicates. |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 23rd April 2018 - 02:58 AM |