Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: Crosstab or alternate Sql solution.

    Hi,
    I am using Ms Access 2002. In Sql, I am trying to show employee benefit results based on employee dependents. I would like to group on employee and display one row per employee. On that row, I would like the total number of depends, and each depends age and birthdate. I tried a crosstab query;

    TRANSFORM Count(tblActiveBenefits.Birthdate) AS CountOfBirthdate
    SELECT tblActiveBenefits.employee, Count(tblActiveBenefits.DependAge) AS [Total Of DependAge]
    FROM tblActiveBenefits
    GROUP BY tblActiveBenefits.employee
    PIVOT "Age: " & tblActiveBenefits.DependAge;

    and got the following results;
    xtabActiveBenefits
    employee Total Of Depend_Age Age: 10 Age: 12 Age: 15 Age: 9
    23333 2 1 1
    1044444 1 1
    1055555 2 1 1

    What I would like to see;
    Employee Total Depends Age Birthdate Age Birthdate
    23333 2 12 12/29/2000 9 1/4/2004
    1044444 1 15 12/27/1997
    1055555 2 12 8/17/2000 10 4/4/2003

    Source Data
    CREATE TABLE tblActiveBenefits3 (employee number, DependCode number, DependAge number, Birthdate DateTime )

    INSERT INTO tblActivebenefits (employee, DependCode, DependAge, Birthdate)
    Select * From DataFile

    DataFile:
    23333, 2, 12, 12/29/2000
    23333, 2, 9, 1/4/2004
    1044444, 3, 15, 12/27/1997
    1055555, 4, 12, 8/17/2000
    1055555, 4, 10, 4/4/2003


    Is what Im thinking (array like) possible with just Sql?

    Thank you.
    Last edited by GeneL; 05-01-13 at 16:58. Reason: Provide more detail

Tags for this Thread

Posting Permissions

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