survey classification queries

survey_com.sql

-- male classification
SELECT 
    tbl_survey_comp.event_key AS event_id, 
    tbl_survey_comp.SPECIES, 
    "alive" AS life_status, 
    "adult" AS age_class, 
    "male" AS sex, 
    tbl_survey_comp.Male AS n, 
    [YRLG_M]+[1pt]+[2pt]+[3pt]+[4pt]+[5pts+]+[6pts+]+[2_3yrs]+[4_5yrs]+[6yrs+] AS total_m, 
    "{" & 
        IIF([YRLG_M] > 0, """yearling"":" & [YRLG_M] & ",", "") &
        IIF([1pt] > 0, """1 point"":" & [1pt] & "," , "") &
        IIF([2pt] > 0, """2 point"":" & [2pt] & "," , "") &
        IIF([3pt] > 0, """3 point"":" & [3pt] & "," , "") &
        IIF([4pt] > 0, """4 point"":" & [4pt] & "," , "") &
        IIF([5pts+] > 0, """5 points +"":" & [5pts+] & "," , "") &
        IIF([6pts+] > 0, """6 points +"":" & [6pts+] & "," , "") &
        IIF([2_3yrs] > 0, """2-3 years"":" & [2_3yrs] & "," , "") &
        IIF([4_5yrs] > 0, """4-5 years"":" & [4_5yrs] & "," , "") &
        IIF([6yrs+] > 0, """6 + years"":" & [6yrs+] & "," , "")
    AS a,
    IIF(Right(a, 1) = ",", 
        Left(a, len(a) - 1) & "}", 
        a & "}") AS b,
    IIF(b = "{}", Null, b) AS composition
FROM tbl_survey_comp
WHERE Male > 0;

-- female classification
SELECT 
    event_key AS event_id, 
    SPECIES, 
    "alive" AS life_status, 
    "adult" AS age_class, 
    "female" AS sex, 
    FEMALE AS n
FROM tbl_survey_comp
WHERE FEMALE > 0

UNION

SELECT 
    event_key AS event_id, 
    SPECIES, 
    "alive" AS life_status, 
    "young of year" AS age_class, 
    "unknown" AS sex, 
    JUVENILE AS n
FROM tbl_survey_comp
WHERE JUVENILE > 0

UNION

SELECT 
    event_key AS event_id, 
    SPECIES, 
    "alive" AS life_status, 
    "unclassified" AS age_class, 
    "unknown" AS sex, 
    TOTAL_UNCLASSIFIED AS n
FROM tbl_survey_comp
WHERE TOTAL_UNCLASSIFIED > 0;