DATES - Grouping sets of sequential dates

[MSC]

Hi,

I'm trying to group assignments for people by the dates that assignment
started and ended - but only sequential assignments to be grouped.
eg:
Table: U
Fields:
ID - Long Int
Name - Text
Role - Text
assgn_start - Date
assgn_end - Date
Allocation - Long Int
Program - Text

Example:
L ID Name Role assgn_start assgn_end Allocation Program
1 994800 Owens, T BSA 01/01/2009 05/04/2009 100 Red
2 994800 Owens, T BSA 06/04/2009 01/05/2009 50 RM
3 994800 Owens, T BSA 06/04/2009 01/05/2009 50 Red
4 994800 Owens, T BSA 02/05/2009 05/06/2009 100 Red
5 994800 Owens, T BSA 06/06/2009 21/06/2009 100 Red
6 994800 Owens, T BSA 22/06/2009 31/07/2009 90 SII
7 994800 Owens, T BSA 22/06/2009 31/07/2009 10 Red

If I use the Min/Max or First/Last functions within a select query it groups
assignments 1, 4 & 5 starting 01-01-09 and end 21-06-09, as everything else
is the same.
BUT thats not a true reflection of the assignments, the only ones that
should be grouped are 4 & 5 as they follow on from each other.

So, I need help in grouping like assignments that follow on from each other.

I've posted this question before, but tried to be clearer this time - any
help appreciated.

Cheers
MSC

 

[Dale Fye]

Using the data you provided, what do you want the output of this query to
look like?

What fields are you grouping on (Program, ID, Name)?

I don't think this would be too difficult with the potential of only 1
follow-on assignment, but what do you want to happen if there is a third
consecutive assignment?

 

[MSC]

Hi,

Output would be:
994800 Owens, T BSA 01/01/2009 05/04/2009 100 Red
994800 Owens, T BSA 06/04/2009 01/05/2009 50 RM
994800 Owens, T BSA 06/04/2009 01/05/2009 50 Red
994800 Owens, T BSA 02/05/2009 21/06/2009 100 Red
994800 Owens, T BSA 22/06/2009 31/07/2009 90 SII
994800 Owens, T BSA 22/06/2009 31/07/2009 10 Red

so the query only groups the two rows that are the same with consecutive
dates.

Grouping on Program, Id, Name, Role and Allocation - basically everything
other that dates.

All consecutive assignments to be grouped, as long as the other fields are
the same, could be as many as 20 assignments.

Thanks for your help.

MSC

 

[Dale Fye]

MSC,

[Name] is a reserved word in Access and should not be used as a field name.

Assuming that the Assgn_End of one preceeds the Assgn_Start of the next by 1
day, in every instance, the following appears to work. I've joined the
table to itself on all of the appropriate fields, to determine whether the
record actually is the beginning of an "assignment". If there is a
predecessor, then it obviously is not the beginning of an assignment, so I
exclude that record from the result set. Then, I select the maximum value
from assgn_end out of all the records that match the rest of the fields.

This is a bit cumbersome, and may be very slow with large data sets.

SELECT BeginAsgnmt.L, BeginAsgnmt.Program, BeginAsgnmt.ID,
BeginAsgnmt.[Name], BeginAsgnmt.Role,
BeginAsgnmt.Allocation,
BeginAsgnmt.Assgn_Start,
(SELECT Max(assgn_end) FROM yourTable
WHERE (yourTable.ID = beginAsgnmt.ID)
AND (yourTable.[Name] = BeginAsgnmt.[Name])
AND (yourTable.Role = BeginAsgnmt.Role)
AND (yourTable.Allocation = BeginAsgnmt.Allocation)
AND (yourTable.Program = BeginAsgnmt.Program)) as
Assgn_End
FROM yourTable AS BeginAsgnmt
LEFT JOIN yourTable AS Predecessor
ON (BeginAsgnmt.assgn_start = Dateadd("d", 1, Predecessor.assgn_end))
AND (BeginAsgnmt.Allocation = Predecessor.Allocation)
AND (BeginAsgnmt.Role = Predecessor.Role)
AND (BeginAsgnmt.LastName = Predecessor.LastName)
AND (BeginAsgnmt.ID = Predecessor.ID)
AND (BeginAsgnmt.Program = Predecessor.Program)
WHERE Predecessor.L IS NULL

Note: you will not be able to properly display this query in the design grid
because of the line:

ON (BeginAsgnmt.assgn_start = Dateadd("d", 1, Predecessor.assgn_end))

This non-equi join cannot be represented in the query grid.

HTH
Dale