I have table for permission

I want select query to group dates based on start date and end date

result query like this:

EDIT:

this is a part of my table for userID = 1370 and 2456. users request for permission and system approve or reject their request. each day can reject or approved. so when i want to response their request , either get them result of all 365 days of year separately , or group rows with start and end date period. –

CodePudding user response：

I solve it by user Defined(udf) function in vba.

Function partID(userID, rowID)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("select * from permission where userID = " & userID & " order by permission.date")
    
    i = 0
    p = ""
    
    Do While rst.EOF = False
        If p <> rst("permission") Then
            i = i   1
            p = rst("permission")
        End If
        If rowID = rst("ID") Then
            Exit Do
        End If
        rst.MoveNext
    Loop
    partID = i

End Function

then select query and group it.

SELECT UserID, Min(Date) AS DateFrom, Max(Date) AS DateTill, Permission
FROM Permission
GROUP BY UserID, Permission, partid([userid],[id])
ORDER BY UserID, partid([userid],[id]);

and it works... :D

CodePudding user response：

It's a little unclear what corolation there is between these date? (Relationship) im guessing that these are related somehow ie a Job number of sorts??? (U need to clarify) Ie ID 3 4 in the 1st Tbl??? And im guessing that if there is a Relationship you would need to show/add that to your Qry IE Table with dates, another with Job ref And also, im guessing, if there is a from date and a null till date then Till date = From date?