Opertaion management/Regression problem

[dxmandx]

Hello,

Here is a (hopefully) basic problem. I have 3 different columns of data: Column A = apples, B = oranges, C = pears. The main criteria is that apples must be sold with everything and use the following hierarchy.

First, sell apples + oranges + pears
Then, sell leftover apples + oranges and/or leftover apples + pears
Finally, sell leftover apples
Note: leftover oranges and/or pears alone do nothing.

Example:
100 Apples, 75 Oranges, 50 Pears
Looking at the categories, I would do the apples + orange + pears = 50

Leftovers now look like this: 50 apples, 25 oranges, 0 pears.
Now I would have 25 apples + oranges.

New leftovers: 25 apples, 0 oranges, 0 pears
My final breakdown would be 25 apples.
So when all said and done, I would have split this original data into 3: 50 apples+orange+pears, 25 apples+oranges, 25 apples

Another exmaple to show apples are a must
Example 2: 20 apples, 100 oranges, 10 pears
First: 10 apples+orange+pears
Then: 10 apples+oranges
Although I have 80 oranges left over, I have no apples to link them to, so they do not get counted.

How would this logic get entered into Access? Would I have to use excel? Any help would be appreciated. Thanks.

 

[Marbles]

Hello

I think I’ve managed to make a query to match your example.

Using this example:

Public Function fMin2(ParamArray varValues() As Variant) As Variant

    Dim I As Integer, vMin As Variant
    vMin = varValues(0)
    For I = 1 To UBound(varValues())
        If IsNull(vMin) Then
            vMin = varValues(I)
        ElseIf varValues(I) < vMin Then
            vMin = varValues(I)
        End If
    Next I
    fMin2 = vMin

End Function

Saved as a module, this as the field [SmFruit], in the query, finds the minimum of the three fruit values.

I found it here:

MS Access question: Finding the minimum value in multiple fields - Straight Dope Message Board

After that it takes a few IIFs.

Table fields:

ID,Apple, Orange ,Pear


Query fields:

Apple, Orange, Pear, SmFruit (The Min function), Mapple (Counting what’s left of the apples after the first round), Oleft (Oranges left to go), Pleft (Pears left to go), and ApplesLeft (Apples left over).

The Query SQL:

SELECT Fruit.Apple, Fruit.Orange, Fruit.Pear, fMin2([Apple],[Orange],[Pear]) AS SmFruit, IIf([SmFruit]<[Apple],[Apple]-[SmFruit],0) AS MApple, IIf(IIf([Orange]>[Pear] And [Orange]-[SmFruit]>=[MApple],[MApple],[Orange]-[Pear])<1,0,(IIf([Orange]>[Pear] And [Orange]-[SmFruit]>=[MApple],[MApple],[Orange]-[Pear]))) AS OLeft, IIf(IIf([Pear]>[Orange] And [Pear]-[SmFruit]>=[MApple],[MApple],[Pear]-[Orange])<1,0,((IIf([Pear]>[Orange] And [Pear]-[SmFruit]>=[MApple],[MApple],[Pear]-[Orange])))) AS PLeft, IIf([Apple]<[Orange] Or [Apple]<[Pear],0,IIf([Orange]>=[Pear],[Apple]-[Orange],[Apple]-[Pear])) AS ApplesLeft
FROM Fruit;

Seems to work okay, but would get a lot more complicated if you added another fruit.