Change Positive entry in 3 number colums to Yes or X 1

[springwood01]

Have 423 records that needed to determine if they were 0%, 50% or 100% compliant to established criteria. Have this figured out based on a positive entry of their account # in one of the 3 columns. Need to have a report like this that either shows yes when a account# appears or an X. Tried to play with some IIF statements but could not get them to populate for the subsequent report. Below the report sample is my SQL. Any ideas on how to do this?

ActiveContractNum 0% 50% 100%
######## YES
######## YES
######## YES
######## YES

This is my SQL:

SELECT TestAutocamSubs.CONTRACT_NUM, TestAutocamSubs.ActiveContractNum, TestAutocamSubs.Employee, [Test 0% Compliance].[0%Contract#], [Test 50% Comp].[50%Contract#], [Test 100% compliance].[100%Contract#]
FROM [Test 100% compliance] RIGHT JOIN ([Test 0% Compliance] RIGHT JOIN ([Test 50% Comp] RIGHT JOIN TestAutocamSubs ON [Test 50% Comp].[50%Contract#] = TestAutocamSubs.CONTRACT_NUM) ON [Test 0% Compliance].[0%Contract#] = TestAutocamSubs.CONTRACT_NUM) ON [Test 100% compliance].[100%Contract#] = TestAutocamSubs.CONTRACT_NUM
ORDER BY TestAutocamSubs.Employee;

 

[Golom]

SELECT A.CONTRACT_NUM
     , A.ActiveContractNum
     , A.Employee
     , IIF(P0.[0%Contract#] > 0, "Yes", "")
     , IIF(P50.[50%Contract#] > 0, "Yes", "")
     , IIF(P100.[100%Contract#] > 0, "Yes", "")

FROM [Test 100% compliance] P100
     RIGHT JOIN ( [Test 0% Compliance] P0
     RIGHT JOIN ( [Test 50% Comp] P50 
     RIGHT JOIN TestAutocamSubs A ON P50.[50%Contract#] = A.CONTRACT_NUM) 
     ON P0.[0%Contract#] = A.CONTRACT_NUM) 
     ON P100.[100%Contract#] = A.CONTRACT_NUM

ORDER BY A.Employee;

 

[springwood01]

Well, I was somewhat in the right direction but you have now made sense of it. One minior problem, when I run the query, instead of a Yes, I get #Error. I tried to play with it a bit, but obviously I have not touched on what will make it go to yes. Any Ideas?

 

[Golom]

Maybe they need column names

SELECT A.CONTRACT_NUM
     , A.ActiveContractNum
     , A.Employee
     , IIF(P0.[0%Contract#] > 0, "Yes", "") [red]As [0%][/red]
     , IIF(P50.[50%Contract#] > 0, "Yes", "") [red]As [50%][/red]
     , IIF(P100.[100%Contract#] > 0, "Yes", "") [red]As [100%][/red]

FROM [Test 100% compliance] P100
     RIGHT JOIN ( [Test 0% Compliance] P0
     RIGHT JOIN ( [Test 50% Comp] P50 
     RIGHT JOIN TestAutocamSubs A ON P50.[50%Contract#] = A.CONTRACT_NUM) 
     ON P0.[0%Contract#] = A.CONTRACT_NUM) 
     ON P100.[100%Contract#] = A.CONTRACT_NUM

ORDER BY A.Employee;

 

[springwood01]

While you were working on your return post, I was always trying some different things and came up with the one that works (your 2nd post did not). Note the changes I made in the IIF statements. Works perfectly now. Thanks so much for getting me on the right track.

IIF(P0.[0%Contract#] > "0", "Yes")
IIF(P50.[50%Contract#] > "0", "Yes")
IIF(P100.[100%Contract#] > "0", "Yes")

 

[lespaul]

apparently 0%Contract, etc are TEXT fields that require string quotes ("") around the data, Golom assumed they were numeric which does not require string quotes.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi