SQL to determine Start Date and End Date for consecutive dates

[RCGUA]

I have a database that has various heavy equipment (machines)
sheduled. Each piece of equipment is scheduled for a specific
location and each day that the machine is at that location the date is
in the table.
A table named "tblEquipment", has the machine name, location and
dates. The query below does a great job of finding the first and last
date in the table for each machine, however, the machine may be in one
location for two or three months, then it may not be scheduled for
several months, then it may be scheduled for a different location for
6 months, etc. Can anyone help with the query below so that the
StartDate and EndDate are only for -consecutive- dates? If there is a
break in the dates, they will would get a new StartDate and a new
EndDate.
I understand that entering every single day into the database table is
~not~ the standard way of doing things, but please consider the
question rather than getting stuck on the fact that this is
different. This post was previously posted in comp.databases.ms-
access, and was posted here because there it was suggested to change
the data entry to only enter the start and end dates instead of
attempting to figure how to modify the SQL. Thanks for your help.
====================== query below =============
SELECT tblEquipment.MachineName, Min(tblEquipment.MachineDate) AS
StartDate, Max(tblEquipment.MachineDate) AS EndDate,
tblEquipment.MachineLocation, tblProyectos.ProyectoColor
FROM tblProyectos INNER JOIN tblEquipment ON
tblProyectos.ProyectoNombre = tblEquipment.MachineLocation
GROUP BY tblEquipment.MachineName, tblEquipment.MachineLocation,
tblProyectos.ProyectoColor;

 

[John W. Vinson]

I have a database that has various heavy equipment (machines)
sheduled. Each piece of equipment is scheduled for a specific
location and each day that the machine is at that location the date is
in the table.
A table named "tblEquipment", has the machine name, location and
dates. The query below does a great job of finding the first and last
date in the table for each machine, however, the machine may be in one
location for two or three months, then it may not be scheduled for
several months, then it may be scheduled for a different location for
6 months, etc. Can anyone help with the query below so that the
StartDate and EndDate are only for -consecutive- dates? If there is a
break in the dates, they will would get a new StartDate and a new
EndDate.
I understand that entering every single day into the database table is
~not~ the standard way of doing things, but please consider the
question rather than getting stuck on the fact that this is
different. This post was previously posted in comp.databases.ms-
access, and was posted here because there it was suggested to change
the data entry to only enter the start and end dates instead of
attempting to figure how to modify the SQL. Thanks for your help.
====================== query below =============
SELECT tblEquipment.MachineName, Min(tblEquipment.MachineDate) AS
StartDate, Max(tblEquipment.MachineDate) AS EndDate,
tblEquipment.MachineLocation, tblProyectos.ProyectoColor
FROM tblProyectos INNER JOIN tblEquipment ON
tblProyectos.ProyectoNombre = tblEquipment.MachineLocation
GROUP BY tblEquipment.MachineName, tblEquipment.MachineLocation,
tblProyectos.ProyectoColor;

If the date fields are pure dates with no time component, you can use a couple
of subqueries. Just to get you started, if you define a "start date" as "a
date for which the previous day has no record for this piece of equipment":

(SELECT A.MachineDate FROM tblEquipment AS A
WHERE A.MachineName = tblEquipment.MachineName
AND NOT EXISTS
(SELECT B.MachineDate FROM tblEquipment AS B
WHERE B.MachineName = tblEquipment.MachineName
AND B.MachineDate = DateAdd("d", -1, A.MachineDate))
AS StartDate

as a calculated field.