Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2007
    Location
    Seattle
    Posts
    7

    Red face Unanswered: Changing Data Format - Quering SQL Server (Access as Front End)

    I have a database with tables linked via SQL Server. The way the data is stored on the server is not useful for reports we must submit to our funder. I received some great advice on how to change the way the data is displayed for Y/N questions but now have run into a different problem.

    Here's how the data is currently displayed and how the funder requires it to be reported. Any advice will be greatly appreciated.

    Fieldname: Role

    CURRENT DISPLAY CORRECT DISPLAY

    ARNP 5
    RN 3
    LPN/LVN 4
    Physician 1
    Physician Asst 2
    Laboratorian 7
    Other 8

    Thanks again for your help. I don't often need to know how to do something in SQL but when I do, I find this forum a great resource. Thanks very much!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Try using nested IIF statements, kinda like
    Code:
    SELECT IIF(tblname.Role='ARNP',5,IIF(tblname.Role='RN',3,IIF(tblname.Role='LPN/LVN',4,IIF(tblname.Role='Physician',1,IIF(tblname.Role='Physician Asst',2,IIF(tblname.Role='Laboratorian',7,IIF(tblname.Role='Other',8,-1))))))) AS FieldName ...
    On a side note, I can't remember how many but I think there's a limit to how many parentheses levels Access can handle...
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think there's a limit to how many parentheses levels Access can handle...
    LOL!! If there is, the SQL builder has some explaining to do!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2007
    Location
    Seattle
    Posts
    7
    Hi Nick,

    With a couple of tiny modifications this works! Thank you so much for your quick response. I'm so close to finishing up this project that I can hardly believe it. Have a terrific week.

    Kathy

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by StarTrekker
    LOL!! If there is, the SQL builder has some explaining to do!!
    Oh right! Then am I thinking there's a limit to nested parentheses in vba? oh well, try the above and see if it works for you
    Me.Geek = True

  6. #6
    Join Date
    Nov 2007
    Location
    Seattle
    Posts
    7
    Oh oh! Nick, you are right. I just got an error message telling me that "Expression too complex in query expression". This is the long one---with 18 different choices for the user. It's in the SQL view--here's my code (which worked for fewer choices). Let me know if you have any suggestions. Thanks very much! --Kathy

    IIf(registration_.functional_role_1='Clinician',1, IIf(registration_.functional_role_1='Administrator ',2, IIf(registration_.functional_role_1='Supervisor',3 , IIf(registration_.functional_role_1='Program Manager/Coordinator',4, IIf(registration_.functional_role_1=''Case Manager',5, IIf(registration_.functional_role_1='Prevention Case Manager',6, IIf(registration_.functional_role_1='Counselor',7, IIf(registration_.functional_role_1='Researcher',8 , IIf(registration_.functional_role_1='Resident/Fellow',9, IIf(registration_.functional_role_1='Laboratorian' ,10, IIf(registration_.functional_role_1='Student',11, IIf(registration_.functional_role_1='Faculty',12, IIf(registration_.functional_role_1='Health Educator',13, IIf(registration_.functional_role_1='Trainer',14, IIf(registration_.functional_role_1='Outreach',15, IIf(registration_.functional_role_1='Disease Intervention/Investigation',16, IIf(registration_.functional_role_1='Not Employed',17, IIf(registration_.functional_role_1='Other',18)))) )))))))))))))) AS FunRC

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nick, no, I am not doubting you, just laughing that there might be a parenthesis limit combined with the nasty habit of the query builder to spam brackets!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    karingwood, If nothing else, you can break the query up in to two (or more if needed queries and Union them together. Something like:
    Code:
    SELECT columns
      , IIf(registration_.functional_role_1 = 'Clinician',1
      , IIf(registration_.functional_role_1 = 'Administrator ',2
      , IIf(registration_.functional_role_1 = 'Supervisor',3 
      , IIf(registration_.functional_role_1 = 'Program Manager/Coordinator',4
      , IIf(registration_.functional_role_1 = ''Case Manager',5
      , IIf(registration_.functional_role_1 = 'Prevention Case Manager',6
      , IIf(registration_.functional_role_1 = 'Counselor',7
      , IIf(registration_.functional_role_1 = 'Researcher',8 
         ))))))))) AS FunRC
    
    FROM table-name
    WHERE registration_.functional_role_1 IN( 'Clinician'
                                            , 'Administrator '
                                            , 'Supervisor'
                                            , 'Program Manager/Coordinator'
                                            , 'Case Manager'
                                            , 'Prevention Case Manager'
                                            , 'Counselor'
                                            , 'Researcher'
                                            , 'Resident/Fellow')
    UNION ALL
    SELECT columns
      , IIf(registration_.functional_role_1 = 'Laboratorian' ,10
      , IIf(registration_.functional_role_1 = 'Student',11
      , IIf(registration_.functional_role_1 = 'Faculty',12
      , IIf(registration_.functional_role_1 = 'Health Educator',13
      , IIf(registration_.functional_role_1 = 'Trainer',14
      , IIf(registration_.functional_role_1 = 'Outreach',15
      , IIf(registration_.functional_role_1 = 'Disease Intervention/Investigation',16
      , IIf(registration_.functional_role_1 = 'Not Employed',17
      , IIf(registration_.functional_role_1 = 'Other',18
           ))))))))) AS FunRC
    FROM table-name
    WHERE registration_.functional_role_1 IN( 'Laboratorian'
                                            , 'Student'
                                            , 'Faculty'
                                            , 'Health Educator'
                                            , 'Trainer'
                                            , 'Outreach'
                                            , 'Disease Intervention/Investigation'
                                            , 'Not Employed'
                                            , 'Other')

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    If speed isn't too much of an issue, you could consider doing a public select function in a public module, and using that in the SQL, like:

    Code:
    Public Function fxnEnumRoleType(strInput as string) as Integer
    
    SELECT CASE strInput
    CASE "Clinician"
         fxnEnumRoleType = 1
    CASE "Administrator "
         fxnEnumRoleType = 2
    CASE "Supervisor"
         fxnEnumRoleType = 3
    'repeat as necessary
    CASE ELSE
         fxnEnumRoleType = -1
    END SELECT
    
    End Function
    And then call it:
    Code:
    SELECT fxnEnumRoleType(registration_.functional_role_1) AS FunRC ...
    It's also a bit cleaner approach, but I think there's a small price to pay in terms of crunch time for calling a vba function.
    Me.Geek = True

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by karingwood
    IIf(registration_.functional_role_1='Clinician',1, IIf(registration_.functional_role_1='Administrator ',2, IIf(registration_.functional_role_1='Supervisor',3 , IIf(registration_.functional_role_1='Program Manager/Coordinator',4, IIf(registration_.functional_role_1=''Case Manager',5, IIf(registration_.functional_role_1='Prevention Case Manager',6, IIf(registration_.functional_role_1='Counselor',7, IIf(registration_.functional_role_1='Researcher',8 , IIf(registration_.functional_role_1='Resident/Fellow',9, IIf(registration_.functional_role_1='Laboratorian' ,10, IIf(registration_.functional_role_1='Student',11, IIf(registration_.functional_role_1='Faculty',12, IIf(registration_.functional_role_1='Health Educator',13, IIf(registration_.functional_role_1='Trainer',14, IIf(registration_.functional_role_1='Outreach',15, IIf(registration_.functional_role_1='Disease Intervention/Investigation',16, IIf(registration_.functional_role_1='Not Employed',17, IIf(registration_.functional_role_1='Other',18)))) )))))))))))))) AS FunRC
    By the way, just noticed in this expression that you don't have an "falsepart" argument in the final IIF function, may be part of your problem.

    EDIT: See here for help on this function.
    Last edited by nckdryr; 06-30-09 at 04:03.
    Me.Geek = True

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally If I were you I'd do the formatting in the front end report. leave the query as is, or if you must do the formatting in the query do it as SQL server not JET.

    another approach would be to put the string codes in a seperate table and do a join to that table

    of the various approaches a table in the SQL would be far smarter.. why, because if a new category is required then its under the control of the users and does not require an application change and therefore doesn't require developer time.

    you could put your formatting in the reports on format event and call the function NickyDryer is suggesting from the report.

    I certainly wouldn't use a complex IIF in a query, especially if there is a need to maintain that over time
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by healdem
    another approach would be to put the string codes in a seperate table and do a join to that table
    As usual, Healdem points out that I'm making it harder than it has to be
    Me.Geek = True

  13. #13
    Join Date
    Nov 2007
    Location
    Seattle
    Posts
    7
    Wow--such great response. I must admit I am a bit overwhelmed in part because I am so new and inexperienced at this.

    healdem, I am not able to modify the front end of the report. I agree tht formatting the query in SQL is a good idea, but I do not know how to do this. Can you point me to some resources so I can learn?

    Nick, I don't think speed will be a problem as this is not a humongous dataset. Can you tell me where in the code I place this syntax (sorry--I truly am a newbee).

    StealthDBA, again, I am not sure how to place this syntax in the code. If you can help me out, let me know and I'll give it a try.

    Thanks to you all for your help. I'm learning, albiet slowly.

    --Kathy

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by nckdryr
    As usual, Healdem points out that I'm making it harder than it has to be

    odd that
    I'm regularly accused of doing that myself.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you've had several suggestions from several different people
    ultimately you have to make your own mind up of which of those suggestions you want to take forward. that choice will reflect your own experience and capabilities and what you are allowed to do.

    FWIW I'd investigate the option of adding a table to the SQL server db which identifies a specific code and describes that code

    eg
    table: roles
    ID: numeric
    Description: varchar

    data
    1 Clinician
    2 Administrator
    3 Supervisor
    4 Program Manager/Coordinator
    666 Accountant
    ....and so on
    but that would require an additional program to maintain that table.

    but when you do your data extract its a simple join to retrieve the role as part of your data stream

    if you are not allowed to do that then I'd fake it by creating such a table in Access, however I wouldn't use a join, I'd write a function which returned the role of that person, and provide for the opportunity where you may get a role you don't know.

    the ansers given here may not be apprpriate for your purposes. it may well be that a complex IIF is appropriate if this is a "smash and grab" change.. ie make the chaneg keep the system working and do a runner. if this is something that is likely to change in the near future, then I would deffo council that you use a table to do this sort of coding.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •