I’M trying to generate SQL TRANSFORM,INNER JOIN AND GROUP BY WITH DATABASE MS ACCESS IN VB.NET.

I don’t want to use functions in MS Access because I use SQL for VB.NET.

Is there any other method? please guide me.

Thanks

TRANSFORM Max(ABSEN.TIME) AS MaxOfTIME
SELECT ABSEN.ID AS ID, MASTERID.NAMEID AS NAMEID, MASTERID.LOCATION AS LOCATION, ABSEN.DATE AS [DATE], format(ABSEN.DATE,'dddd') AS DAYS, ABSEN.STATUS AS STATUS, ABSEN.INFO2 AS INFO2
FROM ABSEN INNER JOIN MASTERID ON ABSEN.ID = MASTERID.ID
GROUP BY ABSEN.ID, MASTERID.NAMEID, MASTERID.LOCATION, ABSEN.DATE, format(ABSEN.DATE,'dddd'), ABSEN.STATUS, ABSEN.INFO2
PIVOT ABSEN.INOUT IN ('IN','OUT');

Public Class ABSEN
    Public Property ID As Integer
    Public Property NAMEID As String
    Public Property LOCATION As String
    Public Property [DATE] As DateTime
    Public Property DAYS As String
    Public Property DEFAULTIN As String
    Public Property DEFAULTOUT As String
    Public Property DEFAULTREST As String
    Public Property STATUS As String
    Public Property IN As String
    Public Property OUT As String
    Public Property DURATIONWORK As String
    Public Property LATE As String
    Public Property INFO2 As String
End Class

TABLE ABSEN

ID  DATE            TIME    INOUT   STATUS         INFO2
1000    25-05-2024  08:00   IN  PRESENT 
1000    25-05-2024  16:00   OUT PRESENT 
1000    26-05-2024      IN  NO PRESENT  NO WORK
1000    26-05-2024      OUT NO PRESENT  NO WORK
1001    25-05-2024  08:00   IN  PRESENT 
1001    25-05-2024  16:00   OUT PRESENT 
1001    26-05-2024      IN  NO PRESENT  NO WORK
1001    26-05-2024      OUT NO PRESENT  NO WORK

TABLE MASTERDAYS

COMBINE      LOCATION   DAY      DEFAULTIN  DEFAULTOUT  DEFAULTREST
Asaturday   A   Saturday    08:00   15:30            01:00:00
Bsaturday   B   Saturday    07:30   15:00            01:00:00

TABLE MASTERID

ID  NAMEID  LOCATION
1000    JACK    A
1001    ROY B

RESULT FROM CODE

ID  NAMEID  LOCATION DATE           DAYS            STATUS         INFO2    IN  OUT
1000    JACK    A   25-05-2024  Saturday    PRESENT             08:00   16:00
1000    JACK    A   26-05-2024  Sunday          NO PRESENT  NO WORK     
1001    ROY B   25-05-2024  Saturday    PRESENT             08:00   16:00
1001    ROY B   26-05-2024  Sunday          NO PRESENT  NO WORK     

DESIRED OUTPUT

• COLUMN DEFAULTIN, DEFAULTOUT AND DEFAULTREST FROM TABLE MASTERDAYS
• COLUMN DURATIONWORK (OUT-DEFAULTIN-DEFAULTREST)
• COLUMN LATE ((IN-DEFAULTIN)-(DEFAULTOUT-OUT)), For in < 08:00 and also out > 15:30 then the result is blank or empty in columns LATE

ID  NAMEID  LOCATION DATE           DAYS        DEFAULTIN   DEFAULTOUT  DEFAULTREST STATUS    IN    OUT DURATIONWORK    LATE    INFO2
1000    JACK    A   25-05-2024  Saturday    08:00   15:30            01:00         PRESENT  08:10   15:25   06:15           00:15   
1000    JACK    A   26-05-2024  Sunday                                     NO PRESENT                                                NO WORK
1001    ROY B   25-05-2024  Saturday    07:30   15:00             01:00        PRESENT  08:00   16:00   07:00       
1001    ROY B   26-05-2024  Sunday                                     NO PRESENT                    NO WORK


DESIRED OUTPUT IN IMAGE

[![enter image description here](https://i.sstatic.net/8M5X7AuT.png)](https://i.sstatic.net/8M5X7AuT.png)