Translating rows into columns

[Simbiose]

Hello everyone.
I'm new around here and I have a problem to which I'm not sure how to solve nor how to better entitle it.

I'm working on a company that has a time clock machine, where every employee is required to register their entries and exits through their fingerprints.
The employees are divided in several groups, where the most relevants ones to this case are the office and logistics employees.
The logistics operators are required to register their entry and exit for the lunch period as well and may not register in several occasions, such as working hours compensation, etc. This means that every logistics operator will have a maximum of 4 registrations per day (2 entries and 2 exits).

There are a few things to take into consideration:

• Sometimes the employees don't register their entries and/or exits (making everything very chaotic...)
• The lunch period isn't accounted into the current database tables (meaning, the Human Resources Department, pretty much has to check the entry and exit timestamps and calculate the difference for the lunch period...)

The result that is needed to be achieved is to calculate the working hours and lunch period hours of each employee in order to calculate the total workings hours per day, considering the days where the employees haven't shown up for work, no matter the reason.

The current database that holds significant information for this are 3:

1. Userinfo (Information regarding the users registered on the time clock machine)
2. Checkinout (Information regarding the entries and exits each employee)
3. Dept (Information regarding to each department)

So, the logic is: every employee has its department and their respect entries and exits for each day.

A problem I found on the database (and I have no idea why this happens...). Since employees often forget to register their entries and/or exits, I tried to retrieve every record between the tables Userinfo and Checkinout, whether the employees have registered on not, meaning, I used a LEFT JOIN between the two tables, with the intention of retrieving null values for the Checkinout table on the days that the employees didn't register their fingerprint.
This isn't working, the records that are returned are exclusive only to the days where the employees register their fingerprint and this makes things even more complicated...

I'm using the following query to retrieve all the entries and exits of every employee between a range of days, prepared for excel connection:

SELECT P.CheckTime, P.CheckType, U.Name, D.DeptName
FROM ((Checkinout P
LEFT JOIN Userinfo U
ON P.Userid = U.Userid)
LEFT JOIN Dept D
ON U.Deptid = D.Deptid)
WHERE P.CheckTime BETWEEN ? AND (DATEADD('d',1,?))
ORDER BY U.Name

This works fine and dandy as you can see on the below image:



Subtitles:
I - Entry
O - Exit
Armz Escritório - Office Employees
Armz Plataforma - Logistics Employees

However, the table that is returned poses as real problem for what is to be achieved. Since we need to check the working hours for each day, we need to subtract the exit timestamp from the entry timestamp. This is not possible as each record returns the timestamp regarding if the record is an entry or exit.
To achieve the goal we'd need to have a query returning something like this:



This way it would already be possible to calculate the total working hours for each employee, but we'd still have the problem for the lunch period, because for the logistics operators (see green marked cells), we still have 2 entries and 2 exits, where which are on differente rows.

In order to be able to overcome the problem, we'd need our query to return something like this:



Is there any possible way to create a query to return the results shown on the last above image? May anyone help?

P.S. If there's any other information need or if is there anything that's confusing, let me know

[Simbiose]

Here's a somewhat of an update.

I'm still trying to achieve my goal through the access query and still not there, but it feels I'm getting closer.
So far I have the following query:

SELECT U.Name,
(SELECT CheckTime
FROM Checkinout
WHERE Userid = U.Userid
AND CheckType = "I"
AND CheckTime = P.CheckTime) AS Entry,
(SELECT CheckTime
FROM Checkinout
WHERE Userid = U.Userid
AND CheckType = "O"
AND CheckTime = P.CheckTime) AS Exit,
D.DeptName
FROM ((Checkinout P
INNER JOIN Userinfo U
ON P.Userid = U.Userid)
INNER JOIN Dept D
ON U.Deptid = D.Deptid)
WHERE P.CheckTime BETWEEN ? AND (DATEADD('d',1,?))
ORDER BY U.Name;

And I'm getting the following datatable:



I'll still try to work for the solution, while hoping for some help from here.
Thanks in advance.

[NTC]

This is not going to be possible via a single query - well at least not without being horrendously complicated code and will run slow.

Instead you must make a series of queries that segregate the data (these are called 'record sets' not tables as they only exist as the result of a query) but you can make queries using another query as the starting point and then in the end you will need a query that joins your queries together in order to get the fields side by side. One key thing you must do in your first query is create a calculated field that is just Date (not time) as you will need to join on Date and Name. So you need that field.

[Simbiose]

This is not going to be possible via a single query - well at least not without being horrendously complicated code and will run slow.

Instead you must make a series of queries that segregate the data (these are called 'record sets' not tables as they only exist as the result of a query) but you can make queries using another query as the starting point and then in the end you will need a query that joins your queries together in order to get the fields side by side. One key thing you must do in your first query is create a calculated field that is just Date (not time) as you will need to join on Date and Name. So you need that field.

I think I understand where you're going with this, but how would I link all the separated queries into the one I'd need to retrieve the results into excel?

[NTC]

you join them by the common fields; date & name - 2 joins

[Simbiose]

I feel like crying lol...
By following your logic I did the following:

1. Created the BaseData query, with the following statement:

SELECT U.Name, DateValue(P.CheckTime) AS CheckDate, D.DeptName
FROM (Userinfo AS U INNER JOIN Checkinout AS P ON U.Userid = P.Userid) INNER JOIN Dept AS D ON U.Deptid = D.Deptid;

2. Created the Entries query, with the following statement:

SELECT U.Name, TimeValue(P.CheckTime) AS Entry, DateValue(P.CheckTime) AS EntryDate
FROM Userinfo AS U INNER JOIN Checkinout AS P ON U.Userid = P.Userid
WHERE P.CheckType = "I";

3. Created the Exits query, with the following statement:

SELECT U.Name, TimeValue(P.CheckTime) AS Exit, DateValue(P.CheckTime) AS ExitDate
FROM Userinfo AS U INNER JOIN Checkinout AS P ON U.Userid = P.Userid
WHERE P.CheckType = "O";

4. Finally, created the Attendances query, with the following statement:

SELECT [BaseData].CheckDate, [BaseData].Name, [BaseData].DeptName, Entries.Entry, [Exits].Exit
FROM (BaseData INNER JOIN Entries ON ([BaseData].Name=Entries.Name) AND ([BaseData].CheckDate=Entries.EntryDate)) INNER JOIN Exits ON ([BaseData].Name=[Exits].Name) AND ([BaseData].CheckDate=[Exits].ExitDate)
WHERE [BaseData].CheckDate Between #10/12/2015# And #10/15/2015#;

The first query returns all the pertinent base data without any filters.
Then, the seconds and third queries return all the fingerprint entry and exit registrations, respectively.
Finally, the fourth and last query returns the data I want to export to excel.

The final query reproduces the following results:



I'm getting really close, but... it's not quite there yet.
The logistics operators (check A. Pinheiro's exemple for instance) have a maximum of 4 records each, where the Entry and Exit timestamps repeat itself twice.
How do I correct this?

[Simbiose]

Finally! I solved my problem.
All I needed to do was to create a query that would return a dataset for each situation, meaning, I'd need a dataset with the records of all morning entry fingerprints, lunch exit fingerprints and so on.
Then I'd just need to make the final query return the results according to each need, where I indicated the hour range for morning, lunch and evening fingerprints.
It's all working good now.