A
aero-spaces
I have literally been working through this problem in my brain for over a
year, and I still have not been able to come to any conclusion (don't ask why
I'm just now asking for help ).
I have information in three different tables that give me the following
information for each part number of a manufacturing plan:
*current inventory level (updated daily), and recommended min/max levels
*average daily usage (in both a "used x per day" and "used every x days"
format)
*forecasted usage by month (more info below)
First, let me give some filler data to talk about what I am looking for:
tbl_CurrentInventory =
[PartNumber], [qty], [min], [max]
12345A, 20, 6, 24
56789B, 5, 3, 9
tbl_Usage =
[PartNumber], [Used x per day], [used every x days]
12345A, 0.33, 3
56789B, 0.03, 100
tbl_Forecast =
[Part Number], [Date of Last Update], [m1], [m2], [m3],...,[m25] (where m1=
current month, m2=next month, etc.)
12345A, 6/12/08, 3, 7, 7,...,7
56789B, 6/12/08, 0, 0, 1,...,1
OK, now my problem--
I would like to know when our current inventory level will reach the min
(both from the tbl_CurrentInventory table) based mostly on the forecasted
usage table. The reason is this: on many parts, the usage is fairly constant
and higher (in the above example, the one that's used every 3 days). On
others, the usage is lower and more sporadic, i.e., they will use 1 a month
for several months, then go to 0 for several months, etc. The system we have
currently works fine the higher the average usage, because our margin of
error is +/- 3 days--in the above example--which is for the most part,
acceptable. On the sporadic low-usage parts, however, our current queries
will say we'll run out in 200 days (i.e., if we have min+2), but as soon as
they use one part, it will instantly drop to 100 days. Not very helpful when
planning manufacturing schedules.
What I want is a query that will take the current inventory level ([qty]),
then subtract each month of the forecast until it reaches [min], then stop
and return that month. THEN use the average usage ([used every x days])
starting from the 1st of that month to tell me which day of that month we'll
hit [min].
I'm having a VERY hard time conceptualizing how this can work, so more than
anything, I need help in the concept part, and then I can come back here if I
run into technical problems. I'll post our current query below which only
uses the average daily demand (AVGDD below).
Thanks in advance for your ideas!!
SELECT [tbl-CurrentInventory].[Part Number], [tbl-CurrentInventory].[Min],
[tbl-CurrentInventory].[qty], [tbl-CurrentInventory].[Max],
[tbl-CurrentInventory].Status, ([qty]-[Min]) AS InvOverMin, tbl_Usage.AVGDD,
Round(daysUntilZeroFunction([AVGDD]),4) AS UsedEveryXDays,
([InvOverMin]*[UsedEveryXDays]) AS DaysUntilMin,
([tbl-CurrentInventory]![Status]+[DaysUntilMin]) AS DateInvMin
FROM [tbl-CurrentInventory] INNER JOIN tbl_Usage ON
[tbl-CurrentInventory].[Part Number] = tbl_Usage.PartNumber
WHERE ((([tbl-CurrentInventory].[qty])>[Min]));
Here's the function referenced:
Public Function DaysUntilZeroFunction(AVGDD)
If (AVGDD < 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD))
ElseIf (AVGDD > 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD), 3)
Else
DaysUntilZeroFunction = 0
End If
End Function
year, and I still have not been able to come to any conclusion (don't ask why
I'm just now asking for help ).
I have information in three different tables that give me the following
information for each part number of a manufacturing plan:
*current inventory level (updated daily), and recommended min/max levels
*average daily usage (in both a "used x per day" and "used every x days"
format)
*forecasted usage by month (more info below)
First, let me give some filler data to talk about what I am looking for:
tbl_CurrentInventory =
[PartNumber], [qty], [min], [max]
12345A, 20, 6, 24
56789B, 5, 3, 9
tbl_Usage =
[PartNumber], [Used x per day], [used every x days]
12345A, 0.33, 3
56789B, 0.03, 100
tbl_Forecast =
[Part Number], [Date of Last Update], [m1], [m2], [m3],...,[m25] (where m1=
current month, m2=next month, etc.)
12345A, 6/12/08, 3, 7, 7,...,7
56789B, 6/12/08, 0, 0, 1,...,1
OK, now my problem--
I would like to know when our current inventory level will reach the min
(both from the tbl_CurrentInventory table) based mostly on the forecasted
usage table. The reason is this: on many parts, the usage is fairly constant
and higher (in the above example, the one that's used every 3 days). On
others, the usage is lower and more sporadic, i.e., they will use 1 a month
for several months, then go to 0 for several months, etc. The system we have
currently works fine the higher the average usage, because our margin of
error is +/- 3 days--in the above example--which is for the most part,
acceptable. On the sporadic low-usage parts, however, our current queries
will say we'll run out in 200 days (i.e., if we have min+2), but as soon as
they use one part, it will instantly drop to 100 days. Not very helpful when
planning manufacturing schedules.
What I want is a query that will take the current inventory level ([qty]),
then subtract each month of the forecast until it reaches [min], then stop
and return that month. THEN use the average usage ([used every x days])
starting from the 1st of that month to tell me which day of that month we'll
hit [min].
I'm having a VERY hard time conceptualizing how this can work, so more than
anything, I need help in the concept part, and then I can come back here if I
run into technical problems. I'll post our current query below which only
uses the average daily demand (AVGDD below).
Thanks in advance for your ideas!!
SELECT [tbl-CurrentInventory].[Part Number], [tbl-CurrentInventory].[Min],
[tbl-CurrentInventory].[qty], [tbl-CurrentInventory].[Max],
[tbl-CurrentInventory].Status, ([qty]-[Min]) AS InvOverMin, tbl_Usage.AVGDD,
Round(daysUntilZeroFunction([AVGDD]),4) AS UsedEveryXDays,
([InvOverMin]*[UsedEveryXDays]) AS DaysUntilMin,
([tbl-CurrentInventory]![Status]+[DaysUntilMin]) AS DateInvMin
FROM [tbl-CurrentInventory] INNER JOIN tbl_Usage ON
[tbl-CurrentInventory].[Part Number] = tbl_Usage.PartNumber
WHERE ((([tbl-CurrentInventory].[qty])>[Min]));
Here's the function referenced:
Public Function DaysUntilZeroFunction(AVGDD)
If (AVGDD < 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD))
ElseIf (AVGDD > 1) Then
DaysUntilZeroFunction = Round((1 / AVGDD), 3)
Else
DaysUntilZeroFunction = 0
End If
End Function