left join result is not as expected

left join result is not as expected

I have two table From microsoft Access Database like this
1.HR_Personnel
+-----+---------------+----------------------+
| ID | NIP | Name |
+---------------------+----------------------+
| 1 | 050803075200 | Teguh |
| 2 | 050803075201 | Supomo |
| 3 | 091121128829 | DHINI ADHITYAS M |
| 4 | 011103078923 | INDAHSARI YULIYANTI |
| 5 | 050825018108 | BAGUS NANDANG SATRIA |
| 6 | 050917077217 | IMAM ABADI |
| 7 | 050811118407 | EKA ANDIKA LATIF |
| 8 | 011102018725 | YANUAR ADE BAGUS |
| 9 | 011208088831 | RIZKY BESAR WIBOWO |
| 10 | 021208088832 | FAJAR HIDAYAT |
| 11 | 011107068624 | DWI PUTRI FITRIANI |
| 12 | 031105098527 | VERA HELZA |
| 13 | 121919128522 | MAYLINDA ALFIANDA |
| 14 | 191122198630 | M ESACK MAULANA |
| 15 | 061204291133 | YUSTIANA PRIMA P |
| 16 | 050809096910 | SAPTO WIDODO |
| 17 | 050828107812 | SATINO |
| 18 | 050819098811 | PURNOMO SETIAWAN |
| 19 | 19 | URIPTO WIBOWO |
| 20 | 121228018835 | RISKA IRMANANINGTYAS |
| 21 | 121202108834 | OKTARISA HALIDA |
| 22 | 22 | heri |
+-----+---------------+----------------------+
TA_Record_Info
+---------+-----------------------+
| Per_Code| Date_Time |
+---------+-----------------------+
| 14 | 2013-02-20 07:45:57 |
| 7 | 2013-02-20 17:24:13 |
| 18 | 2013-02-20 18:18:07 |
| 5 | 2013-02-21 07:53:40 |
| 2 | 2013-02-21 20:31:02 |
| 17 | 2013-02-21 17:31:57 |
| 15 | 2013-02-21 17:31:57 |
| 6 | 2013-02-21 17:31:57 |
| 16 | 2013-02-21 17:31:57 |
| 9 | 2013-02-21 17:31:57 |
| 8 | 2013-02-21 17:31:57 |
| 4 | 2013-02-21 17:31:57 |
| 13 | 2013-02-21 17:31:57 |
| 17 | 2013-02-21 17:31:57 |
| 6 | 2013-02-21 17:31:57 |
| 4 | 2013-02-21 17:31:57 |
| 14 | 2013-02-21 17:31:57 |
| 10 | 2013-02-21 17:31:57 |
| 9 | 2013-02-21 17:31:57 |
| 5 | 2013-02-21 17:31:57 |
| 11 | 2013-02-21 17:31:57 |
| 14 | 2013-02-21 17:31:57 |
| 3 | 2013-02-21 17:31:57 |
| 2 | 2013-02-21 17:31:57 |
| 15 | 2013-02-21 17:31:57 |
| 19 | 2013-02-21 17:31:57 |
| 1 | 2013-02-21 17:31:57 |
| 13 | 2013-02-21 17:31:57 |
| 3 | 2013-02-21 17:31:57 |
| 2 | 2013-02-21 17:31:57 |
+---------+-----------------------+
I want to join 2 tables left is then split datetime into date, InTime, and
OutTime so that we know employees who do not the present(no datetime, in
time and out time).But when I left join that tables The result just show
heri (NIP = 22) and OKTARISA Halida (NIP = 121 202 108 834) as the results
who employees are not present . I use this query and results
Microsoft Access Query
SELECT p.Per_Code AS NIP,
p.Per_Name AS Nama,
Format (a.Date_Time, 'yyyy-mm-dd') as adate,
IIF((Min(a.Date_Time) <> Max(a.Date_Time)),
Format (Min(a.Date_Time), 'hh:mm:ss'),
IIF( Format (Min(a.Date_Time),
'hh:mm:ss') < '12:00:00',
Format (Min(a.Date_Time),
'hh:mm:ss'),
'-'
)
)as InTime,
IIF((Max(a.Date_Time) <> Min(a.Date_Time)),
Format (Max(a.Date_Time), 'hh:mm:ss'),
IIF( Format (Max(a.Date_Time),
'hh:mm:ss') > '12:00:00',
Format (Max(a.Date_Time),
'hh:mm:ss'),
'-'
)
)as OutTime
FROM HR_Personnel AS p
LEFT JOIN TA_Record_Info a
ON p.ID=a.Per_Code
GROUP BY p.Per_Code,
p.Per_Name,
Format (a.Date_Time, 'yyyy-mm-dd')
Order BY Format (a.Date_Time, 'yyyy-mm-dd'),
Right(p.Per_Code,2),
p.Per_Name;
Result
+----------------+---------------------+-------------+-------------+-------------+
| NIP | Nama | adate | InTime |
OutTime |
+----------------+---------------------+-------------+-------------+-------------+
| 22 | heri | | | -
|
| 121202108834 | OKTARISA HALIDA | | | -
|
| 050812066200 | TEGUH SUMARYONO | 2012-08-31 | - |
18:18:07 |
| 050803075201 | SUPOMO | 2012-08-31 | 15:06:06 |
20:57:48 |
| 050811118407 | EKA ANDIKA LATIF | 2012-08-31 | - |
14:59:18 |
| 050825018108 | BAGUS NANDANG SATRIA| 2012-08-31 | 15:03:45 |
17:31:57 |
| 050809096910 | SAPTO WIDODO | 2012-08-31 | - |
15:56:02 |
| 050819098811 | PURNOMO SETIAWAN | 2012-08-31 | - |
15:01:16 |
| 050828107812 | SATINO | 2012-08-31 | 15:09:07 |
16:30:22 |
| 050917077217 | IMAM ABADI | 2012-08-31 | 15:54:38 |
16:31:50 |
| 19 | URIPTO WIBOWO | 2012-08-31 | - |
18:07:06 |
| 121919128522 | MAYLINDA ALFIANDA | 2012-08-31 | 16:27:06 |
20:53:20 |
| 011103078923 | INDAHSARI YULIYANTI | 2012-08-31 | 16:25:46 |
16:32:37 |
| 011107068624 | DWI PUTRI FITRIANI | 2012-08-31 | - |
17:24:20 |
| 011102018725 | YANUAR ADE BAGUS | 2012-08-31 | - |
16:24:50 |
| 091121128829 | DHINI ADHITYAS M | 2012-08-31 | 17:25:30 |
20:53:39 |
| 191122198630 | M ESACK MAULANA | 2012-08-31 | 14:47:00 |
17:25:21 |
| 011208088831 | RIZKY BESAR WIBOWO | 2012-08-31 | 16:23:38 |
16:35:37 |
| 021208088832 | FAJAR HIDAYAT | 2012-08-31 | - |
16:35:14 |
| 061204291133 | YUSTIANA PRIMA P | 2012-08-31 | 15:15:42 |
17:30:21 |
+----------------+---------------------+-------------+-------------+-------------+
I've tried with a simple query that just left the join of the two tables
but the result is still the same.The result just show heri (NIP = 22) and
OKTARISA Halida (NIP = 121 202 108 834) as the results who employees are
not present
Query
SELECT p.Per_Code AS NIP,
p.Per_Name AS Nama,
a.Date_Time as adate
FROM HR_Personnel AS p
LEFT JOIN TA_Record_Info a
ON p.ID=a.Per_Code
Order BY a.Date_Time
Please help me out from my problem. thanks