Calculating low inventory based on forecast

[aero-spaces]

I am having a hard time conceptualizing this problem--I am trying to create a
query (set of queries/tables/macros, whatever necessary) that will determine
when our inventory will reach a pre-determined minimum value based on a
customer's forecasted usage of given part numbers. Here's the data I have:

Part Number [PartNumber]
Current Inventory at customer's location [CurrentInv]
Our Current Inventory [QoH]
Pre-determined minimum level [Min]
Average Daily Demand (total yearly demand divided by total months) [Avgdd]
Forecasted usage by month (x number of pieces this month, y next month,
etc.) [m1, m2, m3, m4, etc.]

What I would like to do is take the total current inventory
([CurrentInv]+[QoH]), and subtract each month's forecasted usage
(-[m1]-[m2]-[m3], etc.) until it reaches the minimum level ([Min]). Then,
have it return a value of the first day of the resulting month and use the
average daily demand ([Avgdd]) to give me a more accurate date within that
month. (Although that last part would just be a bonus, if I could even just
get it to tell me the first day of the month it will hit [min], that would be
a huge start.)

Any idea how I can begin to structure this?

Thanks in advance!
Ryan

 

[Michel Walsh]

I assume you have TWO tables, with, at least, the following fields:

Stats with fields
Part Number [PartNumber]
Our Current Inventory [QoH]
Pre-determined minimum level [Min]

Forecasts with fields
Part Number [PartNumber]
Full date [ForecastedDate]
Forecasted use, for that given date [Qty]




a possible solution is to get the MAXimum date for which the cumulative sum
is still positive:

SELECT stats.partNumber, MAX( forecastedDate )
FROM stats INNER JOIN forecasts
ON stats.partNumber = forecasts.partNumber .
WHERE currentInventory + qoh >= CumulativeSum
GROUP partNumber


The only part which does not work 'as written' is the CumulativeSum
quantity. You can use a simple DSum or a sub-query doing the same job:


SELECT stats.partNumber, MAX( forecastedDate )
FROM stats INNER JOIN forecasts AS f
ON stats.partNumber = f.partNumber .
WHERE currentInventory + qoh >=
(SELECT SUM(g.qty)
FROM forecasts AS g
WHERE g.partNumber=f.partNumber
AND g.forecastedDate <= f.forecastedDate)
GROUP stats.partNumber




Vanderghast, Access MVP

 

[aero-spaces]

Thanks for such a quick response!

You are correct in your assumption that there are two tables, one with the
inventory information and one with the forecasted information.

The problem, as I see it, is that the forecast table is more of a
spreadsheet format in that the only fields are [Part Number], and then [m1],
[m2], [m3], etc. representing how many pieces the customer plans to use in
the respective months, where m1=the current month, m2=current month +1, etc.
(I.e. there is no "Date" field of any kind.) This information is obtained
from the customer directly, so I can't do anything about that format without
using some kind of query to change it into a different format/layout. So long
story short, what you represented above as [ForecastedDate] is the value I'm
trying to find. Could I somehow do a crosstab query which makes it so instead
of being:

[Part Number],[m1],[m2]...[m24]
12345,4,2...4

it would be something like:

[Part Number],
12345, 1/1/09, 4
12345, 2/1/09, 2
....
12345, 12/1/10, 4

Do I do that first, and then do the queries you suggested??

I will start playing with the queries you provided and see what I come up
with.

Thanks again!

 

[Michel Walsh]

The crosstab like is easy to read, for a human, but not easy to work with,
at least, not for a database engine (since the NAME become the variable to
use, a little bit like x1, x2, x3,... is not as efficient as can be an
array: x(1), x(2), x(3) ... )

So it will be preferable to "normalize" the crosstab:


SELECT partNumber, #1/1/2009# AS forecastedDate, m1 AS qty FROM crosstab
UNION ALL
SELECT partNumber, #2/1/2009#, m2 FROM crosstab
UNION ALL
SELECT partNumber, #3/1/2009#, m3 FROM crosstab
....



and make a table out of this query.



Vanderghast, Access MVP

Thanks for such a quick response!

You are correct in your assumption that there are two tables, one with the
inventory information and one with the forecasted information.

The problem, as I see it, is that the forecast table is more of a
spreadsheet format in that the only fields are [Part Number], and then
[m1],
[m2], [m3], etc. representing how many pieces the customer plans to use in
the respective months, where m1=the current month, m2=current month +1,
etc.
(I.e. there is no "Date" field of any kind.) This information is obtained
from the customer directly, so I can't do anything about that format
without
using some kind of query to change it into a different format/layout. So
long
story short, what you represented above as [ForecastedDate] is the value
I'm
trying to find. Could I somehow do a crosstab query which makes it so
instead
of being:

[Part Number],[m1],[m2]...[m24]
12345,4,2...4

it would be something like:

[Part Number],
12345, 1/1/09, 4
12345, 2/1/09, 2
...
12345, 12/1/10, 4

Do I do that first, and then do the queries you suggested??

I will start playing with the queries you provided and see what I come up
with.

Thanks again!

I assume you have TWO tables, with, at least, the following fields:

Stats with fields
Part Number [PartNumber]
Our Current Inventory [QoH]
Pre-determined minimum level [Min]

Forecasts with fields
Part Number [PartNumber]
Full date [ForecastedDate]
Forecasted use, for that given date [Qty]




a possible solution is to get the MAXimum date for which the cumulative
sum
is still positive:

SELECT stats.partNumber, MAX( forecastedDate )
FROM stats INNER JOIN forecasts
ON stats.partNumber = forecasts.partNumber .
WHERE currentInventory + qoh >= CumulativeSum
GROUP partNumber


The only part which does not work 'as written' is the CumulativeSum
quantity. You can use a simple DSum or a sub-query doing the same job:


SELECT stats.partNumber, MAX( forecastedDate )
FROM stats INNER JOIN forecasts AS f
ON stats.partNumber = f.partNumber .
WHERE currentInventory + qoh >=
(SELECT SUM(g.qty)
FROM forecasts AS g
WHERE g.partNumber=f.partNumber
AND g.forecastedDate <= f.forecastedDate)
GROUP stats.partNumber




Vanderghast, Access MVP

 

[aero-spaces]

I understand (I think)...

I will give this union query a shot and then see if I can apply the first
queries you suggested.

Will post back with my results (if any).

Thanks again!

 

[aero-spaces]

OK... I've taken the first baby steps down the right road, I believe... I've
started a query that looks like this:

SELECT PartNumber, Date() AS forecastedDate, m1 AS qty FROM [Forecast]
UNION ALL
SELECT PartNumber, DateAdd("m",1,(Date())), m2 FROM [Forecast]
UNION ALL
....etc.

I started it this way because if I put in #1/1/09#, etc., I would have to go
in and change the values every month. I want to make it relative rather than
fixed, so I tried the above DateAdd function. It works great for month 1, but
for the following months, it makes it on the same day of the month. How can I
change the above function so that for [m1] it is today's date (Date()), but
for future months it's always (this month+x/1/yyyy)? Is that possible? I
suppose I could live with the function the way it is, but it's not quite as
helpful. (And I definitely don't want to use the fixed date solution either.)

Any more ideas?

Thanks in advance,
Ryan

 

[aero-spaces]

Bah.. answered my own question.. posted here for posterity:

SELECT PartNumber, Format(DateAdd("m",1,(Date())),"mm/1/yyyy"), m2 FROM
[Forecast]
UNION ALL

OK... I've taken the first baby steps down the right road, I believe... I've
started a query that looks like this:

SELECT PartNumber, Date() AS forecastedDate, m1 AS qty FROM [Forecast]
UNION ALL
SELECT PartNumber, DateAdd("m",1,(Date())), m2 FROM [Forecast]
UNION ALL
...etc.

I started it this way because if I put in #1/1/09#, etc., I would have to go
in and change the values every month. I want to make it relative rather than
fixed, so I tried the above DateAdd function. It works great for month 1, but
for the following months, it makes it on the same day of the month. How can I
change the above function so that for [m1] it is today's date (Date()), but
for future months it's always (this month+x/1/yyyy)? Is that possible? I
suppose I could live with the function the way it is, but it's not quite as
helpful. (And I definitely don't want to use the fixed date solution either.)

Any more ideas?

Thanks in advance,
Ryan

The crosstab like is easy to read, for a human, but not easy to work with,
at least, not for a database engine (since the NAME become the variable to
use, a little bit like x1, x2, x3,... is not as efficient as can be an
array: x(1), x(2), x(3) ... )

So it will be preferable to "normalize" the crosstab:


SELECT partNumber, #1/1/2009# AS forecastedDate, m1 AS qty FROM crosstab
UNION ALL
SELECT partNumber, #2/1/2009#, m2 FROM crosstab
UNION ALL
SELECT partNumber, #3/1/2009#, m3 FROM crosstab
....



and make a table out of this query.



Vanderghast, Access MVP

 

[Michel Walsh]

You can also use DateSerial, to get a NUMERICAL date rather than a STRING
holding the representation of a date.

The difference can be seen when you ORDER the data, on this computed
expression (or field). Numerical date will give 1, 2, 3, .., 9, 10, ...
while alphanumerical (string) give 1, 10, 11, ...., 19, 2, 20, 21, ...
since alphanumerical see the order as it would for letters: a, ab, ac,
...., az, b, ba, bb, ...

DateSerial( Year( now()) , Month( now()) + 4, 1 )

will give a date (numerical) equals to the first of the month four month
later than now.


Vanderghast, Access MVP

Bah.. answered my own question.. posted here for posterity:

SELECT PartNumber, Format(DateAdd("m",1,(Date())),"mm/1/yyyy"), m2 FROM
[Forecast]
UNION ALL

OK... I've taken the first baby steps down the right road, I believe...
I've
started a query that looks like this:

SELECT PartNumber, Date() AS forecastedDate, m1 AS qty FROM [Forecast]
UNION ALL
SELECT PartNumber, DateAdd("m",1,(Date())), m2 FROM [Forecast]
UNION ALL
...etc.

I started it this way because if I put in #1/1/09#, etc., I would have to
go
in and change the values every month. I want to make it relative rather
than
fixed, so I tried the above DateAdd function. It works great for month 1,
but
for the following months, it makes it on the same day of the month. How
can I
change the above function so that for [m1] it is today's date (Date()),
but
for future months it's always (this month+x/1/yyyy)? Is that possible? I
suppose I could live with the function the way it is, but it's not quite
as
helpful. (And I definitely don't want to use the fixed date solution
either.)

Any more ideas?

Thanks in advance,
Ryan

The crosstab like is easy to read, for a human, but not easy to work
with,
at least, not for a database engine (since the NAME become the variable
to
use, a little bit like x1, x2, x3,... is not as efficient as can be an
array: x(1), x(2), x(3) ... )

So it will be preferable to "normalize" the crosstab:


SELECT partNumber, #1/1/2009# AS forecastedDate, m1 AS qty FROM
crosstab
UNION ALL
SELECT partNumber, #2/1/2009#, m2 FROM crosstab
UNION ALL
SELECT partNumber, #3/1/2009#, m3 FROM crosstab
....



and make a table out of this query.



Vanderghast, Access MVP

 

[aero-spaces]

Thank you for all your responses. I've got the data now in a more
database-friendly format, but I'm still working on getting the calculations
to work from the original suggested query. As such, I don't want to mark the
question as being answered quite yet.

Would doing a task such as this be easier in Excel or no? If it would be, I
could probably rig up a series of macros/saved exports, etc. to get me what I
need. (I do realize this is not an excel discussion group...)

Thanks again!
Ryan

 

[Michel Walsh]

Excel is single user, as far as I remember, while Access is multiple users,
right out of the box.

Excel requires rigid disposition of data (the view is the data), Access
requires 'normalized' data, and dissociates the view (form) and the data
(table), and may produce different 'views' (forms) without duplicating any
of the data (tables).

Excel poorly enforces data integrity (ie, what if the partNumber does not
exists (typo from the end user, as example)). With Access, that is
simplistic made.



Now, for length of time? If your data is already normalized, Access is
definitively faster. Note that with normalized data, adding a NEW MONTH in
your (normalized) table DOES NOT IMPLY editing ANY part of the code (query).
Can you say the same with the strict Excel disposition of data? With
normalized data, in Access, you just need ONE QUERY (and create a form based
on the query), ONCE for ALL. The query is like the one already supplied. Not
much job there, isn't it? And the form, the wizard can do it for you. What
code would you need, with Excel? What manual transformations your Excel
spreadsheet will be required after one month? and each month ever after?


Sure, your data may not be normalized, now, but once it is normalized, work
with the generated table, rather than with the 'crosstab-spreadsheet'
disposition of your data. Remember one of the big difference between Access
and Excel: Access has dissociate the data (in table) and the view (in a
form) so that the view make the data easy to read, for a HUMAN being, but
the table make the data easy to work with, for a computer (and the
developer).


Vanderghast, Access MVP

Thank you for all your responses. I've got the data now in a more
database-friendly format, but I'm still working on getting the
calculations
to work from the original suggested query. As such, I don't want to mark
the
question as being answered quite yet.

Would doing a task such as this be easier in Excel or no? If it would be,
I
could probably rig up a series of macros/saved exports, etc. to get me
what I
need. (I do realize this is not an excel discussion group...)

Thanks again!
Ryan

I assume you have TWO tables, with, at least, the following fields:

Stats with fields
Part Number [PartNumber]
Our Current Inventory [QoH]
Pre-determined minimum level [Min]

Forecasts with fields
Part Number [PartNumber]
Full date [ForecastedDate]
Forecasted use, for that given date [Qty]




a possible solution is to get the MAXimum date for which the cumulative
sum
is still positive:

SELECT stats.partNumber, MAX( forecastedDate )
FROM stats INNER JOIN forecasts
ON stats.partNumber = forecasts.partNumber .
WHERE currentInventory + qoh >= CumulativeSum
GROUP partNumber


The only part which does not work 'as written' is the CumulativeSum
quantity. You can use a simple DSum or a sub-query doing the same job:


SELECT stats.partNumber, MAX( forecastedDate )
FROM stats INNER JOIN forecasts AS f
ON stats.partNumber = f.partNumber .
WHERE currentInventory + qoh >=
(SELECT SUM(g.qty)
FROM forecasts AS g
WHERE g.partNumber=f.partNumber
AND g.forecastedDate <= f.forecastedDate)
GROUP stats.partNumber




Vanderghast, Access MVP