Time Attendant

[kurowa]

Time attendant program

I have table T_Personnel like this
+----+------+----------+---------+
| ID | NIK | Name | Dept_ID |
+----+------+----------+---------+
| 1 | 1001 | Ann | 1 |
| 2 | 1002 | Bond | 1 |
| 3 | 1003 | Chan | 2 |
| 4 | 1004 | Dea | 2 |
| 5 | 1005 | Eno | 3 |
+----+------+----------+---------+

And table T_TimeAttendant
+----+------+---------------------+--------+
| ID | NIK | DateTime | In_Out |
+----+---- -+---------------------+--------+
| 1 | 1001 | 01/10/2013 7:30:01 | 1 |
| 2 | 1002 | 01/10/2013 7:31:05 | 1 |
| 3 | 1003 | 01/10/2013 7:32:21 | 1 |
| 4 | 1004 | 01/10/2013 7:45:16 | 1 |
| 5 | 1001 | 01/10/2013 17:00:47 | 2 |
| 6 | 1002 | 01/10/2013 17:02:01 | 2 |
| 7 | 1003 | 01/10/2013 17:03:22 | 2 |
| 8 | 1001 | 02/10/2013 7:29:01 | 1 |
| 9 | 1002 | 02/10/2013 7:34:37 | 1 |
| 10 | 1001 | 02/10/2013 17:05:16 | 2 |
| 11 | 1002 | 02/10/2013 17:08:28 | 2 |
+----+------+---------------------+--------+

My query is

SELECT Tmp.P.NIK, Max(Tmp.P.Name) AS Name, Tmp.Date_IO, Max(Tmp.TimeIn_1) AS Time_In, Max(Tmp.TimeOut_1) AS Time_Out
FROM [SELECT P.NIK, P.Name, LEFT(TA.DateTime,10) AS Date_IO, RIGHT(TA.DateTime,8) AS TimeIn_1, ' ' AS TimeOut_1
FROM T_Personnel AS P LEFT JOIN T_TimeAttendant AS TA ON P.NIK=TA.NIK
WHERE TA.In_Out = 1 OR TA.In_Out IS NULL
UNION ALL
SELECT P.NIK, P.Name, LEFT(TA.DateTime,10) AS Date_IO, ' ' AS TimeIn_1, RIGHT(TA.DateTime,8) AS TimeOut_1
FROM T_Personnel AS P LEFT JOIN T_TimeAttendant AS TA ON P.NIK=TA.NIK
WHERE TA.In_Out = 2 OR TA.In_Out IS NULL
]. AS Tmp
GROUP BY Tmp.Date_IO, Tmp.P.NIK;

The result
+------+-------+------------+---------+----------+
| NIK | Name | Date_IO | Time_In | Time_Out |
+------+-------+------------+---------+----------+
| 1005 | Eno | | | |
| 1001 | Ann | 01/10/2013 | 7:30:01 | 17:00:47 |
| 1002 | Bond | 01/10/2013 | 7:31:05 | 17:02:01 |
| 1003 | Chan | 01/10/2013 | 7:32:21 | 17:03:22 |
| 1004 | Dea | 01/10/2013 | 7:45:16 | |
| 1001 | Ann | 02/10/2013 | 7:29:01 | 17:05:16 |
| 1002 | Bond | 02/10/2013 | 7:34:37 | 17:08:28 |
+------+-------+------------+---------+----------+

How to make table like this ?
+------+-------+------------+---------+----------+
| NIK | Name | Date_IO | Time_In | Time_Out |
+------+-------+------------+---------+----------+
| 1001 | Ann | 01/10/2013 | 7:30:01 | 17:00:47 |
| 1002 | Bond | 01/10/2013 | 7:31:05 | 17:02:01 |
| 1003 | Chan | 01/10/2013 | 7:32:21 | 17:03:22 |
| 1004 | Dea | 01/10/2013 | 7:45:16 | |
| 1005 | Eno | 01/10/2013 | | |
| 1001 | Ann | 02/10/2013 | 7:29:01 | 17:05:16 |
| 1002 | Bond | 02/10/2013 | 7:34:37 | 17:08:28 |
| 1003 | Chan | 02/10/2013 | 7:31:05 | 17:02:01 |
| 1004 | Dea | 02/10/2013 | | |
| 1005 | Eno | 02/10/2013 | | |
+------+-------+------------+---------+----------+

Thanks before for the enlightenment

Btw I try to make it look good but auto trimming in this forum ruin the tables

Edit: Database file attached

 

[kurowa]

Edit the tables and attached the database file..

 

[namliam]

When creating tables and posting code try using the # sign in the post menu or wrap it yourself by typing [cade] and [/cade] (replace the a by o to make it work)
Code wrapped segments do not get trimmed.

Have you tried to add: "Order by" statement at the end of your query?

Your union query is a pretty hard solution for something that seems quite simple to do....

SELECT T_TimeAttendant.NIK
     , Max(IIf([in_out]=1,[DateTime],Null)) AS DateIn
     , Max(IIf([in_out]=2,[DateTime],Null)) AS DateOut
FROM T_TimeAttendant
GROUP BY T_TimeAttendant.NIK;

This query will create dummy records for each Person to each date:

SELECT T_Personnel.ID, T_VDateRange.Date
FROM T_Personnel, T_VDateRange;

Which you can then Left Join to the above query to get (what I think) is what you are trying to do

 

[JHB]

Use a crosstab query instead of an union query.
Open the attached database and run the query "T_TimeAttendant_Crosstab", below is insert a picture which shows the query result.

 

[kurowa]

When creating tables and posting code try using the # sign in the post menu or wrap it yourself by typing [cade] and [/cade] (replace the a by o to make it work)
Code wrapped segments do not get trimmed.

Thanks for the info

Have you tried to add: "Order by" statement at the end of your query?

Your union query is a pretty hard solution for something that seems quite simple to do....

SELECT T_TimeAttendant.NIK
     , Max(IIf([in_out]=1,[DateTime],Null)) AS DateIn
     , Max(IIf([in_out]=2,[DateTime],Null)) AS DateOut
FROM T_TimeAttendant
GROUP BY T_TimeAttendant.NIK;

=> Only results the last date of each person..

This query will create dummy records for each Person to each date:

SELECT T_Personnel.ID, T_VDateRange.Date
FROM T_Personnel, T_VDateRange;

Which you can then Left Join to the above query to get (what I think) is what you are trying to do

Actually I have made query Q_PxD (in the database file that I attached yesterday) that created that dummy table but I failed when joining the table. What's wrong with this query?

SELECT P.NIK, P.Name, P.Date, T.Time_In, T.Time_Out
FROM Q_PxD AS P LEFT JOIN Q_HOW AS T ON ((P.Date = T.Date_IO) AND (P.NIK = T.NIK));

=> The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

Q_How is my previous query that use union all
Does JOIN command can not accept several condition (AND, OR, etc) ?

 

[kurowa]

Use a crosstab query instead of an union query.
Open the attached database and run the query "T_TimeAttendant_Crosstab"

That works !! Thank you sir, I just know that there are crosstab quey, pivot and transform. Are those exist only in acess or all database?

Now i am thinking about dummy date tables and fill it on the fly with the parameters that we supply (populate one column with range of date). Is it possible to make with query only? Someone make it in SQL server but he make it with function. Unfortunately my knowledge is still very limited to mimic it in access

 

[namliam]

=> Only results the last date of each person..

Yes forgot to add the date to it...

SELECT T_TimeAttendant.NIK, DateValue([DateTime]) AS DateOnly, Max(IIf([in_out]=1,[DateTime],Null)) AS DateIn, Max(IIf([in_out]=2,[DateTime],Null)) AS DateOut
FROM T_TimeAttendant
GROUP BY T_TimeAttendant.NIK, DateValue([DateTime]);

Saved above as Query1

SELECT Q_PxD.NIK, Q_PxD.Name, Query1.DateOnly, Query1.DateIn, Query1.DateOut
FROM Q_PxD LEFT JOIN Query1 ON (Q_PxD.Date = Query1.DateOnly) AND (Q_PxD.NIK = Query1.NIK);

I can very much advice you not to mix types, you are doing Left and Right on date fields, which is VERY iffy at best... instead use the datevalue and timevalue functions.`

Also use more descriptive names for your querys (IMHO)

And

 

[JHB]

That works !! Thank you sir, I just know that there are crosstab quey, pivot and transform. Are those exist only in acess or all database?

Yes in some ex. MS-Sql server 2008, but here it isn't so easy as in MS-Access.

Now i am thinking about dummy date tables and fill it on the fly with the parameters that we supply (populate one column with range of date). Is it possible to make with query only?

It looks like namliam is giving you a working solution, (if not the write again.).

 

[namliam]

depends on if you need every date incl holidays or if you just need applicible dates
If the later, you can just use a distinct query and datevalue the dates to get all the dates without the need for the dummy table.

Filling a dummy table with every posssible date for the comming 100 years should be pretty easy to do if you need every possible date.... Or just copy/paste from excel where its also peanuts to do