Generate From-To list with grouping columns

[Fuii]

Hola,

from an internal source about employees I'm trying to fetch a list about the employees team assignments.
For every date the source got the employee reference and the team assignment reference.

To use that for other queries I need to kinda aggregate it from a daily list, into a from-to list; grouped by the employee and team reference.



The source-picture (picture3) has the structure of the data source. For every single day (days can miss if OFF/PTO) the assignment per employee.
The qry-picture (picture2) shows my puny attempt. If the same team is being switched multiple times it ignores the in-between changes and causing overlapping from-to results (see picture1).

The intendedresult-picture (picture4) is what I ultimately need.
Data by employee + team with the respective start & end time that does not have overlapping entries.

Any hints appreciated.

Happy new year btw!

[Bulzie]

Doesn't look like your table has a primary key like an autonumber value/field? If not you should add it.

Thinking maybe nested queries.
Query1 = Fields (we_id and team_id) Group on both to get unique records.
Query2 = all 3 fields, group on we_id and team_id and do Min on date_full,
Query3 = all 3 fields, group on we_id and team_id and do Max on date_full
Query4 = include all 3 queries, Link Query1, Query2, Query3 on we_id and team_id and display (we_id and team_id from Query1 and MinDate_Full from Query2 and MaxDate_full from Query 3.
At least thats how I see it quickly in my head

[Fuii]

Thanks Bulzie.

I skipped qry1 as the source actually has a combined pKey of wd_id + date_full; so only 1 line per day+employee can exist.
Though the result in the end is the same as my attempt; the team_id 22 would show as 1 line item (after removing duplicates from the result) from 01/01 to 01/07 instead of 2 line items with 01/01 to 01/02 + 01/06 to 01/07 :/

Also attached the sample access file this time to make it easier.

[June7]

I suspect Access SQL doesn't have resources to do this alone and VBA will be needed.

Problem is distinguishing the two team 22 assignment periods and considering them separately. If records had assignment numbers, this could be fairly simple. First 2 team 22 records would be assignment 1 and last 2 would be assignment 3. A VBA procedure could populate field with this value.

Sub Assignment()
Dim rs As DAO.Recordset, x As Long, sWD As String, sT As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM source ORDER BY wd_id, date_full")
sWD = rs!wd_id
sT = rs!team_id
x = 1
Do While Not rs.EOF
    If sWD <> rs!wd_id Then
        x = 0
        sWD = rs!wd_id
    End If
    If sT <> rs!team_id Then
        x = x + 1
        sT = rs!team_id
    End If
    rs.Edit
    rs!AssignNum = x
    rs.Update
    rs.MoveNext
Loop
End Sub

Then query to get min and max dates for each assignment.

SELECT source.wd_id, source.team_id, source.AssignNum, Min(source.date_full) AS sDate, Max(source.date_full) AS eDate
FROM source
GROUP BY source.wd_id, source.team_id, source.AssignNum
ORDER BY source.AssignNum;

wd_id	team_id	AssignNum	sDate	eDate
1003423	22	1	1/1/2024	1/2/2024
1003423	17	2	1/3/2024	1/5/2024
1003423	22	3	1/6/2024	1/7/2024

[Fuii]

I dont mind using VBA; its in use a lot already.

However, to make your solution work June7 I would now also need a script that adds that AssignNum values either to the archive (source table) or on the fly as interim output to make the Assignment() script work - speaking of 100k+ records here from thousands of peeps.
Any suggestion for that?

I can't change the original report source (3rd party); only adding stuff as of my local version.

[Fuii]

Scratch that, misunderstood it; the VBA script is to update the source and then run the SQL code on it.
That works fine, thanks - also tested with a 2nd wd_id to validate that the script keeps enumerate properly.

Though, that now leads to another issue.

If a single day does not appear in the source, the output is skipping that day too.
On first glance its correct, but I'll need to have the "prior assignment" be applied to that day (as part of the from-to range).

Here in that result the source does not have data for the 2nd Jan.



Thus the output skips that day too.


However, I will need that the prior entry that only lasts until Jan 1st is extended to the 2nd in that case (or whatever the last "missing date" before the next entry is), like:


Updated sample file attached.

That probably can't be done with the same script, but another one checking for gaps (DateDif > 1) with index -1 of current row?

[CJ_London]

for your edate problem, try this sql

SELECT Q1.wd_id, Q1.team_id, Q1.AssignNum, Q1.sDate, [edat]-([edat]=[sdate]) AS eDate
FROM (SELECT source.wd_id, source.team_id, source.AssignNum, Min(source.date_full) AS sDate, Max(source.date_full) AS eDat
FROM source
GROUP BY source.wd_id, source.team_id, source.AssignNum
ORDER BY source.AssignNum)  AS Q1;

edit: this solution is based on your description. I did not understand what 'or whatever the last "missing date" before the next entry is' means. What about the last entry where there is no later date?

or on the fly as interim output

you might want to take a look at the solution in this thread which on the face of it is very similar to yours in generating an assign num value

https://www.accessforums.net/showthread.php?t=89214

the function will need some modification to meet your specific requirements for incrementing

This way you might find better to get your dates in order before generating your assign num value

[madpiet]

Are you just trying to do something simple like exploding a (startDate, endDate) pair to all the dates in the range? If that's the case scrap all this VBA glop and just create a calendar table.

then you can just outer/cross join with the calendar table and filter the dates down for each record.
SELECT <other stuff>, c.TheDate
FROM <other stuff> o INNER JOIN Calendar c ON (c.TheDate >= o.StartDate AND c.TheDate<=o.EndDate)

and you get one record per date for each <other stuff> record. No VBA required. I have nothing against VBA, but I'm not sure it's necessary here. And it doesn't scale at all.

[June7]

madpiet, there is no enddate field in table. It must be obtained from a subsequent record. And this process is complicated by nature of data and what OP wants to output. Actually, goal is to compact records, not explode.