UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query - Unwanted Data Replication, Access 2013    
 
   
Psycoperl
post Jan 10 2017, 04:41 PM
Post#1



Posts: 18
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));
Go to the top of the page
 
ranman256
post Jan 10 2017, 05:50 PM
Post#2



Posts: 673
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.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd February 2017 - 08:33 PM