Referencing two separate records from the same column

[Guest]

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.

 

[Guest]

You mention "grouping criteria" twice but don't provide any information on
how you expect to group?

 

[Guest]

Let me re-phase this question and put a little more detail into it. I have a
table which tracks each item as it is manufacured. Our product is consumable
and the delivery time is essential. We actually consider the delivery a part
of the manufacturing process so that our customers can receive the product
quickly. In my table, I track the following fields, ID, product, customer,
delivery truck, load time, product qty. Each of these items are generated in
a single record line in the table. There are no blank fields. These records
are generated with the manufacturing system software so I cannot change it.

What I need is to create a report that I can track the cycle times of the
delivery trucks so that we can measure efficiency and area traffic. I would
like to potentially group this in a report that could designate the grouping
arrangement. I am okay with all of that.

What I don't know how to do is have a running reference to a previous
record. For example, For example, Widget 1 has a ID of 1, (production time
of 10:00
am, customer 1, delivery truck 1). Widget 2 has an ID of 2, (production
time of 10:03 am, customer 2, delivery truck 2). Widget 3 has an ID of 3,
(production time of 10:07 am, delivery truck 1). I need a report that based
on this items falling in sequence of ID can calculate the time differential
from widget 1 and 3 being 0:07 (Minutes), based on the delivery truck
returning to the warehouse and getting another production delivery assignment.

This table is very large we potentially make several hundred deliveries
every day. I hope my question makes more sense.

Thanks

 

[Guest]

I'm still lost (probably on a delivery truck somewhere). It would help me if
you provide a continuous style view of about ten records representing
different trucks or whatever and how you would expect to see these displayed
in your query.

 

[Guest]

ID Date/Time Customer Truck Product Qty
Cycle Time
1 5/3/07 8:00 1 A 12 3
2 5/3/07 8:03 1 A 12
3 0:03
3 5/3/07 8:12 1 A 12
3 0:09
4 5/3/07 8:13 2 B 12 3
5 5/3/07 8:18 2 B 12
3 0:05
6 5/3/07 8:19 1 A 12
3 0:07
7 5/3/07 8:22 2 B 12
3 0:04
8 5/3/07 8:27 1 A 12
3 0:08

 

[Guest]

The first set didn't appear right. This makes it easier to see.

ID Date/Time Customer Truck Cycle Time
1 5/3/07 8:00 1 A
2 5/3/07 8:03 1 A 0:03
3 5/3/07 8:12 1 A 0:09
4 5/3/07 8:13 2 B
5 5/3/07 8:18 2 B 0:05
6 5/3/07 8:19 1 A 0:07
7 5/3/07 8:22 2 B 0:04
8 5/3/07 8:27 1 A 0:08

 

[Guest]

Are these your real field names or are the ones correct in your first post?
Are the dates and times in separate fields? Is there only one customer per
truck? Can you share the table name?

I think Allen's solution should have worked but without knowing your actual
fields and data, it's difficult to troubleshoot.

Duane Hookom
Microsoft Access MVP

 

[Guest]

The first post was a trial table using field names from Allen. These are the
real field names. The table is called "transactions". Allen's solution took
the first value of the query. It would calculate the difference between the
first value and last value. I could use the count function to count the
number of applicable line in the query and divide that into the first time
and last time of the truck which would give me an average. But what I want
to create is the "cycle time" column.

I really appreciate your help. I am a novice to access. I like to think I
know alot, this shows me how stupid I really am. I thought this would be a
fairly easy solution. I would think that many industries would want to query
cycle times based on cycle information.

Thanks again Duane

 

[Guest]

by the way, date and time are in the same field

Are these your real field names or are the ones correct in your first post?
Are the dates and times in separate fields? Is there only one customer per
truck? Can you share the table name?

I think Allen's solution should have worked but without knowing your actual
fields and data, it's difficult to troubleshoot.

Duane Hookom
Microsoft Access MVP

 

[Guest]

Date and Time are in the same field

Are these your real field names or are the ones correct in your first post?
Are the dates and times in separate fields? Is there only one customer per
truck? Can you share the table name?

I think Allen's solution should have worked but without knowing your actual
fields and data, it's difficult to troubleshoot.

Duane Hookom
Microsoft Access MVP

 

[Guest]

Try something like:

SELECT Transactions.*,
[Date/Time]-(Select Max([Date/time]) FROM Transactions Dup WHERE
Transactions.Customer = Dup.Customer AND Dup.DateTime <
Transactions.DateTime) As CycleTime
FROM Transactions;

 

[Guest]

we are really close. here is the code I input. I had to change it slightly.

SELECT Transactions.*, [DateTime]-(Select Max([Datetime]) FROM Transactions
AS Dup WHERE
Transactions.Customer = Dup.Customer AND Dup.DateTime <
Transactions.DateTime) AS CycleTime
FROM Transactions;

It is giving me cycle time by customer. How do I do group it by customer
and truck?
You are GREAT. Thanks for your help.

Try something like:

SELECT Transactions.*,
[Date/Time]-(Select Max([Date/time]) FROM Transactions Dup WHERE
Transactions.Customer = Dup.Customer AND Dup.DateTime <
Transactions.DateTime) As CycleTime
FROM Transactions;


--
Duane Hookom
Microsoft Access MVP

The first post was a trial table using field names from Allen. These are the
real field names. The table is called "transactions". Allen's solution took
the first value of the query. It would calculate the difference between the
first value and last value. I could use the count function to count the
number of applicable line in the query and divide that into the first time
and last time of the truck which would give me an average. But what I want
to create is the "cycle time" column.

I really appreciate your help. I am a novice to access. I like to think I
know alot, this shows me how stupid I really am. I thought this would be a
fairly easy solution. I would think that many industries would want to query
cycle times based on cycle information.

Thanks again Duane

 

[Guest]

I think I finally have it working. Here was my final statement:

SELECT Transactions.ID, Transactions.Customer, Transactions.Truck,
Transactions.DateTime, [DateTime]-(Select Max([Datetime]) FROM Transactions
AS Dup WHERE
Transactions.Customer = Dup.Customer and transactions.truck = dup.truck AND
Dup.DateTime < Transactions.DateTime) AS CycleTime
FROM Transactions;

I think I even understand the statement. Thanks

Try something like:

SELECT Transactions.*,
[Date/Time]-(Select Max([Date/time]) FROM Transactions Dup WHERE
Transactions.Customer = Dup.Customer AND Dup.DateTime <
Transactions.DateTime) As CycleTime
FROM Transactions;


--
Duane Hookom
Microsoft Access MVP

The first post was a trial table using field names from Allen. These are the
real field names. The table is called "transactions". Allen's solution took
the first value of the query. It would calculate the difference between the
first value and last value. I could use the count function to count the
number of applicable line in the query and divide that into the first time
and last time of the truck which would give me an average. But what I want
to create is the "cycle time" column.

I really appreciate your help. I am a novice to access. I like to think I
know alot, this shows me how stupid I really am. I thought this would be a
fairly easy solution. I would think that many industries would want to query
cycle times based on cycle information.

Thanks again Duane

 

[Guest]

That's why I asked several posts back "Is there only one customer per truck?"

Glad you got it correct. In the future, you should provide some actual table
and field names, sample records, and desired output. You might find your
threads will be only a few posts rather than more than a dozen.
--
Duane Hookom
Microsoft Access MVP

I think I finally have it working. Here was my final statement:

SELECT Transactions.ID, Transactions.Customer, Transactions.Truck,
Transactions.DateTime, [DateTime]-(Select Max([Datetime]) FROM Transactions
AS Dup WHERE
Transactions.Customer = Dup.Customer and transactions.truck = dup.truck AND
Dup.DateTime < Transactions.DateTime) AS CycleTime
FROM Transactions;

I think I even understand the statement. Thanks

Try something like:

SELECT Transactions.*,
[Date/Time]-(Select Max([Date/time]) FROM Transactions Dup WHERE
Transactions.Customer = Dup.Customer AND Dup.DateTime <
Transactions.DateTime) As CycleTime
FROM Transactions;


--
Duane Hookom
Microsoft Access MVP

The first post was a trial table using field names from Allen. These are the
real field names. The table is called "transactions". Allen's solution took
the first value of the query. It would calculate the difference between the
first value and last value. I could use the count function to count the
number of applicable line in the query and divide that into the first time
and last time of the truck which would give me an average. But what I want
to create is the "cycle time" column.

I really appreciate your help. I am a novice to access. I like to think I
know alot, this shows me how stupid I really am. I thought this would be a
fairly easy solution. I would think that many industries would want to query
cycle times based on cycle information.

Thanks again Duane

:

Are these your real field names or are the ones correct in your first post?
Are the dates and times in separate fields? Is there only one customer per
truck? Can you share the table name?

I think Allen's solution should have worked but without knowing your actual
fields and data, it's difficult to troubleshoot.

Duane Hookom
Microsoft Access MVP


:

The first set didn't appear right. This makes it easier to see.

ID Date/Time Customer Truck Cycle Time
1 5/3/07 8:00 1 A
2 5/3/07 8:03 1 A 0:03
3 5/3/07 8:12 1 A 0:09
4 5/3/07 8:13 2 B
5 5/3/07 8:18 2 B 0:05
6 5/3/07 8:19 1 A 0:07
7 5/3/07 8:22 2 B 0:04
8 5/3/07 8:27 1 A 0:08

:

ID Date/Time Customer Truck Product Qty
Cycle Time
1 5/3/07 8:00 1 A 12 3
2 5/3/07 8:03 1 A 12
3 0:03
3 5/3/07 8:12 1 A 12
3 0:09
4 5/3/07 8:13 2 B 12 3
5 5/3/07 8:18 2 B 12
3 0:05
6 5/3/07 8:19 1 A 12
3 0:07
7 5/3/07 8:22 2 B 12
3 0:04
8 5/3/07 8:27 1 A 12
3 0:08


:

I'm still lost (probably on a delivery truck somewhere). It would help me if
you provide a continuous style view of about ten records representing
different trucks or whatever and how you would expect to see these displayed
in your query.
--
Duane Hookom
Microsoft Access MVP


:

Let me re-phase this question and put a little more detail into it. I have a
table which tracks each item as it is manufacured. Our product is consumable
and the delivery time is essential. We actually consider the delivery a part
of the manufacturing process so that our customers can receive the product
quickly. In my table, I track the following fields, ID, product, customer,
delivery truck, load time, product qty. Each of these items are generated in
a single record line in the table. There are no blank fields. These records
are generated with the manufacturing system software so I cannot change it.

What I need is to create a report that I can track the cycle times of the
delivery trucks so that we can measure efficiency and area traffic. I would
like to potentially group this in a report that could designate the grouping
arrangement. I am okay with all of that.

What I don't know how to do is have a running reference to a previous
record. For example, For example, Widget 1 has a ID of 1, (production time
of 10:00
am, customer 1, delivery truck 1). Widget 2 has an ID of 2, (production
time of 10:03 am, customer 2, delivery truck 2). Widget 3 has an ID of 3,
(production time of 10:07 am, delivery truck 1). I need a report that based
on this items falling in sequence of ID can calculate the time differential
from widget 1 and 3 being 0:07 (Minutes), based on the delivery truck
returning to the warehouse and getting another production delivery assignment.

This table is very large we potentially make several hundred deliveries
every day. I hope my question makes more sense.

Thanks