Rolling Differences

[Guest]

I have a query (qry_Inv_byDate) that has the following records:

Date Status ID Inventory
---------- O -----------
5/1/2001 O 4500
5/9/2001 O 3500
5/10/2001 M Null (or zero if necessary)
5/16/2001 O 3000
5/31/2001 O 2000

I need a field to generate a rolling difference, i.e.,

Diff
-----
Null
1000
3500
-3000
-1000

On another, less urgent, thought, how can I generate the following rolling
total field?

Total
------
4500
8000
8000
11000
13000

I appreciate any help.

 

[Guest]

Assuming you have a consecutive ID on each line use the DLOOKUP function.

 

[Guest]

Does this work in a query? I can code it under a form button, etc., but I
was hoping to take care of it with standard SQL/queries alone.

 

[Guest]

As an alternative to calculating the rolling differences, is there a way I
can make the query/queries find any "M" records in Status ID and then
subtract the Inventory values in the surrounding "O" records (i.e., 3500-3000
= 500)?

 

[John Vinson]

As an alternative to calculating the rolling differences, is there a way I
can make the query/queries find any "M" records in Status ID and then
subtract the Inventory values in the surrounding "O" records (i.e., 3500-3000
= 500)?

A rather snarky and nasty self join. You appear to be treating the
table as a Spreadsheet - there *is* no "next record" or "previous
record" in a Table; a table is an unordered "heap" of data. You'll
need to use Min() and Max() to find the earliest subsequent date or
the latest prior data, respectively, using at least three subqueries.

John W. Vinson[MVP]

 

[Tom Ellison]

Dear Jewel:

Without the SQL of your query so far, and not knowing whether there is a
"grouping" that would reset the total (as opposed to having the running
sum/difference continue to the end of the table) I submit the following
solution as a starting point.

I will use a subquery to find the sum of all preceding rows, and another to
find the value of the preceding row, in order to facilitate what I
understand you want. With a little added complexity, I can modify this to
perform some kind of "grouping" to reset a total, and to give the "NULL"
difference, as you have shown.

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory,
(SELECT Inventory
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] =
(SELECT MAX([Date]
FROM qry_Inv_byDate Q2
WHERE Q2.[Date] < Q.[Date]
AND Q2.[Status ID] = "M"))
AS PreviousInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

There are potential porblems with this. First, Jet (the default database
engine for Access MDBs) does not usually work with a nested subquery (there
is one in what I wrote) which references the outer query (which I did have
to do). There is a work-around for that, but we'll cross that bridge if
that is truly the case for you. It would complain about a reference to
Q.[Date] if that happens. You could try it temporarily without the
"Previous Inventory" value:

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

Second, Date would have to be unique for everything to work well. The
difference value would be ambiguous if there are 2 rows for the previous
value (and the query engine will complain of this even if they have the same
value for Inventory). Don't know if that's the case for you. Also, the
running sum will increase simultaneously for all the rows of the same date.
You see, they're not in any order if they have the same date. We can deal
with that problem if it comes up, but be advised, you need to be thinking
about all the data that may be added to the table in the FUTURE, not just
what's there now. Making a query that works now, but not tomorrow, is
rarely a way to get a promotion!

Third, I mentioned the question of "groups" which may reset your totals.

Next, I'm not sure I understand what you mean for the difference. I didn't
give you a difference, but I did give you the PreviousValue, from which a
difference can be readily calculated. However, I had it skip the "M" rows
in doing this. Not sure you want that, or how you want to deal with NULLs.
Please describe what is going on there!

That's not all, I'm sure, but hopefully it would give us a good start. What
do you think?

Tom Ellison

 

[Guest]

Thank you Tom for your help so far. I have been trying to apply the second
query you wrote (I figure if I can get it working then I'll move on to the
first statement with the previous inventory value) as follows:

SELECT Q.Date, Q.[Status ID], Q.[Inventory], (SELECT Sum(Nz([Inventory],0))
FROM qry_Inv_byDate AS Q1
WHERE (([q1].[Date]<=Q.Date))) AS [AS RunningSum]
FROM qry_Inv_byDate AS Q;

It appears that it has an issue reading Q.Date in the WHERE statement of the
subquery. To test it, I substituted #5/15/2001# for Q1.Date, i.e., my WHERE
statement was WHERE (([q1].[Date]<=#5/15/2001#)). In this case, the query
runs and gives a RunningSum value of 8000 (4500 + 3500) for all records. Do
you know what is going on here?

Thanks Again,

Dear Jewel:

Without the SQL of your query so far, and not knowing whether there is a
"grouping" that would reset the total (as opposed to having the running
sum/difference continue to the end of the table) I submit the following
solution as a starting point.

I will use a subquery to find the sum of all preceding rows, and another to
find the value of the preceding row, in order to facilitate what I
understand you want. With a little added complexity, I can modify this to
perform some kind of "grouping" to reset a total, and to give the "NULL"
difference, as you have shown.

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory,
(SELECT Inventory
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] =
(SELECT MAX([Date]
FROM qry_Inv_byDate Q2
WHERE Q2.[Date] < Q.[Date]
AND Q2.[Status ID] = "M"))
AS PreviousInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

There are potential porblems with this. First, Jet (the default database
engine for Access MDBs) does not usually work with a nested subquery (there
is one in what I wrote) which references the outer query (which I did have
to do). There is a work-around for that, but we'll cross that bridge if
that is truly the case for you. It would complain about a reference to
Q.[Date] if that happens. You could try it temporarily without the
"Previous Inventory" value:

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

Second, Date would have to be unique for everything to work well. The
difference value would be ambiguous if there are 2 rows for the previous
value (and the query engine will complain of this even if they have the same
value for Inventory). Don't know if that's the case for you. Also, the
running sum will increase simultaneously for all the rows of the same date.
You see, they're not in any order if they have the same date. We can deal
with that problem if it comes up, but be advised, you need to be thinking
about all the data that may be added to the table in the FUTURE, not just
what's there now. Making a query that works now, but not tomorrow, is
rarely a way to get a promotion!

Third, I mentioned the question of "groups" which may reset your totals.

Next, I'm not sure I understand what you mean for the difference. I didn't
give you a difference, but I did give you the PreviousValue, from which a
difference can be readily calculated. However, I had it skip the "M" rows
in doing this. Not sure you want that, or how you want to deal with NULLs.
Please describe what is going on there!

That's not all, I'm sure, but hopefully it would give us a good start. What
do you think?

Tom Ellison

I have a query (qry_Inv_byDate) that has the following records:

Date Status ID Inventory
---------- O -----------
5/1/2001 O 4500
5/9/2001 O 3500
5/10/2001 M Null (or zero if necessary)
5/16/2001 O 3000
5/31/2001 O 2000

I need a field to generate a rolling difference, i.e.,

Diff
-----
Null
1000
3500
-3000
-1000

On another, less urgent, thought, how can I generate the following rolling
total field?

Total
------
4500
8000
8000
11000
13000

I appreciate any help.

 

[Michel Walsh]

Hi,



SELECT a.date,
LAST(a.statusID), LAST(a.inventory), LAST(b.inventory),
LAST(a.inventory) - LAST(b.inventory)

FROM (myTable As A LEFT JOIN myTable As b
ON a.date < b.date) LEFT JOIN myTable As c
ON a.date < c.date

GROUP BY a.date, b.date

HAVING b.date IS NULL or b.date=MIN(c.date)




should do the job.


Hoping it may help,
Vanderghast, Access MVP

 

[Tom Ellison]

Dear Jewell:

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

OK, now I see some similarities. But, in the third line, how did we get AS
[AS RunningSum]? I guess that's not the problem, just a rather funny way to
name the column I guess.

What do you get running exactly what I sent? Please try this before making
any alterations, and before Access can mangle the code (which it will do
when you try to save it). What a wonderful feature THAT is. I mean
literally to copy and paste it in. OK? Any error message? What does it
say?

If you use a fixed date as you substituted, the running sum will be a fixed
value. That part makes perfect sense. But it isn't what I designed,
either.

Now, there IS a [Date] column in qry_Inv_byDate, right? I don't see what's
wrong yet. Paren's look good.

Your substitution idea was pretty good! It still doesn't give me a clue,
however. I don't know what else to say at the moment. Make the test I
asked for, please, and get back.

Tom Ellison

Thank you Tom for your help so far. I have been trying to apply the
second
query you wrote (I figure if I can get it working then I'll move on to the
first statement with the previous inventory value) as follows:

SELECT Q.Date, Q.[Status ID], Q.[Inventory], (SELECT
Sum(Nz([Inventory],0))
FROM qry_Inv_byDate AS Q1
WHERE (([q1].[Date]<=Q.Date))) AS [AS RunningSum]
FROM qry_Inv_byDate AS Q;

It appears that it has an issue reading Q.Date in the WHERE statement of
the
subquery. To test it, I substituted #5/15/2001# for Q1.Date, i.e., my
WHERE
statement was WHERE (([q1].[Date]<=#5/15/2001#)). In this case, the query
runs and gives a RunningSum value of 8000 (4500 + 3500) for all records.
Do
you know what is going on here?

Thanks Again,

Dear Jewel:

Without the SQL of your query so far, and not knowing whether there is a
"grouping" that would reset the total (as opposed to having the running
sum/difference continue to the end of the table) I submit the following
solution as a starting point.

I will use a subquery to find the sum of all preceding rows, and another
to
find the value of the preceding row, in order to facilitate what I
understand you want. With a little added complexity, I can modify this
to
perform some kind of "grouping" to reset a total, and to give the "NULL"
difference, as you have shown.

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory,
(SELECT Inventory
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] =
(SELECT MAX([Date]
FROM qry_Inv_byDate Q2
WHERE Q2.[Date] < Q.[Date]
AND Q2.[Status ID] = "M"))
AS PreviousInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

There are potential porblems with this. First, Jet (the default database
engine for Access MDBs) does not usually work with a nested subquery
(there
is one in what I wrote) which references the outer query (which I did
have
to do). There is a work-around for that, but we'll cross that bridge if
that is truly the case for you. It would complain about a reference to
Q.[Date] if that happens. You could try it temporarily without the
"Previous Inventory" value:

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

Second, Date would have to be unique for everything to work well. The
difference value would be ambiguous if there are 2 rows for the previous
value (and the query engine will complain of this even if they have the
same
value for Inventory). Don't know if that's the case for you. Also, the
running sum will increase simultaneously for all the rows of the same
date.
You see, they're not in any order if they have the same date. We can
deal
with that problem if it comes up, but be advised, you need to be thinking
about all the data that may be added to the table in the FUTURE, not just
what's there now. Making a query that works now, but not tomorrow, is
rarely a way to get a promotion!

Third, I mentioned the question of "groups" which may reset your totals.

Next, I'm not sure I understand what you mean for the difference. I
didn't
give you a difference, but I did give you the PreviousValue, from which a
difference can be readily calculated. However, I had it skip the "M"
rows
in doing this. Not sure you want that, or how you want to deal with
NULLs.
Please describe what is going on there!

That's not all, I'm sure, but hopefully it would give us a good start.
What
do you think?

Tom Ellison

I have a query (qry_Inv_byDate) that has the following records:

Date Status ID Inventory
---------- O -----------
5/1/2001 O 4500
5/9/2001 O 3500
5/10/2001 M Null (or zero if necessary)
5/16/2001 O 3000
5/31/2001 O 2000

I need a field to generate a rolling difference, i.e.,

Diff
-----
Null
1000
3500
-3000
-1000

On another, less urgent, thought, how can I generate the following
rolling
total field?

Total
------
4500
8000
8000
11000
13000

I appreciate any help.

 

[Guest]

The query you wrote below doesn't run. It doesn't give a warning, error, or
anything - it just doesn't run. I substituted #5/15/2001# for Q.[Date] in
the where statement again and it runs the same as I described previously
(i.e., it gives 8000 as the runningsomofinventory.

Dear Jewell:

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

OK, now I see some similarities. But, in the third line, how did we get AS
[AS RunningSum]? I guess that's not the problem, just a rather funny way to
name the column I guess.

What do you get running exactly what I sent? Please try this before making
any alterations, and before Access can mangle the code (which it will do
when you try to save it). What a wonderful feature THAT is. I mean
literally to copy and paste it in. OK? Any error message? What does it
say?

If you use a fixed date as you substituted, the running sum will be a fixed
value. That part makes perfect sense. But it isn't what I designed,
either.

Now, there IS a [Date] column in qry_Inv_byDate, right? I don't see what's
wrong yet. Paren's look good.

Your substitution idea was pretty good! It still doesn't give me a clue,
however. I don't know what else to say at the moment. Make the test I
asked for, please, and get back.

Tom Ellison

Thank you Tom for your help so far. I have been trying to apply the
second
query you wrote (I figure if I can get it working then I'll move on to the
first statement with the previous inventory value) as follows:

SELECT Q.Date, Q.[Status ID], Q.[Inventory], (SELECT
Sum(Nz([Inventory],0))
FROM qry_Inv_byDate AS Q1
WHERE (([q1].[Date]<=Q.Date))) AS [AS RunningSum]
FROM qry_Inv_byDate AS Q;

It appears that it has an issue reading Q.Date in the WHERE statement of
the
subquery. To test it, I substituted #5/15/2001# for Q1.Date, i.e., my
WHERE
statement was WHERE (([q1].[Date]<=#5/15/2001#)). In this case, the query
runs and gives a RunningSum value of 8000 (4500 + 3500) for all records.
Do
you know what is going on here?

Thanks Again,

Dear Jewel:

Without the SQL of your query so far, and not knowing whether there is a
"grouping" that would reset the total (as opposed to having the running
sum/difference continue to the end of the table) I submit the following
solution as a starting point.

I will use a subquery to find the sum of all preceding rows, and another
to
find the value of the preceding row, in order to facilitate what I
understand you want. With a little added complexity, I can modify this
to
perform some kind of "grouping" to reset a total, and to give the "NULL"
difference, as you have shown.

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory,
(SELECT Inventory
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] =
(SELECT MAX([Date]
FROM qry_Inv_byDate Q2
WHERE Q2.[Date] < Q.[Date]
AND Q2.[Status ID] = "M"))
AS PreviousInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

There are potential porblems with this. First, Jet (the default database
engine for Access MDBs) does not usually work with a nested subquery
(there
is one in what I wrote) which references the outer query (which I did
have
to do). There is a work-around for that, but we'll cross that bridge if
that is truly the case for you. It would complain about a reference to
Q.[Date] if that happens. You could try it temporarily without the
"Previous Inventory" value:

SELECT [Date], [Status ID], Inventory
(SELECT SUM(Nz(Inventory, 0))
FROM qry_Inv_byDate Q1
WHERE Q1.[Date] <= Q.[Date])
AS RunningSumOfInventory
FROM qry_Inv_byDate Q
ORDER BY [Date]

Second, Date would have to be unique for everything to work well. The
difference value would be ambiguous if there are 2 rows for the previous
value (and the query engine will complain of this even if they have the
same
value for Inventory). Don't know if that's the case for you. Also, the
running sum will increase simultaneously for all the rows of the same
date.
You see, they're not in any order if they have the same date. We can
deal
with that problem if it comes up, but be advised, you need to be thinking
about all the data that may be added to the table in the FUTURE, not just
what's there now. Making a query that works now, but not tomorrow, is
rarely a way to get a promotion!

Third, I mentioned the question of "groups" which may reset your totals.

Next, I'm not sure I understand what you mean for the difference. I
didn't
give you a difference, but I did give you the PreviousValue, from which a
difference can be readily calculated. However, I had it skip the "M"
rows
in doing this. Not sure you want that, or how you want to deal with
NULLs.
Please describe what is going on there!

That's not all, I'm sure, but hopefully it would give us a good start.
What
do you think?

Tom Ellison


I have a query (qry_Inv_byDate) that has the following records:

Date Status ID Inventory
---------- O -----------
5/1/2001 O 4500
5/9/2001 O 3500
5/10/2001 M Null (or zero if necessary)
5/16/2001 O 3000
5/31/2001 O 2000

I need a field to generate a rolling difference, i.e.,

Diff
-----
Null
1000
3500
-3000
-1000

On another, less urgent, thought, how can I generate the following
rolling
total field?

Total
------
4500
8000
8000
11000
13000

I appreciate any help.

 

[Guest]

Thank you and all who helped me with this question for your assistance. I
used your statement as a starting point to get exactly what I needed, which
admittely is slightly different from what I first posted - see below. I post
it here in case anybody has a similar need in the future.

From the data I originally posted, I use the SQL statement below to get the
results shown below.

A.date B.date OpStatusID InvRead OpID NextInvRead Diff
--------- --------- ---------- ------- ---- ----------- ------
5/01/2001 5/09/2001 O 4500 O 3500 1000
5/09/2001 5/10/2001 O 3500 M 0 3500
5/10/2001 5/16/2001 M 0 M 3000 -3000
5/16/2001 5/31/2001 O 3000 O 2000 1000
5/31/2001 Null O 2000 O 2000 0

SELECT A.date, b.date, LAST(A.[OpStatusID]) AS OpStatusID,
iif(isnull(LAST(a.[Inventory])),0,LAST(a.[Inventory])) AS InvRead,
iif(opstatusid<>"O",opstatusid,iif(isnull(LAST(b.[OpStatusID])),"O",LAST(b.[OpStatusID])))
AS OpID,
iif(isnull(b.date),LAST(a.[Inventory]),iif(isnull(LAST(B.[Inventory])),0,LAST(B.[Inventory]))) AS NextInvRead, InvRead-NextInvRead AS Diff
FROM (qry_Inv_byDate AS A LEFT JOIN qry_Inv_byDate AS B ON A.date<B.date)
LEFT JOIN qry_Inv_byDate AS C ON A.date<C.date
GROUP BY A.date, B.date
HAVING B.date IS NULL or B.date=MIN(C.date);

As you can see, I changed your code to move next-values up instead of
previous values down and then I added some convenient naming and some null
handling.

I then used a second query to sum the Diff field where OpID <> "O" giving me
the ultimate goal - how much inventory is lost during non "O" periods.
Again, thank you.