CODEWITHSUNDEEP
ms-access
angryBird
angryBird

Reputation: 44

Transpose data in ms access

please I want help with the following issue: I have the following table named emp_attend in ms ACCESS

     emp_code    N_A        Date_in
      1          N        1/1/17
      1          N        2/1/17
      1          A        3/1/17
      .           .         .
      .           .         .
      2          A       1/1/17
      2          N       2/1/17
      2          A       3/1/17
      .           .         .
      .           .         .
      .           .         .

I want to convert the previous table to the get the following result using MS-access query

    emp_code         Attendance
      1          N A N A ........................
      2          N A N A ........................
      3          N A N A ........................
      4          N A N A ........................
      .

Upvotes: 0

Views: 473

Answers (1)

June7
June7

Reputation: 21370

Assuming data has multiple years and you only want 1 month output, consider this CROSSTAB:

PARAMETERS Yr Long, Mo Long;
TRANSFORM First(emp_attend.N_A) AS FirstOfN_A
SELECT emp_attend.emp_code
FROM emp_attend
GROUP BY emp_attend.emp_code
PIVOT Day([Date_in]);

Upvotes: 1

Related Questions