how to query based on start and end period dates with MS ACCESS in VB.NET

I'M trying to query based on start and end period dates with MS ACCESS in VB.NET

Table ABSEN can union with table HELPERABSEN

so I want to take the start date and end date from the table PERIOD with where condition based on PERIODNAME from table PERIOD.

if the start date and end date of the data do not exist in the Absen table in the DATEABSEN field then the query creates a new record according to ID2 and INOUT

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

if there is any other method please guide me

Thanks

Public Class Form1
    Private AbsenService As New ABSENservice()
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = AbsenService.GetAbsenfull()
    End Sub
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 Sub New()
        _conn = New OleDbConnection(_connectionString)
    End Sub
    Public Function GetAbsenfull() As IEnumerable(Of ABSEN)
        Dim sql = $"SELECT ABSEN.ID2 AS [ID2],MASTERID2.NAMEID2 AS [NAMEID2],ABSEN.INOUT AS [INOUT],ABSEN.DATEABSEN AS [DATEABSEN],Format(ABSEN.TIME,'hh:mm:ss') AS [TIME],ABSEN.STATUS AS [STATUS] FROM ABSEN INNER JOIN MASTERID2 ON ABSEN.ID2 = MASTERID2.ID2"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of ABSEN)(sql).ToList()
        End Using
    End Function
End Class
Public Class ABSEN
    Public Property ID() As Integer
    Public Property ID2() As String
    Public Property NAMEID2() As String
    Public Property INOUT() As String
    Public Property DATEABSEN() As DateTime
    Public Property TIME() As String
    Public Property STATUS() As String

End Class

RESULT FROM CODE

Sample Data

Table ABSEN

Table HELPERABSEN

Table MASTERID2

Table PERIOD

DESIRED RESULT ALL

but I want to base it on each period

I think I would build the list by some fancy looping, but you can get close with SQL.

First create a table or a query that will return 10 numbers named TEN:

SELECT DISTINCT [Id] Mod 10 AS Factor
FROM MSysObjects
WHERE MSysObjects.Id > 0

and an INOUT table having one field INOUT and two records:

IN  
OUT

Next, create a UNION query, ALLABSEN:

SELECT 
    ID2, INOUT, DATEABSEN, [TIME], STATUS
FROM 
    ABSEN
UNION 
SELECT 
    ID2, INOUT, DATEABSEN, [TIME], STATUS
FROM 
    HELPERABSEN

and a Cartesian query, ALLDATES, to generate all dates:

SELECT 
    PERIOD.PERIODNAME, [Factor]+[STARTDATE] AS [DATE]
FROM 
    PERIOD, 
    TEN
WHERE 
    [Factor]+[STARTDATE] Between [STARTDATE] And [ENDDATE]
ORDER BY 
    [Factor]+[STARTDATE]

and yet a Cartesian query, ALLID2DATES, to return all possible entries:

SELECT 
    MASTERID2.ID2, MASTERID2.NAMEID2, INOUT.INOUT, ALLDATES.Date
FROM 
    MASTERID2, 
    ALLDATES, 
    INOUT
WHERE
    MASTERID2.POSITION = 'STAFF'
ORDER BY 
    ALLDATES.Date, MASTERID2.ID2, INOUT.INOUT;

Finally, you can assemble the output query, ID2ALLABSEN:

SELECT 
    ALLID2DATES.ID2, ALLID2DATES.NAMEID2, ALLID2DATES.INOUT, ALLID2DATES.DATE, ALLABSEN.TIME, ALLABSEN.STATUS
FROM 
    ALLID2DATES 
LEFT JOIN 
    ALLABSEN 
    ON (ALLID2DATES.DATE = ALLABSEN.DATEABSEN) AND (ALLID2DATES.INOUT = ALLABSEN.INOUT) AND (ALLID2DATES.ID2 = ALLABSEN.ID2)
ORDER BY 
    ALLID2DATES.DATE, ALLID2DATES.ID2, ALLID2DATES.INOUT, [DATE]+[ALLABSEN].[TIME]

Output: