Generate SQL TRANSFORM with MS ACCESS

Dear Sir

I'm Trying to generate SQL TRANSFORM with MS ACCESS via dapper or via linq dapper to datagridview in VB.NET.

 link database ms access

Is there any other method? Please guide me.

Thanks

Table ABSEN

 Table MASTERDAYS

Table MASTERID

Imports System.Data.OleDb
Imports Dapper

Public Class Form1
    Dim ABSENService As New ABSENService()
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = ABSENService.GetAllStatus.ToList()
    End Sub
End Class
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
Public Class ABSENService
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\TRIALABSEN.accdb;Persist Security Info=False;"
    End Function
    Private ReadOnly _conn As OleDbConnection
    Private _connectionString As String = GetOledbConnectionString()
    Public Function GetAllStatus() As IEnumerable(Of ABSEN)
        Dim sql2 = <sql>
      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');
        </sql>.Value
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ABSEN)(sql2).ToList()
        End Using
    End Function
End Class

Result From code

ID     NAMEID	LOCATION      DATE	DAYS	DEFAULTIN	DEFAULTOUT	DEFAULTREST	STATUS	IN	OUT	DURATIONWORK	LATE	INFO2
1000	JACK	A	     25-May-24	Saturday PRESENT	08:00	16:00			
1000	JACK	A	     26-May-24	Sunday   NO PRESENT					      NO WORK
1001	ROY	B	     25-May-24	Saturday PRESENT	08:00	16:00			
1001	ROY	B	     26-May-24	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