Consumption during time window

[GillesScouvart]

As part of a safety stock - reorder point analysis, I want to compute the
historical consumption during specified time windows. As a start point, I
have a list of all net consumptions per day (Material|Plant|Date|Consumption).


Till now, I used a not-so-quick-but-dirty method by adding fields in this
table for each length of the time-window (30 days, 60 days, and so on), and
offsetting the date of the consumption by the number of days. Then I use a
Make-table query by
- linking the table to itself (on Material and Plant)
- grouping by Material|Plant|Date
- and summing the consumptions of the copy if their date falls into the time
window.

This takes a long time. I then thought about using Dsum, but I can't get it
working.

SELECT Plant, Material, Date, Quantity, DSum("[T1 Daily consumption].
[Quantity]","[T1 Daily consumption]","[T1 Daily consumption].[Material]='" &
[T1 Daily consumption].[Material] & "' AND [T1 Daily consumption].[Plant]='"
& [T1 Daily consumption].[Plant] & "' AND [T1 Daily consumption].[Posting
date]<#" & DateAdd("d",30,[T1 Daily consumption].[Posting date]) & "# AND [T1
Daily consumption].[Posting date]>=#" & [T1 Daily consumption].[Posting date]
& "#") AS [Cons 30days]
FROM [T1 Daily consumption];

It does work right for some records, and not for others, without apparent
reason.

Any suggestion welcome!

 

[John Spencer]

I would try the following instead of using the DSUM function

SELECT Plant, Material, [Posting Date], Quantity,
(SELECT SUM(Quantity)
FROM [T1 Daily Consumption] as Temp
WHERE Temp.Material = T1.Material
AND Temp.Plant = T1.Plant
AND Temp.[Posting Date] < DateAdd("d",30,T1.[Posting Date])
AND Temp.[Posting Date] >=T1.[Posting Date] ) as [30 Day Total]
FROM [T1 Daily Consumption] as T1

If you are getting some errors in your return values, then you need to look
at the records that are causing the problem and see what is causing that.
In other words, your problem seems to be one caused by the data and not by
the implementation of the code.

By the way, in your posted SQL statement, you have Date in the list of
fields in the Select clause, but in the DSum you refer to Posting Date. I
chose to believe that the latter was the field name you were interested in.

 

[GillesScouvart via AccessMonster.com]

Thank you John!

It seems to work pretty well; I think the problem came from the difference in
date format between my table (European dd/mm/yyyy) and the SQL logic
(American mm/dd/yyyy).
The trouble now is that I'd rather update a table with this value than create
a new one (because of file size constraint), but I get the usual "Operation
must use an updateable query". Do you think of any workaround?

Thanks again for your time!

Gilles.

I would try the following instead of using the DSUM function

SELECT Plant, Material, [Posting Date], Quantity,
(SELECT SUM(Quantity)
FROM [T1 Daily Consumption] as Temp
WHERE Temp.Material = T1.Material
AND Temp.Plant = T1.Plant
AND Temp.[Posting Date] < DateAdd("d",30,T1.[Posting Date])
AND Temp.[Posting Date] >=T1.[Posting Date] ) as [30 Day Total]
FROM [T1 Daily Consumption] as T1

If you are getting some errors in your return values, then you need to look
at the records that are causing the problem and see what is causing that.
In other words, your problem seems to be one caused by the data and not by
the implementation of the code.

By the way, in your posted SQL statement, you have Date in the list of
fields in the Select clause, but in the DSum you refer to Posting Date. I
chose to believe that the latter was the field name you were interested in.

As part of a safety stock - reorder point analysis, I want to compute the
historical consumption during specified time windows. As a start point, I

[quoted text clipped - 33 lines]

Any suggestion welcome!

 

[John Spencer]

Why do you think you need to save the calculated data? Is this method too
slow for your use?
If so, then you can write the query results to a temporary working table and
then use that table to update a field in your existing table. If needed you
can even build the temporary working table in a temporary database
See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example

The danger here is that you must always remember to run the procedure
whenever you need the correct value. If your data is stable (that is you
seldom change existing records or delete existing, but generally only ADD
records) then this approach might be a viable one.

Your other option is to use the DSUM function and figure out how to work
with the dates. See International Dates in Access at:
http://allenbrowne.com/ser-36.html

That will hopefully help you fix the problem with your dates. You could try
forcing the format of the dates in the DSUM to an unambiguous format.

DSum("[T1 Daily consumption].[Quantity]","[T1 Daily consumption]", ... "
AND
[T1 Daily consumption].[Posting date]<" &
Format(DateAdd("d",30,[T1 Daily consumption].[Posting date],
"\#yyyy-mm-dd\#") & " AND ...

Thank you John!

It seems to work pretty well; I think the problem came from the difference
in
date format between my table (European dd/mm/yyyy) and the SQL logic
(American mm/dd/yyyy).
The trouble now is that I'd rather update a table with this value than
create
a new one (because of file size constraint), but I get the usual
"Operation
must use an updateable query". Do you think of any workaround?

Thanks again for your time!

Gilles.

I would try the following instead of using the DSUM function

SELECT Plant, Material, [Posting Date], Quantity,
(SELECT SUM(Quantity)
FROM [T1 Daily Consumption] as Temp
WHERE Temp.Material = T1.Material
AND Temp.Plant = T1.Plant
AND Temp.[Posting Date] < DateAdd("d",30,T1.[Posting Date])
AND Temp.[Posting Date] >=T1.[Posting Date] ) as [30 Day Total]
FROM [T1 Daily Consumption] as T1

If you are getting some errors in your return values, then you need to
look
at the records that are causing the problem and see what is causing that.
In other words, your problem seems to be one caused by the data and not by
the implementation of the code.

By the way, in your posted SQL statement, you have Date in the list of
fields in the Select clause, but in the DSum you refer to Posting Date. I
chose to believe that the latter was the field name you were interested
in.

As part of a safety stock - reorder point analysis, I want to compute
the
historical consumption during specified time windows. As a start point,
I

[quoted text clipped - 33 lines]

Any suggestion welcome!