graceusguide
asked on
Convert SQL statement in Access Query
I have the following SQL Statement that I use to get results that I need. I am wanting to have this same statement work in Access, yet am getting lost as to what I need to change to make this happen.
Hoping someone can help?
Hoping someone can help?
SELECT cln_urn, CASE cln_hispanic_yes_un_rfk WHEN 1 THEN 'Hispanic' ELSE CASE CAST(cln_race_white AS int) + CAST(cln_race_black AS int)
+ CAST(cln_race_asian AS int) + CAST(cln_race_pacific AS int) + CAST(cln_race_indian AS int) + CAST(cln_race_other AS int)
+ CAST(cln_race_unknown AS int)
WHEN 0 THEN 'Not Specified' WHEN 1 THEN CASE cln_race_white WHEN 1 THEN 'White (non-Hispanic)' ELSE CASE cln_race_black WHEN 1 THEN 'Black or African-American'
ELSE CASE cln_race_asian WHEN 1 THEN 'Asian' ELSE CASE cln_race_pacific WHEN 1 THEN 'Pacific Islander' ELSE CASE cln_race_indian WHEN 1 THEN
'Am. Indian/Alaska Native' ELSE CASE cln_race_other WHEN 1 THEN 'Other' ELSE 'Unknown' END END END END END END ELSE 'More than one race'
END END AS race
FROM cw_client
In Access, you need to translate CASE expressions into IIf(), Switch(), and/or Choose() statements. In this case, Switch() works nicely. The last condition, True, is equivalent to the final ELSE.
Good luck!
(°v°)
Good luck!
(°v°)
SELECT
cln_urn,
Switch(
cln_hispanic_yes_un_rfk=1, 'Hispanic',
cln_race_white=1, 'White (non-Hispanic)',
cln_race_black=1, 'Black or African-American',
cln_race_asian=1, 'Asian',
cln_race_pacific=1, 'Pacific Islander',
cln_race_indian=1, 'Am. Indian/Alaska Native',
cln_race_other=1, 'Other',
cln_race_unknown=1, 'Unknown',
True, 'Not Specified')
FROM cw_client
Ach! Unlike AHenderson526, I didn't catch the 'more than one race...'. Still, Switch() seems appropriate here.
(°v°)
(°v°)
SELECT
cln_urn,
Switch(
cln_hispanic_yes_un_rfk=1, 'Hispanic',
cln_race_white+cln_race_black+cln_race_asian+cln_race_pacific
+cln_race_indian+cln_race_other+cln_race_unknown>1, 'More than one race',
cln_race_white=1, 'White (non-Hispanic)',
cln_race_black=1, 'Black or African-American',
cln_race_asian=1, 'Asian',
cln_race_pacific=1, 'Pacific Islander',
cln_race_indian=1, 'Am. Indian/Alaska Native',
cln_race_other=1, 'Other',
cln_race_unknown=1, 'Unknown',
True, 'Not Specified')
FROM cw_client
ASKER
Hi again,
I have tried both results giving so far and neither worked. The first (ahenderson526) showed Syntax error (missing Operator) in query expression 'iif(cln_hispanic_yes_un_r
The second result (harfang) showed the results with the first column showing the cln_urn as it should, yet the second coloumn is called expr1001 with only #Error listed all the way down.
Think we are getting closer however....
Thanks
Kerry
I have tried both results giving so far and neither worked. The first (ahenderson526) showed Syntax error (missing Operator) in query expression 'iif(cln_hispanic_yes_un_r
The second result (harfang) showed the results with the first column showing the cln_urn as it should, yet the second coloumn is called expr1001 with only #Error listed all the way down.
Think we are getting closer however....
Thanks
Kerry
I see, you have text fields. The CAST should have told me.
SELECT
cln_urn,
Switch(
cln_hispanic_yes_un_rfk='1', 'Hispanic',
val(cln_race_white)+val(cln_race_black)+val(cln_race_asian)
+val(cln_race_pacific)+val(cln_race_indian)+val(cln_race_other)
+val(cln_race_unknown)>1, 'More than one race',
cln_race_white='1', 'White (non-Hispanic)',
cln_race_black='1', 'Black or African-American',
cln_race_asian='1', 'Asian',
cln_race_pacific='1', 'Pacific Islander',
cln_race_indian='1', 'Am. Indian/Alaska Native',
cln_race_other='1', 'Other',
cln_race_unknown='1', 'Unknown',
True, 'Not Specified')
FROM cw_client
I recounted and think I have too many close-rarentheses.
Try
SELECT cln_urn,
iif(cln_hispanic_yes_un_rf
iif(cint(cln_race_white) + cint(cln_race_black) + cint(cln_race_asian) + cint(cln_race_pacific)+ cint(cln_race_indian) + cint(cln_race_other) + Cint(cln_race_unknown)=0,"
iif(cint(cln_race_white) + cint(cln_race_black) + cint(cln_race_asian) + cint(cln_race_pacific)+ cint(cln_race_indian) + cint(cln_race_other) + Cint(cln_race_unknown)>1,"
iif(cln_race_white=1,"Whit
iif(cln_race_black=1, "Black or African-American",
iif(cln_race_asian= 1,"Asian",
iif(cln_race_pacific=1,"Pa
iif(cln_race_indian=1,"Am.
iif(cln_race_other,=1,"Oth
AS race
FROM cw_client
Try
SELECT cln_urn,
iif(cln_hispanic_yes_un_rf
iif(cint(cln_race_white) + cint(cln_race_black) + cint(cln_race_asian) + cint(cln_race_pacific)+ cint(cln_race_indian) + cint(cln_race_other) + Cint(cln_race_unknown)=0,"
iif(cint(cln_race_white) + cint(cln_race_black) + cint(cln_race_asian) + cint(cln_race_pacific)+ cint(cln_race_indian) + cint(cln_race_other) + Cint(cln_race_unknown)>1,"
iif(cln_race_white=1,"Whit
iif(cln_race_black=1, "Black or African-American",
iif(cln_race_asian= 1,"Asian",
iif(cln_race_pacific=1,"Pa
iif(cln_race_indian=1,"Am.
iif(cln_race_other,=1,"Oth
AS race
FROM cw_client
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh, I wish it did work, but still getting expr1001 with #errors running all the way down. I did add
to the 2nd last "True, 'Not Specified')As Race" so the column had a heading, yet I am not sure why I am getting the errors in the results still. The cln_urn field is a text fieldn and the cln_race fields are all Yes/No fields. Not sure if that helps any?
to the 2nd last "True, 'Not Specified')As Race" so the column had a heading, yet I am not sure why I am getting the errors in the results still. The cln_urn field is a text fieldn and the cln_race fields are all Yes/No fields. Not sure if that helps any?
ASKER
AHenderson526, I tried your new one, still getting the Syntax Error (Missing Operator) in query Expression ......
Harfang, I just tried yours and it worked!
Thanks so much to you both
Kerry
Harfang, I just tried yours and it worked!
Thanks so much to you both
Kerry
ASKER
Thank you greatly, The only little part I had to add was As Race on the 2nd last line so expr1001 didn't show as the column header. Thank you again though
Kerry
Kerry
That 'missing operator' error usually means that a parenthesis is missing, but with so many nested iif's, its hard to find were it was at. Glad Harfang's solution worked. I didn't know about the switch() function, I'll have to use that sometime myself.
Thanks, I'm glad it worked. Just curious: why grade 'B'? This seems to be a typical 'A+', on all accounts!
(°v°)
(°v°)
ASKER
Oh Sorry,
it was my first post and I was clicking everything to see what to do where. I must have somehow left it there. It was an A on all accounts. I will try and see if I can change that.
it was my first post and I was clicking everything to see what to do where. I must have somehow left it there. It was an A on all accounts. I will try and see if I can change that.
No need. I was really just curious. Thanks for replying, and have fun here at EE!
(°v°)
(°v°)
SELECT cln_urn,
iif(cln_hispanic_yes_un_rf
iif(cint(cln_race_white) + cint(cln_race_black) + cint(cln_race_asian) + cint(cln_race_pacific)+ cint(cln_race_indian) + cint(cln_race_other) + Cint(cln_race_unknown)=0,"
iif(cint(cln_race_white) + cint(cln_race_black) + cint(cln_race_asian) + cint(cln_race_pacific)+ cint(cln_race_indian) + cint(cln_race_other) + Cint(cln_race_unknown)>1,"
iif(cln_race_white=1,"Whit
iif(cln_race_black=1, "Black or African-American",
iif(cln_race_asian= 1,"Asian",
iif(cln_race_pacific=1,"Pa
iif(cln_race_indian=1,"Am.
iif(cln_race_other,=1,"Oth
AS race
FROM cw_client
There is a limit on how many nested IIF functions can be done in one shot. I think it is 9 and if so, I believe this just hits it (if I counted correctly). If this doesn't work at first, check my rarentheses, I may have miss counted the number of closes needed.