psycoperl
Active Member
- Joined
- Oct 23, 2007
- Messages
- 339
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
- Web
Good Afternoon,
I am trying to select a SINGLE test record for each person, we want to take their most recent pass and if they do not have any passes but have failing records then their most recent FAIL. I tried to use the code below but am still getting multiple records for some people, if you look at the sample data Person N they have taken the exam 3 times I should only get the July 28 2005 record not the other two.
Any Suggestions?
Thank you in advance for your assistance
SAMPLE DATA
I am trying to select a SINGLE test record for each person, we want to take their most recent pass and if they do not have any passes but have failing records then their most recent FAIL. I tried to use the code below but am still getting multiple records for some people, if you look at the sample data Person N they have taken the exam 3 times I should only get the July 28 2005 record not the other two.
Any Suggestions?
Thank you in advance for your assistance
Code:
SELECT WEF.PERSON, WEF.[Test Dt], Format([WE1].[Score],"00") AS s1, Format([WE2].[Score],"00") AS s2, Format([WE3].[Score],"00") AS s3,
Format([WEF].[Score],"00") AS sF, WEF.[Ltr Score], WEF.[ESL Indic] AS SecLang, Min(WEF.Hirearchy) AS MinOfHirearchy
FROM WE3 RIGHT JOIN (WE2 RIGHT JOIN ( WE1 RIGHT JOIN WEF ON WE1.DATAKEY = WEF.DATAKEY) ON WE2.DATAKEY = WEF.DATAKEY) ON WE3.DATAKEY = WEF.DATAKEY
GROUP BY WEF.ID, WEF.Last, WEF.[First Name], WEF.Middle, WEF.[Test Dt], Format([WE1].[Score],"00"), Format([WE2].[Score],"00"),
Format([WE3].[Score],"00"), Format([WEF].[Score],"00"), WEF.[Ltr Score], WEF.[ESL Indic]
ORDER BY WEF.Last, WEF.[First Name], WEF.Middle, First(WEF.[Test Dt]) DESC;
SAMPLE DATA
Code:
PERSON [Test Dt] S1 S2 S3 SF SCORE SecLang MinOfHirearchy
A 17-Aug-01 04 03 07 PASS N 1
B 22-Dec-09 02 02 04 FAIL Y 2
C 12-Aug-10 04 04 08 PASS N 1
D 05-Feb-07 03 04 07 PASS N 1
E 02-Jul-09 03 04 07 PASS N 1
F 17-May-07 04 03 07 PASS N 1
G 20-May-10 02 02 04 FAIL Y 2
H 18-Mar-10 04 04 08 PASS N 1
I 27-Jul-06 04 04 08 PASS N 1
J 07-Jul-10 03 03 06 FAIL N 2
K 04-Mar-10 04 04 08 PASS N 1
L 22-May-03 03 03 06 FAIL N 2
M 05-Jun-06 02 02 04 FAIL N 2
N 28-Jul-05 04 04 08 PASS N 1
N 17-May-05 03 03 06 FAIL N 2
N 18-Jan-05 03 03 06 FAIL N 2
O 09-Aug-02 04 04 08 PASS Y 1
P 01-Aug-06 03 04 07 PASS N 1