How to do a Horizontal Min or Max

[doyle60]

How do I get a query to return the highest value or the lowest value
for certain fields? So if I have a query that returns:

KeyField----Value1----Value2-----Value 3
St10000--------10----------20-------------5----
St10020------110-------2110--------3334----
St10444------544---------341-----------11----

how do I get a field to return the 20, 3334, and 544 as the max for
each record? I know how to do this by turning it into a union query
and then reattaching it and all but is there a cheaper way? I could
also write a nested expression but that would get a bit "twiggy" real
fast.

Excel handles it in the Max and Min function. It allows for
Min(Cell1, Cell2, Cell3). But Access complains about the wrong number
of arguments when doign that, even if "And"s are used.

Thanks,

Matt

 

[Klatuu]

You can do it with a Totals query.

 

[Graham Mandeno]

Hi Matt

It looks very much like you are "committing spreadsheet" here. Access is
not a spreadsheet application, it is a relational database system. If you
have multiple fields in a table that need to be summed or otherwise
manipulated arithmetically, it probably means that your database design is
not "normalised".

Without knowing more about your application, it seems that a more correct
design for your table would be:

KeyField ValueField
St10000 10
St10000 20
St10000 5
St10020 110
St10020 2110
St10020 3334
St10444 544
St10444 341
St10444 11

You can then use a query like this:

Select KeyField, Min(ValueField) as MinValue,
Max(ValueField) as MaxValue
from YourTable group by KeyField;

Sometimes it really is necessary to find the Min or Max of an arbitrary
number of values. For this you can write your own function. For example:

Public Function MaxValue(ParamArray Values()) As Variant
Dim i As Integer, vMax As Variant
vMax = Values(LBound(Values))
For i = LBound(Values) + 1 To UBound(Values)
If Values(i) > vMax Then vMax = Values(i)
Next
MaxValue = vMax
End Function

I would recommend that you don't use this in your case before you have
seriously considered redesign.

 

[doyle60]

Thanks Graham. Believe me, I understand proper database design. My
database has been used for 10 years, is split, and has over 50 users.
It currently has 450 tables. Over the years, I've pulled in tables
from three different mainframes. There are over 1,000 queries, many
of which are alive and well and many of which are dead. It churns out
enormous amounts of data, everything from picture catalogs to involved
data manipulation. I've made very few mistakes in table design, if
any. It short, it cranks. And I owe a lot of my success to this very
newsgroup.

The tables are set up as you have it, but those are not the numbers I
want. The query that I want to perform this function in has numbers
that are calculated in a very difficult process from those tables, up
to 30 queries or so. (I'm sure if I knew code better I could have
done it in 6, but it works very fast and so I am happy, and believe
me, it is a complicated matter.)

Thanks for the code. And I seriously and righteously need it, believe
me.

But I don't understand what to put in the query itself? Do I put
this:

High4: MaxValue(Field1, Field2,Field3)

???

Thanks so much,

Matt

 

[Graham Mandeno]

Hi Matt

High4: MaxValue(Field1, Field2,Field3)

Yes, that should do the trick. Have you tried it?

 

[doyle60]

I just tried it. It's a beautiful thing. I also edited it to do the
Minimum as well.

Thanks so much,

Matt

 

[Graham Mandeno]

You're welcome, Matt

Cheers!

 

[doyle60]

Thanks for the code. But I am having trouble with the part before I
use this code. I thought it would be easy but I now see it is just too
difficult to do with IIf statements in the query itself.

What I have is a section in my database where users enter Projections
for certain SKUs (Style, Color, Size, and some other keys) for each
garment, usually panties, by the way.

I gave them a nice report that shows them what production should be
moved up and what production should be moved out. Production is what
is coming in to the ware house, of course, and once in, will be added
to the On Hand, or the inventory.

Though this is all they really need to know what to do, management
would like to see the report filtered to only the bad things, that is,
when we are going to be short or when we are holding too much On
Hand. Both costs too much money, stocking costs gained and
opportunity costs lost.

What I need is a number that simply tells us how many months of
inventory we will be holding at the beginning of each month (given the
entered projections and the known On Hand and production coming in).

Sounds easy, but it is kind of difficult to do in the query itself.

The Projections are in these fields:

M01Adj, M02Adj, M03Adj…M12Adj (with M01Adj always being the present
month)

The production are in these fields:

M01ProdAdj, M02ProdAdj, M03ProdAdj… M12ProdAdj (with M01ProdAdj always
being the present month.

The On hand is in this field: OnHandAdj.

The fields stop at M12.

So, given this data we should be able to figure out the number of
months inventory we have at the beginning of each month.

Let’s take this example, and though I will just type it out, please
understand that all the data is on one record (in a query called
Map_DS380qry_NeedAndActl):

Projection: 2000, 0500, 1000, 1000, 5000, 2000, 1500, 2000, 0500,
1000, 1000, 2000
Production: 0000, 0000, 2000, 0000, 3000, 0000, 1000, 0000, 0000,
0000, 0000, 0000
On Hand: 8000

First off, the Projection for the first month should be adjusted for
the current day of the month it is. So if it is the 15th day of the
month, let’s say, we should adjust it to 2000 * 15/30, or 1000. The
reason for this is that the On Hand has been adjusted for selling in
the month but the projection remains the same.

With this in mind, I want to report these numbers (figured out in my
head) for the number of months inventory we will be carrying if the
projections are correct (for the first day of each month):

Mths/Ivn: 4.9, 3.9, 2.9, 2.75, 1.75, 2.5, 1.5, 1, 0, 0, 0, 0

How did I derive these numbers? The 4.9 derives from the fact that
the 8000 inventory will last for 4 months ( 1000 (the 2000 adjusted
down of the first month) + 500+1000+1000 = 3500). I can’t add the
next month 5000 (1000+500+1000+1000+5000 = 8500) because it would go
over 8000 inventory. But it does go into that month 0.9 of the way.
So I get 4.9. The 2000 production in M03 does not come into play as
it is not carried for the M01 month. M01 has 4.9 months of inventory.

The 3.9 and the 2.9 for M02 and M03 are figured in the same way. The
2.75 for M04 takes into account the 2000 production of M03. You see,
I don’t include the M03 production coming in in the M03 figure because
I am reporting the months inventory for the Beginning of the month and
the production is presumably coming in sometime after the first. It
is true that it may come in on Day 01 or Day 02. But I do not need to
be so picky and it is safer to report lower numbers than higher
numbers.

Do you want to crawl into a hole now? It is a bit difficult to
explain but it is commonsense too. If you wish to tackle it, please
let me know. If you do not want to, believe me, I understand.

Thanks so much,

Matt

 

[Graham Mandeno]

Hi Matt

You sure know how to issue a challenge, don't you? <grin>

First off, you *are* committing spreadsheet! Badly!!
You need to recite "Hail E.F.Codd" 50 times and your database needs a good
wash with Holy Water ;-)

Seriously, you would make life much easier for yourself with a better
normalised design. At the moment, besides the calculation problems that you
have described, you have the following problems:
1. At the start of every month, you need to "shuffle along" every MnnXXX
field into the previous one.
2. This loses any history (maybe you don't want history!)
3. You can never project further than 12 months (maybe you don't want
to!)

I suggest you have a single StockAdjustments table with 6 fields:
SKU *
Month *
ExpectedSales
ExpectedProduction
ActualSales
ActualProduction

* SKU and Month would form a composite primary key

For as many months as you wish in the future, you add a new record for each
SKU with the expected sales and production. At the end of each month you
update each SKU record for that month with the actual sales and production.
You main Products table has, for each SKU, and OnHand quantity and an
OnHandAt field, being the start of the month that the OnHand value was
valid.

The actual stock on hand at the start of any given month up to the present
is then easy to calculate from a single query:

OnHand + Sum(ActualProduction) - Sum(ActualSales)
for the months from OnHandAt to the given month

Your projected stock for a given month in the future is:
[Calculated OnHand for the start of the current month]
+ Sum(ExpectedProduction) - Sum(ExpectedSales)
for the months from the current month to the given month

For the report you describe, you may be able to do it in SQL, but it would
get fairly heavy-duty.

I would prefer to do it in VBA code in the Format event of your report. For
each SKU:
1. Get the OnHand quantity for the start of the current month
2. Open a recordset of StockAdjustments for the next 12 months and load
the ExpectedSales and ExpectedProduction values into two arrays.
3. Calculate the expected OnHand for the start of each of the next 12
months, and story these values in a third array
4. For each of the 12 months, start with the expected OnHand and
subtract the ExpectedSales, counting the months, until the next month's
sales exceeds the remainder. Then divide the remainder by the next month's
sales and add that fraction to the month count. Write the result into the
corresponding month's textbox.

If you want to show only the "bad things" then decide the criteria for a
"bad thing" (e.g. months' stock on hand is <1.5 or >3) and if there are no
figures for the current SKU that are "bad" then you can simply cancel the
Format event for that section.

Have a go at digesting these random thoughts and let me know what you think.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for the code. But I am having trouble with the part before I
use this code. I thought it would be easy but I now see it is just too
difficult to do with IIf statements in the query itself.

What I have is a section in my database where users enter Projections
for certain SKUs (Style, Color, Size, and some other keys) for each
garment, usually panties, by the way.

I gave them a nice report that shows them what production should be
moved up and what production should be moved out. Production is what
is coming in to the ware house, of course, and once in, will be added
to the On Hand, or the inventory.

Though this is all they really need to know what to do, management
would like to see the report filtered to only the bad things, that is,
when we are going to be short or when we are holding too much On
Hand. Both costs too much money, stocking costs gained and
opportunity costs lost.

What I need is a number that simply tells us how many months of
inventory we will be holding at the beginning of each month (given the
entered projections and the known On Hand and production coming in).

Sounds easy, but it is kind of difficult to do in the query itself.

The Projections are in these fields:

M01Adj, M02Adj, M03Adj…M12Adj (with M01Adj always being the present
month)

The production are in these fields:

M01ProdAdj, M02ProdAdj, M03ProdAdj… M12ProdAdj (with M01ProdAdj always
being the present month.

The On hand is in this field: OnHandAdj.

The fields stop at M12.

So, given this data we should be able to figure out the number of
months inventory we have at the beginning of each month.

Let’s take this example, and though I will just type it out, please
understand that all the data is on one record (in a query called
Map_DS380qry_NeedAndActl):

Projection: 2000, 0500, 1000, 1000, 5000, 2000, 1500, 2000, 0500,
1000, 1000, 2000
Production: 0000, 0000, 2000, 0000, 3000, 0000, 1000, 0000, 0000,
0000, 0000, 0000
On Hand: 8000

First off, the Projection for the first month should be adjusted for
the current day of the month it is. So if it is the 15th day of the
month, let’s say, we should adjust it to 2000 * 15/30, or 1000. The
reason for this is that the On Hand has been adjusted for selling in
the month but the projection remains the same.

With this in mind, I want to report these numbers (figured out in my
head) for the number of months inventory we will be carrying if the
projections are correct (for the first day of each month):

Mths/Ivn: 4.9, 3.9, 2.9, 2.75, 1.75, 2.5, 1.5, 1, 0, 0, 0, 0

How did I derive these numbers? The 4.9 derives from the fact that
the 8000 inventory will last for 4 months ( 1000 (the 2000 adjusted
down of the first month) + 500+1000+1000 = 3500). I can’t add the
next month 5000 (1000+500+1000+1000+5000 = 8500) because it would go
over 8000 inventory. But it does go into that month 0.9 of the way.
So I get 4.9. The 2000 production in M03 does not come into play as
it is not carried for the M01 month. M01 has 4.9 months of inventory.

The 3.9 and the 2.9 for M02 and M03 are figured in the same way. The
2.75 for M04 takes into account the 2000 production of M03. You see,
I don’t include the M03 production coming in in the M03 figure because
I am reporting the months inventory for the Beginning of the month and
the production is presumably coming in sometime after the first. It
is true that it may come in on Day 01 or Day 02. But I do not need to
be so picky and it is safer to report lower numbers than higher
numbers.

Do you want to crawl into a hole now? It is a bit difficult to
explain but it is commonsense too. If you wish to tackle it, please
let me know. If you do not want to, believe me, I understand.

Thanks so much,

Matt

 

[doyle60]

Once again, four of my original tables are designed exactly as you
state. One table is designed sideways for very good reason which I
can defend and did defend and won't defend again. That table can be
turned around to mirror the "proper" design as the others. But the
funny thing is for most forms and reports I have to turn the properly
designed ones into horizontal months for best viewing and reporting.

Anyway. Believe me, please. My database tables are set up correctly.

There is no way I should have a table like the one you set up. The
only way I should have such a table is if I build it from my properly
designed tables.

What you are really saying here is this: Matt, to do what you want to
do it would be easier to begin with a query (or table built from
queries) that looks something like this.

And with that, how can I disagree.

Graham, please, please, please, believe me. My database does not need
a "wash with Holy Water."

If I build a table (filled in with queries), will you help me do the
code to answer this question?

Users can enter data in for as many months as they want. But no one
will ever enter more than a year ahead. My naming the present month
M01 and so on was only for this query. Of course the actual fields
are month number plus year. November is 11 but turns into M01 because
it is the present month. I turn it into M01 so I can perform
calculations. And I have done several quite successfully. My current
task, however, is a bit much.

Believe me, believe me, please please believe me, I know how to set up
tables properly and I have done so. My description is of a QUERY
built from properly designed tables.

Thanks,

Matt

 

[Graham Mandeno]

Hi Matt

I'm sorry if you took even the slightest offence from my very
tongue-in-cheek comments. It was certainly not intended!

I didn't realise that your multiple month fields were in a query based on
normalised tables. It must be one hell of a query! Just so we're reading
from the same page, could you please post the SQL for that query and also
the pertinent bits of the design of the relevant tables?

You do want the results of all this in a report, not a query, don't you?

I think the best solution is along the lines of what I've already
described - load the in/out adjustment values into arrays and use code in
the Format event of the report's detail section to fill unbound textboxes.

I can be a bit more specific when you give a bit more detail on the current
design.

 

[doyle60]

Thanks. Since I already have a report that contains all the data we
need to derive these numbers, it is best that we do as you suggest and
do this code in the report. That way we won't have to bother redoing
what I already have done.

Each field has an M prefix standing for month and a number after it
for the actual month. So M01 is November 2008 and M02 is December
2008, and so on. The fields for the Demand (that is, the projections)
have an Adj suffix. And the fields for the Production coming in have
a ProdAdj suffix. So we have the following fields to work with:

M01Adj, M02Adj, M03Adj...to M12Adj (Projections).
M01ProdAdj, M02ProdAdj, M03ProdAdj.... to M12ProdAdj (Production).
OnHandadj (On Hand, or inventory).

These controls have the same names as the fields in the query. If I
need to change the name of each control for the code, I can do so.

If you help me with the code for the first box, I could probably do
the rest. Remember, we are after the number of months inventory we
are carrying at the beginning of each month. But also, remember that
the first month needs an adjustment in the demand based on the
percentage of the month we are currently in. (I cannot carry the
value of the Inventory at the beginning of the month as you
suggested. Such a thing is too dependent on doing things at a certain
time. Best to do it my way and just take the percentage of the
month. And it is no problem working with 30 day average.)

I suppose the code needed is a Case function which I have used but am
a bit fuzzy on.

So once again, if you can possibly do the first one, I could probably
do the rest.

The reason I didn't think of doing this in the report itself is
probably because I wasn't sure how to filter from this point. I have
done some code writing in reports and I have even filtered in a report
(and not in the query) but am not sure if I can combine the two in
this complicated case. Maybe I know how but maybe I don't.

Thanks and I apologize for not seeing that you misinterpreted my query
as an original table.

If you want, I can email you a snapshot of the report which will give
you a clearer picture of what I am trying to do.

Matt

 

[doyle60]

Graham, for the fun of it, I wrote code to figure out the first
month. It isn't elegant and I'm sure you could figure out a better,
shorter way. Don't laugh too hard at the length of it. This is the
code, in the detail on format event of the report:

If OnHandAdj <= M01AdjByDay Then
If M02Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M02Adj - (M01AdjByDay + M02Adj - OnHandAdj)) / M02Adj)
+ 1
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj And OnHandAdj < M01AdjByDay +
M02Adj + M03Adj Then
If M03Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M03Adj - (M01AdjByDay + M02Adj + M03Adj - OnHandAdj)) /
M03Adj) + 2
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj And OnHandAdj <
M01AdjByDay + M02Adj + M03Adj + M04Adj Then
If M04Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M04Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj -
OnHandAdj)) / M04Adj) + 3
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj And OnHandAdj <
M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj Then
If M05Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M05Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj - OnHandAdj)) / M05Adj) + 4
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj And
OnHandAdj < M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj + M06Adj
Then
If M06Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M06Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj + M06Adj - OnHandAdj)) / M06Adj) + 5
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj +
M06Adj And OnHandAdj < M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj
+ M06Adj + M07Adj Then
If M07Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M07Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj + M06Adj + M07Adj - OnHandAdj)) / M07Adj) + 6
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj +
M06Adj + M07Adj And OnHandAdj < M01AdjByDay + M02Adj + M03Adj + M04Adj
+ M05Adj + M06Adj + M07Adj + M08Adj Then
If M08Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M08Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj + M06Adj + M07Adj + M08Adj - OnHandAdj)) / M08Adj) + 7
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj +
M06Adj + M07Adj + M08Adj And OnHandAdj < M01AdjByDay + M02Adj + M03Adj
+ M04Adj + M05Adj + M06Adj + M07Adj + M08Adj + M09Adj Then
If M09Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M09Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj + M06Adj + M07Adj + M08Adj + M09Adj - OnHandAdj)) / M09Adj) + 8
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj +
M06Adj + M07Adj + M08Adj + M09Adj And OnHandAdj < M01AdjByDay + M02Adj
+ M03Adj + M04Adj + M05Adj + M06Adj + M07Adj + M08Adj + M09Adj +
M10Adj Then
If M10Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M10Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj + M06Adj + M07Adj + M08Adj + M09Adj + M10Adj - OnHandAdj)) /
M10Adj) + 9
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj +
M06Adj + M07Adj + M08Adj + M09Adj + M10Adj And OnHandAdj < M01AdjByDay
+ M02Adj + M03Adj + M04Adj + M05Adj + M06Adj + M07Adj + M08Adj +
M09Adj + M10Adj + M11Adj Then
If M11Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M11Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj + M06Adj + M07Adj + M08Adj + M09Adj + M10Adj + M11Adj -
OnHandAdj)) / M11Adj) + 10
End If
End If

If OnHandAdj > M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj +
M06Adj + M07Adj + M08Adj + M09Adj + M10Adj + M11Adj And OnHandAdj <
M01AdjByDay + M02Adj + M03Adj + M04Adj + M05Adj + M06Adj + M07Adj +
M08Adj + M09Adj + M10Adj + M11Adj + M12Adj Then
If M12Adj = 0 Then
M01BOM = 0
Else
M01BOM = ((M12Adj - (M01AdjByDay + M02Adj + M03Adj + M04Adj +
M05Adj + M06Adj + M07Adj + M08Adj + M09Adj + M10Adj + M11Adj + M12Adj
- OnHandAdj)) / M12Adj) + 11
End If
End If

 

[Graham Mandeno]

Hi Matt

Sorry, it's Saturday here and a beautiful sunny day, so I've just been
spending the morning swimming at the beach with my golden retriever

There are some tricks to avoid tedious repetition of very similar blocks of
code. I'm sure you are are familiar with the concept of a counted loop:

Dim iMonth as Integer
For iMonth = 1 to 12
... do some stuff
Next iMonth

The problem is how to translate the value in a variable into the name of a
control - for example, iMonth=6, but we need [M06Adj].

The trick is to construct a string containing the name of the control and
then refer to it as a member of the Controls collection:

"M" & Format(iMonth, "00") & "Adj" gives us "M06Adj"

So we can refer to:
Me.Controls("M" & Format(iMonth, "00") & "Adj")

Or, as Controls is the default collection for a Form or Report object:
Me("M" & Format(iMonth, "00") & "Adj")

Now, referring to these repeatedly can itself be a bit cumbersome, so I
would use two arrays:
Dim aProj(1 to 12) as Long
Dim aProd(1 to 12) as Long
Dim m as Integer
For m = 1 to 12
aProj = Me("M" & Format(m, "00") & "Adj")
aProd = Me("M" & Format(m, "00") & "ProdAdj")
Next m

Now we can simply refer to aProj(i) or aProd(n).

Next, I would use another array to store the calculated "OnHand" stock for
each of the 12 months. If I understand you correctly, these are:
For Month 1 (current day):
OnHandAdj - (aProj(1) * CurrentDayInMonth / DaysInCurrentmonth)
For Month 2:
OnHandAdj - aProj(1) + aProd(1)
For Month m (3 to 12):
aOnHand(m-1) - aProj(m-1) + aProd(m-1)

So we can actually add this to the loop we already have:
Select Case m
Case 1
aOnHand(1) = OnHandAdj _
- (aProj(1) * Day(Date) _
/ Day(DateSerial(Year(Date), Month(Date)+1, 0)) )
Case 2
aOnHand(2) = OnHandAdj - aProj(1) + aProd(1)
Case Else
aOnHand(m) = aOnHand(m-1) - aProj(m-1) + aProd(m-1)
End Select

* Note that DateSerial(Year(Date), Month(Date)+1, 0) gives the date of the
zeroth day in the next month, in other words, the last day of the current
month

With me so far?

OK, now the number of months' worth of stock on hand for a given month (m)
can be calculated by starting with aOnHand(m) and comparing it with
aProj(m). If it is more, then we add one to our month counter and check the
next month. If it is equal or less, then we add to out month counter the
fraction aOnHand(m)/aProj(m).

So we have:
Dim m as Integer
Dim i as Integer
Dim aMOH(1 to 12) as Single ' Months on hand
Dim done as Boolean
For m = 1 to 12
i = 0 ' months on hand
Done = False
Do Until done
If aOnHand(m+i) > aProj(m+i) Then
i = i + 1
If m+i > 12 Then done = True ' we can't go past 12
Else
aMOH(m) = i + aOnHand(m+i) / aProj(m+i)
' *** see below
done = True
End If
Loop
Next m

*** At this point, you could write the calculated value directly into the
destination control, as the job is done, instead of using another array.
Additionally, you could do your filtering for "bad" values and set a boolean
variable to True if you find one:

Dim MOH as Single ' instead of array
Dim HadBad as Boolean
...
MOH = i + aOnHand(m+i) / aProj(m+i)
Me("MOH" & Format(m, "00")) = MOH
If <test MOH for bad value> Then
HadBad = True
End If

.... and at the end of the procedure:
If HadBad Then Cancel = True

I hope that's enough for you to go on with. Let me know how it goes.

 

[doyle60]

Graham, I didn't see my reply pop up after an hour or so so I will
rewrite it here:

I went to New Zealand in about 2003 with my wife. We only did the
South Island. We landed in Christchurch and then toured the island
counter clockwise, visiting such towns as Westport, Queenstown, Te
Anau, Dunedin, and Christchurch again, but with many stops between.
It was one of my favorite trips.

Your code, however, is not one of my favorite trips. I tried to
understand it and do in bits and pieces, but not as a whole, and
therefore, I am having trouble piecing it all together.

1) I get a "Compile Error: Can't assign to array" on the "aProj =".

2) I believe you are not taking the first month reduction (where we
divide it into a fraction of the current day) into account on the
months other than the first. For example, even in month 5, we have to
reduce this month down to reflect the current day we are running the
report. We don't simply take the reduction when figuring month 1
itself.

3) Am I to understand that I am to create text boxes called MOH01 to
MOH12?

4) Could you piece it all together for me without doing the part where
we save the data for filtering. I'm confused all over but most
confused there. It would be best if I understand if first without
this bit in it.

Thanks,

Matt

 

[doyle60]

To make point 2 more clear:

2) I believe you are not taking the first month reduction (where we
divide it into a fraction of the current day) into account on the
months other than the first. For example, even in month 5, we have to
reduce *****month 1***** down to reflect the current day we are
running the report. We don't simply take the reduction when figuring
month 1 itself, ****we take the month 1 reduction in all months.****

Matt

 

[Graham Mandeno]

Hi Matt

If you had to choose between the North and South Islands, you certainly made
the right choice

Let's address your concerns about the code:

1) I get a "Compile Error: Can't assign to array" on the "aProj =".

Well, clearly one shouldn't write code after a day at the beach in the sun
;-)
Of course, you need to say which element in the array you are assigning to:
For m = 1 to 12
aProj(m) = Me("M" & Format(m, "00") & "Adj")
aProd(m) = Me("M" & Format(m, "00") & "ProdAdj")
Next m

2) I believe you are not taking the first month reduction (where we
divide it into a fraction of the current day) into account on the
months other than the first.

OK, I wasn't quite sure of the logic there. Simply getting rid of the
special case for month 2 should do the trick. As the only test is now for
month 1, the Select Case can become an If Then Else statement.

*** PS: I'm still not convinced this is working correctly. How exactly do
you calculate the stock in hand at the beginning of each of the future
months?

3) Am I to understand that I am to create text boxes called MOH01 to
MOH12?

Precisely! These are the textboxes that will display the number of months'
stock on hand in your report

4) Could you piece it all together for me without doing the part where
we save the data for filtering.

Hey, I was leaving the fun bit to you! But if you insist... ;-)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim aProj(1 to 12) as Long
Dim aProd(1 to 12) as Long
Dim aOnHand(1 to 12) as Long
Dim m as Integer
Dim i as Integer
Dim MOH as Single
Dim Done as Boolean
Dim ShowRecord as Boolean
For m = 1 to 12
aProj(m) = Me("M" & Format(m, "00") & "Adj")
aProd(m) = Me("M" & Format(m, "00") & "ProdAdj")
If m = 1 Then
aOnHand(1) = OnHandAdj _
- (aProj(1) * Day(Date) _
/ Day(DateSerial(Year(Date), Month(Date)+1, 0)) )
Else
aOnHand(m) = aOnHand(m-1) - aProj(m-1) + aProd(m-1)
End If
Next m
For m = 1 to 12
i = 0 ' months on hand
Done = False
Do Until Done
If aOnHand(m+i) > aProj(m+i) Then
i = i + 1
If m+i > 12 Then
Done = True ' we can't go past 12
MOH = i
End If
Else
Done = True
MOH = i + aOnHand(m+i) / aProj(m+i)
End If
Loop
Me("MOH" & Format(m, "00")) = MOH
If MOH >= 0 Then ' ***
ShowRecord = True
End If
Next m
If Not ShowRecord Then Cancel = True
End Sub

*** This line is where you do your filtering. Clearly, MOH will always be
non-negative, so this test will show all records. When you are satisfied it
is working, you could change it to something like:
If (MOH < 1.5) or (MOH > 3) Then

You could also change the background shading or the font weight of the
textbox containing the offending value to highlight it.

 

[doyle60]

Thanks Graham. The code performs without error---almost---but the
values are not correct.

1)

If m = 1 Then
aOnHand(1) = OnHandAdj _
- (aProj(1) * Day(Date) _
/ Day(DateSerial(Year(Date), Month(Date)+1, 0)) )
Else
aOnHand(m) = aOnHand(m-1) - aProj(m-1) + aProd(m-1)
End If

The part above is probably why the values are not correct. I tried to
fix it but failed. What we need to adjust for all calculations, for
each month, is the projection for the current month, not the OnHand as
the code above does. You see, when I run the numbers (from a
mainframe) I get the current and true OnHand. But the projection,
done months before, is not adjusted and remains a representation of
the whole month. So if it is the 18th of the month, the projection of
800, which was actually for the whole month, simply cannot be used.
So I need to take a fraction of it and that fraction must be used when
figuring not only month 1, but month 2, month 3, and all other months.

(So, in fact, for the current month, we are actually figuring out the
data for the current day and not the first of the month. It is true
that this will add a value of one to all calculations even on the 27th
day of the month, but the figure is really how many months I have even
if I only have 2 days left in the current month. You see, the way
things go, people here cannot really do anything about the current
month, they can't fix it in time, so it is really the next few months
that are important. If they run the report and use it, they shouldn't
get into trouble. Sorry for the blabbing.)

When I tried to correct the below to reflect the above, I didn't get
the right results. The formula for fixing it should read something
like:

Proj01Adj * (Day/MonthDays)

and since it needs to be taken all the time, I believe the Else
statement can be deleted too.

2)

MOH = i + aOnHand(m+i) / aProj(m+i)

The line above needs to be adjusted for division by zero.

Thanks. Yes, if I go back to New Zealand, and I want to, I'd probably
go back to the south island.

Matt

 

[Graham Mandeno]

Hi Matt

I went back to your example posted a little over 5 days ago and fed the
numbers into my algorithm. They were:

Day: 15
MonthDays: 30
OnHandAdj: 8000
MxxAdj: 2000, 0500, 1000, 1000, 5000, 2000, 1500, 2000, 0500, 1000,
1000, 2000
MxxProdAdj: 0000, 0000, 2000, 0000, 3000, 0000, 1000, 0000, 0000, 0000,
0000, 0000

Your calculations gave:
MOH: 4.9, 3.9, 2.9, 2.75, 1.75, 2.5, 1.5, 1, 0, 0, 0, 0

Mine give:
MOH: 4.9, 3.9, 2.9, 1.9, 0.9, 1.333, 0.333,
0, -4, -2.5, -3.5, -2.25

It's an interesting coincidence that the first three months match precisely
before they diverge.

The approach I've adopted is to calculate the stock on hand now (for M01)
and then at the start of each month thereafter. By adjusting that amount
for each successive month, and counting the months, we can work out the
number of month's stock on hand.

There were three problems with my code - one of logic and two of
understanding.

1. I was allowing the calculation of aOnHand(m) to become negative and, of
course, you can't have negative stock!

2. I understood the OnHandAdj value to be the stock on hand at the *start*
of the current month, not on the current day.

3. I was adjusting the remaining stock up (for Production) as well as down
(for Projected), and it appears from your sample that you don't want to do
this.

Using your figures, I get these values for aOnHand(m):

8000, 7000, 6500, 7500, 6500, 4500, 2500, 2000, 0, 0, 0, 0

These are derived as follows:

8000: what we start with
7000: 8000 less adjusted M01Adj (1000) + M01ProdAdj (0)
6500: 7000 less adjusted M02Adj (500) + M02ProdAdj (0)
7500: 6500 less adjusted M03Adj (1000) + M03ProdAdj (2000)
.... etc

Here is the revised code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim aProj(1 To 12) As Long
Dim aProd(1 To 12) As Long
Dim aOnHand(1 To 12) As Long
Dim m As Integer
Dim i As Integer
Dim MOH As Single
Dim iRemaining As Long
Dim iDaysInMonth As Integer
Dim Done As Boolean
Dim ShowRecord As Boolean
iDaysInMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 0))
For m = 1 To 12
aProj(m) = Me("M" & Format(m, "00") & "Adj")
aProd(m) = Me("M" & Format(m, "00") & "ProdAdj")
If m = 1 Then
aProj(1) = aProj(1) * (iDaysInMonth - Day(Date)) / iDaysInMonth
aOnHand(1) = OnHandAdj
Else
aOnHand(m) = aOnHand(m - 1) - aProj(m - 1) + aProd(m - 1)
If aOnHand(m) < 0 Then aOnHand(m) = 0
End If
Next m
For m = 1 To 12
iRemaining = aOnHand(m)
i = 0 ' month counter
Done = False
Do Until Done
If iRemaining > aProj(m + i) Then
iRemaining = iRemaining - aProj(m + i)
i = i + 1
If m + i > 12 Then
Done = True ' we can't go past 12
MOH = i
End If
Else
Done = True
MOH = i + iRemaining / aProj(m + i)
End If
Loop
Me("MOH" & Format(m, "00")) = MOH
If MOH >= 0 Then
ShowRecord = True
End If
Next m
If Not ShowRecord Then Cancel = True
End Sub

I believe the test for DIV0 at
MOH = i + iRemaining / aProj(m + i)
is unnecessary, because the code only gets there if iRemaining is < aProj(m
+ i) and iRemaining can never get less than 0.

 

[doyle60]

Thanks. I get an overflow error on this line:

MOH = i + iRemaining / aProj(m + i)

It was also the one that had division by zero problems before, right?
If you don't get the error perhaps I get it because I have more data
than you. Some On Hands can be zero. Some actually can be negative,
by the way. It doesn't make any sense but computers allow for it so
they can ship out to the store before the receive it. Don't make me
explain this but it is true.

I'm writing quickly to see if I can catch you before you take to bed.

Matt