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.

 

[Micron]

Very interesting scenario. If done in Access, I think it would have to be done in code. The logic might be something like
create recordset with 3 fields.
get count of apples, oranges, pears.
determine which is least
if it's apples, subtract apples count from apples, oranges and pears
report values - done
if least is not apples,
subtract least from each group
report values
test if new apples count >= least
if true,
subtract least from remaining category and apples
report values
report leftover apples
if false,
subtract apples count from apples and remaining category
done

How would this logic get entered into Access?

Not sure what you mean by this question. Entered into a table? If you go down the suggested path, you'll need a place to put the calculated values, even if temporary, and something that triggers the whole process. BTW, if this is what you consider basic, I'd hate to see what you consider complicated!

 

[dxmandx]

Thanks for the reply. As far as the "How would this logic get entered into Access?" question, I was thinking of best method. Is this something that should be done via formulas in Excel? Is this able to be done with creating tables in access and running a bunch of queries? Is this a VBA thing ... loops or arrays?

I understand the logic behind it (writing it downon paper and manually doing the calculations), but I didn't even know how to start the coding. I was trying to find some linear programming examples since I feel this is somewhat similar. I didn't know if there are some access or excel plug ins that would do these counts and subtractions.

 

[xenou]

the Excel solver addin can be used for linear programming problems. Access doesn't have native support for solving problems of that type so you'd have to write your own coded solution in Access.

 

[shg]

One way, in Excel:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td]

A​

[/td][td]

O​

[/td][td]

P​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[tr][td]

2​

[/td][td]

100​

[/td][td]

75​

[/td][td]

50​

[/td][td][/td][td]A2:C2: Input[/td][/tr]
[tr][td]

3​

[/td][td]

50​

[/td][td]

25​

[/td][td]

0​

[/td][td][/td][td]A3: {=MAX(0, A2 - MIN(IF($A2:$C2 <> 0, $A2:$C2)))}[/td][/tr]
[tr][td]

4​

[/td][td]

25​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]

20​

[/td][td]

100​

[/td][td]

10​

[/td][td][/td][td]A8:C8: Input[/td][/tr]
[tr][td]

9​

[/td][td]

10​

[/td][td]

90​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td]

0​

[/td][td]

80​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[/table]

 

[Micron]

Thanks for the reply. As far as the "How would this logic get entered into Access?" question, I was thinking of best method. Is this something that should be done via formulas in Excel? I can't answer that one. Is this able to be done with creating tables in access and running a bunch of queries? Yes to tables but not a "bunch of queries" if Access. Probably only 1 or 2. Is this a VBA thing ... loops or arrays? Loops and data entry. Once the numbers are calculated, you need somewhere to put them.

I understand the logic behind it (writing it downon paper and manually doing the calculations), but I didn't even know how to start the coding. I was trying to find some linear programming examples since I feel this is somewhat similar. I didn't know if there are some access or excel plug ins that would do these counts and subtractions.

My comments in red. I'd be willing to assist if you're not in a real panic to get it done. Holidays are upon us...

 

[dxmandx]

Thanks again everyone for their inputs. SHG's comment is great, but let me clarify how the output is to be dispayed. Note: I added an extra example to maybe help with the logic.

[TABLE="width: 445"]
<TBODY>[TR]
[TD]Record</SPAN>[/TD]
[TD]Apples</SPAN>[/TD]
[TD]Oranges</SPAN>[/TD]
[TD]Pears</SPAN>[/TD]
[TD] [/TD]
[TD]A/O/P</SPAN>[/TD]
[TD]A/O</SPAN>[/TD]
[TD]A/P</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[TD]75</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD] [/TD]
[TD]50</SPAN>[/TD]
[TD]25</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]25</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]20</SPAN>[/TD]
[TD]100</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD] [/TD]
[TD]10</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]50</SPAN>[/TD]
[TD]20</SPAN>[/TD]
[TD]30</SPAN>[/TD]
[TD] [/TD]
[TD]20</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]10</SPAN>[/TD]
[TD]20</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]



There is not a rush on this. Happy holidays.

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr][tr][td]

1​

[/td][td]

Record​

[/td][td]

Apples​

[/td][td]

Oranges​

[/td][td]

Pears​

[/td][td][/td][td]

A/O/P​

[/td][td]

A/O​

[/td][td]

A/P​

[/td][td]

A​

[/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]

100​

[/td][td]

75​

[/td][td]

50​

[/td][td][/td][td]

50​

[/td][td]

25​

[/td][td]

0​

[/td][td]

25​

[/td][td][/td][td]F2 and down: =MIN(B2:D2)[/td][/tr]
[tr][td]

3​

[/td][td]

2​

[/td][td]

20​

[/td][td]

100​

[/td][td]

10​

[/td][td][/td][td]

10​

[/td][td]

10​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td]G2 and down: =MAX(0, MIN($B2 - SUM($F2:F2), C2 - $F2))[/td][/tr]
[tr][td]

4​

[/td][td]

3​

[/td][td]

50​

[/td][td]

20​

[/td][td]

30​

[/td][td][/td][td]

20​

[/td][td]

0​

[/td][td]

10​

[/td][td]

20​

[/td][td][/td][td]H2 and down: =MAX(0, MIN($B2 - SUM($F2:G2), D2 - $F2))[/td][/tr]
[tr][td]

5​

[/td][td]

3​

[/td][td]

100​

[/td][td]

100​

[/td][td]

20​

[/td][td][/td][td]

20​

[/td][td]

80​

[/td][td]

0​

[/td][td]

0​

[/td][td][/td][td]I2 and down: =B2 - SUM($F2:H2)[/td][/tr]
[/table]

 

[dxmandx]

Hello all,

Sorry for the delay. Things got messy at work, and I could not devote time to this. I tried shg's response, and it works wonders. Thanks everyone for their help. Happy holidays.

 

[shg]

You're welcome.