How to find the difference between values of multiple records

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Hello All

i have an app that stores how many hours a machine has run at what dates.
like this:

RunningHoursID HoursAtDate RHDate BlockID
1 10 01/01/01 12
2 20 01/02/01 12
3 30 01/03/01 12
4 40 01/04/01 12
5 543 01/01/01 20
6 590 01/02/01 20

what i need to do is take each record and compare it to the next (or last
depending on how you look at it) then calculate the difference between the
amount of hours the machine has run. the reason is that the 20 in the second
record is the total hours the machine showed on the meter the day the record
was made. the meter doesnt get reset, its like the odometer on a car. i also
need to calculate the amount of time between each record.

the reason is that we are trying to build a forcasting tool to know on
average how many hours will be added to the machine over a certain amount of
time. after a certain amount of hours the machines need a checkup and we want
to be able to schedule that.

i have tried a query and subquery but it crashes access.
 
SELECT RunningHours.RunningHoursID, RunningHours.HoursAtDate,
RunningHours.RHDate, RunningHours.BlockID, hoursatdate-(select
newrunninghours.hoursatdate from runninghours as newrunninghours where
newrunninghours.blockid = runninghours.blockid and
newrunninghours.runninghoursid =runninghours.runninghoursid-1) AS
difference
FROM RunningHours;

that query returns

RunningHoursID HoursAtDate RHDate BlockID difference
1 10 1/01/2001 12
2 20 2/01/2001 12 10
3 30 3/01/2001 12 10
4 40 4/01/2001 12 10
5 543 1/01/2001 20
6 590 2/01/2001 20 47

hope this helps

Regards
Kelvan
 
Stupid google groups thinking i am a spammer

ingore my other post read this one

actually on further pondering that one wont work use

SELECT RunningHours.RunningHoursID, RunningHours.HoursAtDate,
RunningHours.RHDate, RunningHours.BlockID, hoursatdate-(select
newrunninghours.hoursatdate from runninghours as newrunninghours where
newrunninghours.blockid = runninghours.blockid and newrunninghours.RHDate
=runninghours.RHDate-1) AS difference
FROM RunningHours;

it returns the same results just is more stable

AS A NOTE
it will only work if a record is entered in for each day and none are missed.

if you do get missing records please tell me and ill show you a different
method using two queries and is almost full proof.\

Regards
Kelvan
 
the reason is that we are trying to build a forcasting tool
I would recommend some additional math into your calculations. I would take
the lowest value and add 10% to it and use that as a low cutoff for the
averaging. Do the same for the high end. This will remove spikes in the
data.
 
Hello Kelvan

actually there are gaps. that was a quick example, and each date was meant
to be the begining of a month. in the real situation the dates are not
sequencial or have a regular interval, they are all over the map.

i will try your sql, thanks for the help.
 
Hello Karl

would cutting off the upper and lower 10% cause it to be able to predict the
timing and hours in the forcasting of the checkups?

thanks for your help and input. :)
 
WARNING UNTESTED SQL statements follow.

This query should give you the dates to match up

SELECT A.BlockID, A.RHDate, Max(B.RHDate) As PriorDate
FROM YourTable as A LEFT JOIN YourTable As B
On A.BlockId = B.BlockID
AND A.RHDate > B.RHDate

This query (using the above query - saved as qSavedquery or whatever name you
choose) should give you all the necessary fields to do your calculations. The
only problem would be if you have more than one record for a blockid on the
same date.

SELECT *
FROM (qSavedQuery as Q LEFT JOIN YourTable As A
ON q.BlockID = A.BlockID AND Q.RHDate = A.RHDate)
LEFT JOIN YourTable As B
ON q.BlockID = B.BlockID AND Q.PriorDate = B.RHDate

Alternatively, you could use an expression to get the value of the prior
HoursAtDate assuming that the Hours at date field is always increasing.

Assuming that both HoursAtDate and BlockID are number fields.

DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] & " AND
Blockid=" & [BlockID])

SELECT A.BlockID, A.RHDate,
HoursAtDate - DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] &
" AND Blockid=" & [BlockID]) as ElapsedHours
FROM YourTable

One of these solutions may be significantly faster than the other, so if you
can get them both to work you can test them to see which has better speed.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Hello John

i plugged in the sql using my names and stuff.

i am not sure, but i think you intended 2 separate queries, here is what
mine look like:

qryTestingForcastingsql
SELECT A.BlockID, A.RHDate, Max(B.RHDate) AS PriorDate
FROM subtblRunningHours AS A LEFT JOIN subtblRunningHours AS B ON (A.RHDate
B.RHDate) AND (A.BlockID = B.BlockID);
Click to expand...

qryTestingForcastingSql2
SELECT *
FROM (qryTestingForcastingsql AS Q LEFT JOIN subtblRunningHours AS A
ON q.BlockID = A.BlockID AND Q.RHDate = A.RHDate) LEFT JOIN
subtblRunningHours AS B ON q.BlockID = B.BlockID AND Q.PriorDate = B.RHDate;

on both queries i am getting an error:

"you tried to execute a query that does not include the specified expression
'BlockID' as part of an aggregate function"

i am not a sql professional by any means, i look at it and understand the
gist of what it is doing, but there are somethings that mystify me sometimes.
usually when someone gives me sql i will enter it and then flip to the QBE
grid. both queries wont go to the QBE so that leaves me with trying to figure
out the sql.

in the qryTestingForcastingsql where is the BlockID not aggregated? should
we have b.BLockID being selected as well?

any ideas?

John Spencer said:
WARNING UNTESTED SQL statements follow.

This query should give you the dates to match up

SELECT A.BlockID, A.RHDate, Max(B.RHDate) As PriorDate
FROM YourTable as A LEFT JOIN YourTable As B
On A.BlockId = B.BlockID
AND A.RHDate > B.RHDate

This query (using the above query - saved as qSavedquery or whatever name you
choose) should give you all the necessary fields to do your calculations. The
only problem would be if you have more than one record for a blockid on the
same date.

SELECT *
FROM (qSavedQuery as Q LEFT JOIN YourTable As A
ON q.BlockID = A.BlockID AND Q.RHDate = A.RHDate)
LEFT JOIN YourTable As B
ON q.BlockID = B.BlockID AND Q.PriorDate = B.RHDate

Alternatively, you could use an expression to get the value of the prior
HoursAtDate assuming that the Hours at date field is always increasing.

Assuming that both HoursAtDate and BlockID are number fields.

DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] & " AND
Blockid=" & [BlockID])

SELECT A.BlockID, A.RHDate,
HoursAtDate - DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] &
" AND Blockid=" & [BlockID]) as ElapsedHours
FROM YourTable

One of these solutions may be significantly faster than the other, so if you
can get them both to work you can test them to see which has better speed.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hello All

i have an app that stores how many hours a machine has run at what dates.
like this:

RunningHoursID HoursAtDate RHDate BlockID
1 10 01/01/01 12
2 20 01/02/01 12
3 30 01/03/01 12
4 40 01/04/01 12
5 543 01/01/01 20
6 590 01/02/01 20

what i need to do is take each record and compare it to the next (or last
depending on how you look at it) then calculate the difference between the
amount of hours the machine has run. the reason is that the 20 in the second
record is the total hours the machine showed on the meter the day the record
was made. the meter doesnt get reset, its like the odometer on a car. i also
need to calculate the amount of time between each record.

the reason is that we are trying to build a forcasting tool to know on
average how many hours will be added to the machine over a certain amount of
time. after a certain amount of hours the machines need a checkup and we want
to be able to schedule that.

i have tried a query and subquery but it crashes access.
Click to expand...
Click to expand...
 
Hello John

forget my last post. i added group by for each field that gave me the error
on the first query, now it all works.

kinda...

i guess that at this point i need to add any calculations that i want to do?

here is the sql from both queries again:

qryTestingForcastingsql
SELECT A.RunningHoursID, A.BlockID, A.RHDate, Max(B.RHDate) AS PriorDate
FROM subtblRunningHours AS A LEFT JOIN subtblRunningHours AS B ON
(A.RHDate>B.RHDate) AND (A.BlockID=B.BlockID)
GROUP BY A.BlockID, A.RHDate, A.RunningHoursID;

qryTestingForcastingSql2
SELECT *
FROM (qryTestingForcastingsql AS Q LEFT JOIN subtblRunningHours AS A ON
(Q.BlockID = A.BlockID) AND (Q.RHDate = A.RHDate)) LEFT JOIN
subtblRunningHours AS B ON (Q.BlockID = B.BlockID) AND (Q.PriorDate =
B.RHDate);

i added in the RunningHoursID and the group by on the first query. adding
the group by cause the first query to run, and that allowed the second to
work as well.

i guess now i need to add a calculated field to the second query that
figures out the difference between the 2 HoursAtDate from A and B.

so here is my attempt:

SELECT *, (A.HoursAtDate - B.HoursAtDate) AS Difference
FROM (qryTestingForcastingsql AS Q LEFT JOIN subtblRunningHours AS A ON
(Q.RHDate = A.RHDate) AND (Q.BlockID = A.BlockID)) LEFT JOIN
subtblRunningHours AS B ON (Q.PriorDate = B.RHDate) AND (Q.BlockID =
B.BlockID);

now the only concern is that i am getting duplicate data as below:

(I appologize for this mess
Difference Q.RunningHoursID Q.BlockID Q.RHDate PriorDate A.RHDate A.HoursAtDate A.BlockID B.RHDate B.HoursAtDate B.BlockID
1260 2006/08/15
1285 2007/08/21
2805 4 2003/06/16 2003/06/16 250 4
1399 7 2005/06/22 2005/06/22 13904 7
197 1400 7 2005/07/10 2005/06/22 2005/07/10 14101 7 2005/06/22 13904 7
90 1401 7 2005/07/19 2005/07/10 2005/07/19 14191 7 2005/07/10 14101 7
90 1401 7 2005/07/19 2005/07/10 2005/07/19 14191 7 2005/07/10 14101 7
90 1402 7 2005/07/19 2005/07/10 2005/07/19 14191 7 2005/07/10 14101 7
90 1402 7 2005/07/19 2005/07/10 2005/07/19 14191 7 2005/07/10 14101 7
78 1403 7 2005/07/27 2005/07/19 2005/07/27 14269 7 2005/07/19 14191 7
78 1403 7 2005/07/27 2005/07/19 2005/07/27 14269 7 2005/07/19 14191 7
79 1403 7 2005/07/27 2005/07/19 2005/07/27 14270 7 2005/07/19 14191 7
79 1403 7 2005/07/27 2005/07/19 2005/07/27 14270 7 2005/07/19 14191 7
78 1404 7 2005/07/27 2005/07/19 2005/07/27 14269 7 2005/07/19 14191 7
78 1404 7 2005/07/27 2005/07/19 2005/07/27 14269 7 2005/07/19 14191 7
79 1404 7 2005/07/27 2005/07/19 2005/07/27 14270 7 2005/07/19 14191 7
79 1404 7 2005/07/27 2005/07/19 2005/07/27 14270 7 2005/07/19 14191 7
61 1405 7 2005/08/01 2005/07/27 2005/08/01 14330 7 2005/07/27 14269 7
60 1405 7 2005/08/01 2005/07/27 2005/08/01 14330 7 2005/07/27 14270 7
57 1406 7 2005/08/06 2005/08/01 2005/08/06 14387 7 2005/08/01 14330 7
13 1407 7 2005/08/07 2005/08/06 2005/08/07 14400 7 2005/08/06 14387 7

this would cause any averaging of the difference to be skewed. :/

any suggestions?
 
Hello Again

the data in that last post is slightly incomplete.

there are some other factors that are causing the duplicate data.

first here is the latest SQL:

SELECT (A.HoursAtDate-B.HoursAtDate) AS Difference,
datediff("d",PriorDate,Q.RHDate) AS IntervalDays, *
FROM (qryTestingForcastingsql AS Q LEFT JOIN subtblRunningHours AS A ON
(Q.RHDate=A.RHDate) AND (Q.BlockID=A.BlockID)) LEFT JOIN subtblRunningHours
AS B ON (Q.PriorDate=B.RHDate) AND (Q.BlockID=B.BlockID);

this works great, except for the duplicates. the problem is that there are
other fields involved that i never mentioned because they are not needed for
what i am trying to do, however they are the culprits for the duplicates.

the fields are 2 ID fields that tell me what "item" the running hours
information comes from. the 2 IDs are ServiceReportID and IssueID. sometimes
these have the same amount of hours posted on the same day or the next day.
sometimes there is a service report and an issue entered on the same day with
the same data for running hours. i am starting to think that i dont want to
have 2 places the data is coming from.

any suggestions would be appreciated. and thanks for all the help, and
forgive me for the multiposts.

John Spencer said:
WARNING UNTESTED SQL statements follow.

This query should give you the dates to match up

SELECT A.BlockID, A.RHDate, Max(B.RHDate) As PriorDate
FROM YourTable as A LEFT JOIN YourTable As B
On A.BlockId = B.BlockID
AND A.RHDate > B.RHDate

This query (using the above query - saved as qSavedquery or whatever name you
choose) should give you all the necessary fields to do your calculations. The
only problem would be if you have more than one record for a blockid on the
same date.

SELECT *
FROM (qSavedQuery as Q LEFT JOIN YourTable As A
ON q.BlockID = A.BlockID AND Q.RHDate = A.RHDate)
LEFT JOIN YourTable As B
ON q.BlockID = B.BlockID AND Q.PriorDate = B.RHDate

Alternatively, you could use an expression to get the value of the prior
HoursAtDate assuming that the Hours at date field is always increasing.

Assuming that both HoursAtDate and BlockID are number fields.

DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] & " AND
Blockid=" & [BlockID])

SELECT A.BlockID, A.RHDate,
HoursAtDate - DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] &
" AND Blockid=" & [BlockID]) as ElapsedHours
FROM YourTable

One of these solutions may be significantly faster than the other, so if you
can get them both to work you can test them to see which has better speed.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hello All

i have an app that stores how many hours a machine has run at what dates.
like this:

RunningHoursID HoursAtDate RHDate BlockID
1 10 01/01/01 12
2 20 01/02/01 12
3 30 01/03/01 12
4 40 01/04/01 12
5 543 01/01/01 20
6 590 01/02/01 20

what i need to do is take each record and compare it to the next (or last
depending on how you look at it) then calculate the difference between the
amount of hours the machine has run. the reason is that the 20 in the second
record is the total hours the machine showed on the meter the day the record
was made. the meter doesnt get reset, its like the odometer on a car. i also
need to calculate the amount of time between each record.

the reason is that we are trying to build a forcasting tool to know on
average how many hours will be added to the machine over a certain amount of
time. after a certain amount of hours the machines need a checkup and we want
to be able to schedule that.

i have tried a query and subquery but it crashes access.
Click to expand...
Click to expand...
 
I'm sorry, but I can't see a way to help you further.

One thing you might try is to create a query that gets rid of the duplicates
and use that in place of the table.

Perhaps something like
SELECT DISTINCT HoursAtDate
, RHDate
, BlockID
, RunningHoursID
FROM subtblRunningHours

Then in the places you would use subtblRunningHours you would use the above
saved query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hello Again

the data in that last post is slightly incomplete.

there are some other factors that are causing the duplicate data.

first here is the latest SQL:

SELECT (A.HoursAtDate-B.HoursAtDate) AS Difference,
datediff("d",PriorDate,Q.RHDate) AS IntervalDays, *
FROM (qryTestingForcastingsql AS Q LEFT JOIN subtblRunningHours AS A ON
(Q.RHDate=A.RHDate) AND (Q.BlockID=A.BlockID)) LEFT JOIN subtblRunningHours
AS B ON (Q.PriorDate=B.RHDate) AND (Q.BlockID=B.BlockID);

this works great, except for the duplicates. the problem is that there are
other fields involved that i never mentioned because they are not needed for
what i am trying to do, however they are the culprits for the duplicates.

the fields are 2 ID fields that tell me what "item" the running hours
information comes from. the 2 IDs are ServiceReportID and IssueID. sometimes
these have the same amount of hours posted on the same day or the next day.
sometimes there is a service report and an issue entered on the same day with
the same data for running hours. i am starting to think that i dont want to
have 2 places the data is coming from.

any suggestions would be appreciated. and thanks for all the help, and
forgive me for the multiposts.

John Spencer said:
WARNING UNTESTED SQL statements follow.

This query should give you the dates to match up

SELECT A.BlockID, A.RHDate, Max(B.RHDate) As PriorDate
FROM YourTable as A LEFT JOIN YourTable As B
On A.BlockId = B.BlockID
AND A.RHDate > B.RHDate

This query (using the above query - saved as qSavedquery or whatever name you
choose) should give you all the necessary fields to do your calculations. The
only problem would be if you have more than one record for a blockid on the
same date.

SELECT *
FROM (qSavedQuery as Q LEFT JOIN YourTable As A
ON q.BlockID = A.BlockID AND Q.RHDate = A.RHDate)
LEFT JOIN YourTable As B
ON q.BlockID = B.BlockID AND Q.PriorDate = B.RHDate

Alternatively, you could use an expression to get the value of the prior
HoursAtDate assuming that the Hours at date field is always increasing.

Assuming that both HoursAtDate and BlockID are number fields.

DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] & " AND
Blockid=" & [BlockID])

SELECT A.BlockID, A.RHDate,
HoursAtDate - DMax("HoursAtDate","YourTable","HourAtDate <" & [HoursAtDate] &
" AND Blockid=" & [BlockID]) as ElapsedHours
FROM YourTable

One of these solutions may be significantly faster than the other, so if you
can get them both to work you can test them to see which has better speed.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hello All

i have an app that stores how many hours a machine has run at what dates.
like this:

RunningHoursID HoursAtDate RHDate BlockID
1 10 01/01/01 12
2 20 01/02/01 12
3 30 01/03/01 12
4 40 01/04/01 12
5 543 01/01/01 20
6 590 01/02/01 20

what i need to do is take each record and compare it to the next (or last
depending on how you look at it) then calculate the difference between the
amount of hours the machine has run. the reason is that the 20 in the second
record is the total hours the machine showed on the meter the day the record
was made. the meter doesnt get reset, its like the odometer on a car. i also
need to calculate the amount of time between each record.

the reason is that we are trying to build a forcasting tool to know on
average how many hours will be added to the machine over a certain amount of
time. after a certain amount of hours the machines need a checkup and we want
to be able to schedule that.

i have tried a query and subquery but it crashes access.
Click to expand...
Click to expand...
Click to expand...
 
Cutting off the upper and lower 10% will reduce error that odd spike would
cause.
If you did several double shits and did not remove that data it would cause
the average to be high.
If you had a lot of short days either for low production requirements or
downtime that would skew the data also.
 
ok this is what i have done

SELECT subtblRunningHours.RunningHoursID,
subtblRunningHours.HoursAtDate, subtblRunningHours.RHDate,
subtblRunningHours.BlockID, (SELECT Count(runninghoursid) + 1 FROM
subtblrunninghours AS newsubtblrunninghours WHERE
newsubtblrunninghours.rhdate < subtblrunninghours.rhdate and
newsubtblrunninghours.blockid = subtblrunninghours.blockid) AS
recordnum
FROM subtblRunningHours;

name the above qrytestingforcastingsetup

and then

SELECT qryTestingForcastingsetup.RunningHoursID,
qryTestingForcastingsetup.HoursAtDate,
qryTestingForcastingsetup.RHDate, qryTestingForcastingsetup.BlockID,
[hoursatdate]-(select newqrytestingforcastingsetup.hoursatdate from
qrytestingforcastingsetup as newqrytestingforcastingsetup where
newqrytestingforcastingsetup.recordnum =
qrytestingforcastingsetup.recordnum-1 and
newqrytestingforcastingsetup.blockid =
qrytestingforcastingsetup.blockid) AS difference
FROM qryTestingForcastingsetup;

save this as whatever this is the one you run

this one matches the times up and returns

runningHoursID HoursAtDate RHDate BlockID difference
1 10 1/01/2001 12
2 20 1/02/2001 12 10
3 30 1/03/2001 12 10
4 40 1/04/2001 12 10
5 543 1/01/2001 20
6 590 1/02/2001 20 47

regardless of the time difference

you should have to modify the tables i used your table names and field
names so it shoudl be just a matter of plugging them in and saving the
first query as the right name.


hope this helps

Regards
Kelvan
 
Back
Top