J
JoeJ
I need to create a report from a table that collects the time that a
manufactured item is ready for delivery. One item I need to add to the
report is the time differential of each manufactured item sent to a specific
location. For example, Widget 1 has a ID of 1, and production time of 10:00
am. Widget 2 has an ID of 2, and a production time of 10:03. Widget 3 has
an ID of 3, and a production time of 10:07. I need a report that based on
this items falling in sequence of ID can calculate the time differential is
0:07 (Minutes).
I posted this question earlier and Allen Browne helped me get a subquery
setup. Here is the SQL text:
SELECT MeterReading.ID, MeterReading.Date, MeterReading.AddressID,
MeterReading.Time, MeterReading.Value, (Select top 1 Dup.Time FROM
MeterReading as Dup Where Dup.AddressID = MeterReading.AddressID AND Dup.Date
<= MeterReading.Date
Order by dup.date DESC, Dup.ID) AS PriorTime, ([Time]-[PriorTime]) AS
CycleTime
FROM MeterReading;
When I first tried this, it appeared to work. However, this takes the
difference between the first record of the day and the current record. I
need it to take the difference of the current record and the previous record
with some grouping criteria. (what type of widget, and the destination
location of that item.) Is there a way to do this in VB or SQL? I basically
need the previous value to move in relationship to the current value based on
the grouping criteria.
manufactured item is ready for delivery. One item I need to add to the
report is the time differential of each manufactured item sent to a specific
location. For example, Widget 1 has a ID of 1, and production time of 10:00
am. Widget 2 has an ID of 2, and a production time of 10:03. Widget 3 has
an ID of 3, and a production time of 10:07. I need a report that based on
this items falling in sequence of ID can calculate the time differential is
0:07 (Minutes).
I posted this question earlier and Allen Browne helped me get a subquery
setup. Here is the SQL text:
SELECT MeterReading.ID, MeterReading.Date, MeterReading.AddressID,
MeterReading.Time, MeterReading.Value, (Select top 1 Dup.Time FROM
MeterReading as Dup Where Dup.AddressID = MeterReading.AddressID AND Dup.Date
<= MeterReading.Date
Order by dup.date DESC, Dup.ID) AS PriorTime, ([Time]-[PriorTime]) AS
CycleTime
FROM MeterReading;
When I first tried this, it appeared to work. However, this takes the
difference between the first record of the day and the current record. I
need it to take the difference of the current record and the previous record
with some grouping criteria. (what type of widget, and the destination
location of that item.) Is there a way to do this in VB or SQL? I basically
need the previous value to move in relationship to the current value based on
the grouping criteria.