0

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 ........................
      .
angryBird
  • 44
  • 8
  • Shouldn't the result read: `1: NNA` and `2: ANA`? – Gustav Jun 15 '17 at 07:34
  • A for absent and N for normal (Present) – angryBird Jun 15 '17 at 07:37
  • 1
    You could try a CROSSTAB query with date_in field as the column header. Or review http://allenbrowne.com/func-concat.html and http://www.accessforums.net/showthread.php?t=18459 and http://www.accessforums.net/showthread.php?t=21972 – June7 Jun 15 '17 at 07:40
  • i'hv tried crosstab query but did'nt get positice result – angryBird Jun 15 '17 at 07:52
  • And what is the expected result from your example data? Probably not as shown. – Gustav Jun 15 '17 at 08:10
  • Possible duplicate of [Combine values from related rows into a single concatenated string value](https://stackoverflow.com/questions/13278590/combine-values-from-related-rows-into-a-single-concatenated-string-value) – Andre Jun 15 '17 at 11:06
  • What's wrong with CROSSTAB? I tested and looks good to me. Why do you have the example showing the N and A alternating for each record? That's not what should output from the example data - as already pointed out by Gustav. You are showing dates in international format. Review http://allenbrowne.com/ser-36.html – June7 Jun 15 '17 at 17:21

1 Answers1

1

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]);
June7
  • 19,874
  • 8
  • 24
  • 34