SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Case Statement using SQL in MS Access Desgin

    I need to use a case statement to score some answers from this survey for instance if the answer to the question is Yes then return the value of 1 No 0, On the on question it has highest levele of Education if they answer Elementary = 1 Middle School = 1 Etc you see my attempt in the code below. Any ideas on this? Anyone??

    SELECT
    HRA_DATA_ALL_03092012.[CREATEDATE],
    HRA_DATA_ALL_03092012.[DOB] ,
    HRA_DATA_ALL_03092012.[MemberID] ,
    HRA_DATA_ALL_03092012.[FirstName],
    HRA_DATA_ALL_03092012.[MI] ,
    HRA_DATA_ALL_03092012.[LastName] ,
    HRA_DATA_ALL_03092012.[HomePhone],
    HRA_DATA_ALL_03092012.[CellPhone],
    HRA_DATA_ALL_03092012.[EmContactFirstName],
    HRA_DATA_ALL_03092012.[EmContactLastName] ,
    HRA_DATA_ALL_03092012.[EmContactPhone] ,
    HRA_DATA_ALL_03092012.[EmContactCellPhone],
    HRA_DATA_ALL_03092012.[POAFirstName],
    HRA_DATA_ALL_03092012.[POALastName],
    HRA_DATA_ALL_03092012.[POAPhone] ,
    HRA_DATA_ALL_03092012.[InterpreterFlag] ,
    HRA_DATA_ALL_03092012.[Race] ,
    CASE HRA_DATA_ALL_03092012.[HighestEdLevel]
    WHEN 'ELEMENTARY' THEN 'ELEMENTARY - 1'
    WHEN 'Middle School' THEN 'Middle School - 1'
    END
    HRA_DATA_ALL_03092012.[SelfCompareHealthOthers] ,
    HRA_DATA_ALL_03092012.[SelfCompareHealthYearAgo] ,
    HRA_DATA_ALL_03092012.[SelfCompareEmotionalOthers] ,
    HRA_DATA_ALL_03092012.[SelfCompareEmotionalYearAgo],
    HRA_DATA_ALL_03092012.[HavePCPFlag],
    HRA_DATA_ALL_03092012.[HospAdmitPastSixFlag] ,
    HRA_DATA_ALL_03092012.[ERPastSixFlag] ,
    HRA_DATA_ALL_03092012.[OxygenFlag] ,
    HRA_DATA_ALL_03092012.[TrachFlag] ,
    HRA_DATA_ALL_03092012.[TubeFeedFlag] ,
    HRA_DATA_ALL_03092012.[WheelchairFlag] ,
    HRA_DATA_ALL_03092012.[HomeSpeechFlag] ,
    HRA_DATA_ALL_03092012.[HomeNurseFlag] ,
    HRA_DATA_ALL_03092012.[NebulizerFlag] ,
    HRA_DATA_ALL_03092012.[CatheterFlag] ,
    HRA_DATA_ALL_03092012.[InsulinPumpFlag],
    HRA_DATA_ALL_03092012.[WalkerFlag] ,
    HRA_DATA_ALL_03092012.[HomeOccFlag],
    HRA_DATA_ALL_03092012.[CPAPFlag] ,
    HRA_DATA_ALL_03092012.[OstomyFlag] ,
    HRA_DATA_ALL_03092012.[HomeInfusionFlag]
    HRA_DATA_ALL_03092012.[HospBedFlag] ,
    HRA_DATA_ALL_03092012.[HomePhysFlag],
    HRA_DATA_ALL_03092012.[HHDMEOtherFlag] ,
    HRA_DATA_ALL_03092012.[GroomingHelp] ,
    HRA_DATA_ALL_03092012.[DressingHelp] ,
    HRA_DATA_ALL_03092012.[BathingHelp] ,
    HRA_DATA_ALL_03092012.[EatingHelp],
    HRA_DATA_ALL_03092012.[WalkingHelp] ,
    HRA_DATA_ALL_03092012.[TransferHelp] ,
    HRA_DATA_ALL_03092012.[SpecialDietFlag],
    HRA_DATA_ALL_03092012.[NumDailyMeds] ,
    HRA_DATA_ALL_03092012.[OftenSadFlag],
    HRA_DATA_ALL_03092012.[AlcoholDrugAbuseHistoryFlag],
    HRA_DATA_ALL_03092012.[TobaccoUseFlag] ,
    HRA_DATA_ALL_03092012.[CondCHFFlag],
    HRA_DATA_ALL_03092012.[CondDiabetesFlag] ,
    HRA_DATA_ALL_03092012.[CondCOPDFlag] ,
    HRA_DATA_ALL_03092012.[CondHBPFlag] ,
    HRA_DATA_ALL_03092012.[CondCancerFlag] ,
    HRA_DATA_ALL_03092012.[CondTransplantFlag],
    HRA_DATA_ALL_03092012.[CondHIVFlag] ,
    HRA_DATA_ALL_03092012.[CondDepressionFlag] ,
    HRA_DATA_ALL_03092012.[CondPainFlag] ,
    HRA_DATA_ALL_03092012.[CondArthritisFlag] ,
    HRA_DATA_ALL_03092012.[CondParkinsonsFlag] ,
    HRA_DATA_ALL_03092012.[CondHearingFlag] ,
    HRA_DATA_ALL_03092012.[CondVisionFlag] ,
    HRA_DATA_ALL_03092012.[CondBreathingFlag],
    HRA_DATA_ALL_03092012.[CondCholesterolFlag] ,
    HRA_DATA_ALL_03092012.[CondHeartAttackFlag] ,
    HRA_DATA_ALL_03092012.[CondStrokeFlag] ,
    HRA_DATA_ALL_03092012.[CondLiverFlag] ,
    HRA_DATA_ALL_03092012.[CondKidneyFlag],
    HRA_DATA_ALL_03092012.[CondUrinaryFlag] ,
    HRA_DATA_ALL_03092012.[CondAlzheimersFlag]
    FROM HRA_DATA_ALL_03092012;

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    There is a missing comma after the case expression.

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Don't know if access sql supports cases. But you can directly call VBA functions, I would model the case statement that way given that option.


Bookmarks

Posting Permissions

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