Hi
Can someone help please. I have this query
SELECT “NOM ROLL”.“SERVICENo”, “NOM ROLL”.“SURNAME”, “NOM ROLL”.“FIRSTNAME”, “NOM ROLL”.“RANK”, “NOM ROLL”.“UNIT”, “Query_Badges”.“SHOOTDATE”, “Query_Badges”.“Pct”, “Query_Badges”.“Badge”, “NOM ROLL”.“AGEDOUTb” FROM “Query_Badges” “Query_Badges”, “NOM ROLL” “NOM ROLL” WHERE “Query_Badges”.“CadetID” = “NOM ROLL”.“ID” AND “NOM ROLL”.“AGEDOUTb” = FALSE AND ( “Query_Badges”.“SHOOTDATE” >= {d ‘2016-01-01’ } AND “Query_Badges”.“Badge” = ‘/’ OR “Query_Badges”.“SHOOTDATE” >= {d ‘2016-01-01’ } AND “Query_Badges”.“Badge” = ‘X’ ) ORDER BY “NOM ROLL”.“SURNAME” ASC
This gives me everybody that has qualified for a single and or crossed rifle. I would like to be able to refine the query. The Crossed badge is the superior badge. I would like the query to give me those who have earned the “X” thus ignoring the “/” badge by date and if anybody has not earned the “x” badge then the latest date they earn’t the “/” badge so we can eliminate duplicate records.
Thank you
Nev