Combining start and end dates to create a date range

[Guest]

Hi,

I have a table with the following fields;

ID
SURNAME
FIRST NAME
TEAM
START
END

The table records clients open to a team so for instance

5 - STEPHENSON - MARK - TEAM A - 1/1/04 - 3/1/04
5 - STEPHENSON - MARK - TEAM B - 3/1/04 - 5/1/04
5 - STEPHENSON - MARK - TEAM A - 7/1/05 -

As you can see from the above, the first 2 rows of data show an unbroken
allocation to someone (team a/b) from 1/1/04 to the 5/1/04, then another run
from 7/1/05 - Null...

What i want to do is make the above 3 rows for example look like

5 - STEPHENSON - MARK - 1/1/04 - 5/1/04
5 - STEPHENSON - MARK - 7/1/05

Any help is appreciated.

Thanks,


Mark.

 

[Michel Walsh]

Hi,


A first query:

SELECT id, surname, firstName, team, start as x FROM myTable
UNION ALL
SELECT id, surname, firstName, team, end FROM myTable


save it, as, say, q1

Then

SELECT id, surname, firstName, team, x
FROM q1
GROUP BY id, surname, firstName, team, x
HAVING COUNT(*)=1


should return the limits (we eliminate those dates that occur twice in their
merge starting/ending data. Save it as q2.


A last query:
------------------------
SELECT b.id, b.surname, b.firstname, b.team, b.x AS starting, MIN(c.x) AS
ending

FROM ( myTable As a INNER JOIN q2 AS b
ON a.id=b.id AND a.surname=b.surname AND a.firstName = b.firstname AND
a.team=b.team AND b.x=a.start )
INNER JOIN q2 AS c
ON a.id=c.id AND a.surname=c.surname AND a.firstName = c.firstname AND
a.team=c.team AND c.x=a.end

WHERE c.x > b.x

GROUP BY b.id, b.surname, b.firstname, b.team, b.x
------------------------


should re-establish the interval limits on a same record ( I haven't
tested )




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks,

I shall give this a try!!!

Has been really bugging me!