Sql TRANSFORM with calculation time in MS Access

I'm Trying to make Sql TRANSFORM with calculation time in column in and out.

I want the answer in 1 query and I don't want to use the function in ms access because I want to use the sql in vb.net

Please Guide me

Thanks

Table ABSEN

Table MASTERDAYS

TRANSFORM Max(ABSEN.Time) AS MaxOfTIME
SELECT ABSEN.Date AS [DATE], 
       Format(ABSEN.Date,'dddd') AS DAYS, 
       ABSEN.STATUS AS STATUS, 
       IIF(ABSEN.STATUS = 'NOT PRESENT', '',MASTERDAYS.DEFREST) AS DEFREST
FROM ABSEN INNER JOIN MASTERDAYS AS MASTERDAYS ON (MASTERDAYS.DAY = FORMAT(ABSEN.DATE,'dddd')) AND (MASTERDAYS.LOCATION = ABSEN.LOCATION)
GROUP BY ABSEN.Date, 
         Format(ABSEN.Date,'dddd'), 
         ABSEN.STATUS, MASTERDAYS.DEFREST
PIVOT ABSEN.INOUT In ('IN','OUT');

Result From Code

Desired output

DUROFWORK = (ABSEN.Time IN - ABSEN.Time OUT - MASTERDAYS.DEFREST) LATEIN = (ABSEN.Time IN - MASTERDAYS.DEFIN)

For status NOT PRESENT I want to make blank for DEFREST,IN,OUT,DUROFWORK,LATEIN

Info data type 

Public Class DTOABSENTRANSFORMREPORT
    Public Property [DATE] As DateTime
    Public Property DAYS As String
    Public Property STATUS As String
    Public Property DEFREST As String
    Public Property TIME As String
    Public Property [IN] As String
    Public Property OUT As String
    Public Property DUROFWORK As String
    Public Property LATEIN As String
End Class

Update testing table as on 29-08-2024

Table ABSEN

Table MASTERDAYS

Table MASTERID

SELECT 
    AIn.ID AS [ID]
 ,  AIn.Date AS [DATE]
  , MD.day AS [Day of Week]
  , MI.NAMEID
  , AIn.status
  , IIF(AIn.STATUS = 'NOT PRESENT', '',MD.DEFREST) AS DEFREST
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AIn.TIME) AS [IN]
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AOut.TIME) AS OUT
  , IIF(AIn.STATUS = 'NOT PRESENT', '',
          dateadd("s", datediff("s", AIn.TIME, AOut.TIME)-datediff("s", 0, MD.DefRest), 0)) AS DUROFWORK
  , IIF(cdate(AIn.[TIME]) > cdate(MD.DEFIN), format(dateadd("s", datediff("s", cdate(MD.DEFIN),
          cdate(AIn.[TIME])),0),"hh:nn:ss"),'') AS LATEIN
FROM ((ABSEN AS AIn 
       INNER JOIN MASTERDAYS AS MD ON AIn.LOCATION = MD.LOCATION) 
       INNER JOIN MASTERID AS MI ON MI.ID=AIn.ID)
       LEFT JOIN (SELECT * 
                  FROM ABSEN AS AOut1 
                  WHERE AOut1.INOUT='OUT') AS AOut ON (AOut.LOCATION=AIn.LOCATION) AND (AOut.DATE=AIn.DATE)
WHERE format(AIn.Date,"dddd")=MD.day
      AND  AIn.INOUT='IN' AND AIn.OPTION IS NULL AND AOut.OPTION IS NULL AND MI.POSITIONID='STAFF';

Result from code

Desired Result

  | ID   | DATE     | DAYS     | NAMEID   |  STATUS      |DEFREST  |IN       |OUT      |DUROFWORK|LATEIN   |
    | -----| -------- | -------- |  --------|  --------    |-------- |-------- |-------- |-------- |-------- |
    | 1000 | 26-Aug-24| Monday   | A        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1000 | 27-Aug-24| Tuesday  | A        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1000 | 28-Aug-24| Wednesday| A        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1000 | 29-Aug-24| Thursday | A        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1000 | 30-Aug-24| Friday   | A        |   PRESENT    | 01:30:00| 08:00:00| 17:00:00| 07:30:00|         |
    | 1000 | 31-Aug-24| Saturday | A        |   PRESENT    | 01:30:00| 08:00:00| 17:00:00| 06:10:00|00:20:00 |
    | 1000 | 01-Sep-24| Sunday   | A        |   NOT PRESENT|         |         |         |         |         |          
    | 1001 | 26-Aug-24| Monday   | B        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
    | 1001 | 27-Aug-24| Tuesday  | B        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
    | 1001 | 28-Aug-24| Wednesday| B        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
    | 1001 | 29-Aug-24| Thursday | B        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
    | 1001 | 30-Aug-24| Friday   | B        |   PRESENT    | 01:30:00| 08:00:00| 17:00:00| 07:30:00|01:00:00 |
    | 1001 | 31-Aug-24| Saturday | B        |   PRESENT    | 01:30:00| 08:00:00| 17:00:00| 06:10:00|00:20:00 |
    | 1001 | 01-Sep-24| Sunday   | B        |   NOT PRESENT|         |         |         |         |         |
    | 1002 | 26-Aug-24| Monday   | C        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1002 | 27-Aug-24| Tuesday  | C        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1002 | 28-Aug-24| Wednesday| C        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1002 | 29-Aug-24| Thursday | C        |   PRESENT    | 01:00:00| 08:00:00| 17:00:00| 08:00:00|         |
    | 1002 | 30-Aug-24| Friday   | C        |   PRESENT    | 01:30:00| 08:00:00| 17:00:00| 07:30:00|         |
    | 1002 | 31-Aug-24| Saturday | C        |   PRESENT    | 01:30:00| 08:00:00| 17:00:00| 06:10:00|00:20:00 |
    | 1002 | 01-Sep-24| Sunday   | C        |   NOT PRESENT|         |         |         |         |         |              

Problem Solved AND (AOut.ID=AIn.ID)

SELECT 
    AIn.ID AS [ID]
 ,  AIn.Date AS [DATE]
  , MD.day AS [Day of Week]
  , MI.NAMEID
  , AIn.status
  , IIF(AIn.STATUS = 'NOT PRESENT', '',MD.DEFREST) AS DEFREST
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AIn.TIME) AS [IN]
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AOut.TIME) AS OUT
  , IIF(AIn.STATUS = 'NOT PRESENT', '',
          dateadd("s", datediff("s", AIn.TIME, AOut.TIME)-datediff("s", 0, MD.DefRest), 0)) AS DUROFWORK
  , IIF(cdate(AIn.[TIME]) > cdate(MD.DEFIN), format(dateadd("s", datediff("s", cdate(MD.DEFIN),
          cdate(AIn.[TIME])),0),"hh:nn:ss"),'') AS LATEIN
FROM ((ABSEN AS AIn 
       INNER JOIN MASTERDAYS AS MD ON AIn.LOCATION = MD.LOCATION) 
       INNER JOIN MASTERID AS MI ON MI.ID=AIn.ID)
       LEFT JOIN (SELECT * 
                  FROM ABSEN AS AOut1 
                  WHERE AOut1.INOUT='OUT') AS AOut ON (AOut.LOCATION=AIn.LOCATION) AND (AOut.DATE=AIn.DATE)           AND (AOut.ID=AIn.ID)
WHERE format(AIn.Date,"dddd")=MD.day
      AND  AIn.INOUT='IN' AND AIn.OPTION IS NULL AND AOut.OPTION IS NULL AND MI.POSITIONID='STAFF';

If you can give up using TRANSFORM; then you can use something like:

SELECT 
    AIn.Date AS [DATE]
  , MD.day AS [Day of Week]
  , MI.NAMEID
  , AIn.status
  , IIF(AIn.STATUS = 'NOT PRESENT', '',MD.DEFREST) AS DEFREST
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AIn.TIME) AS [IN]
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AOut.TIME) AS OUT
  , IIF(AIn.STATUS = 'NOT PRESENT', '',
          dateadd("s", datediff("s", AIn.TIME, AOut.TIME)-datediff("s", 0, MD.DefRest), 0)) AS DUROFWORK
  , IIF(cdate(AIn.[TIME]) > cdate(MD.DEFIN), format(dateadd("s", datediff("s", cdate(MD.DEFIN),
          cdate(AIn.[TIME])),0),"hh:nn:ss"),'') AS LATEIN
FROM ((ABSEN AS AIn 
       INNER JOIN MASTERDAYS AS MD ON AIn.LOCATION = MD.LOCATION) 
       INNER JOIN MASTERID AS MI ON MI.ID=AIn.ID)
       LEFT JOIN (SELECT * 
                  FROM ABSEN AS AOut1 
                  WHERE AOut1.INOUT='OUT') AS AOut 
       ON (AOut.LOCATION=AIn.LOCATION) 
          AND (AOut.DATE=AIn.DATE)
          AND (AOut.ID=AIn.ID)
WHERE format(AIn.Date,"dddd")=MD.day
      AND  AIn.INOUT='IN';

I assumed that your 'other' table NAM In my case I held the 'time-only' columns in 'ShortText' (you may need to adjust, if yours is different.