Passing Quantities with an array

[RedHeadedMonster]

I think I have to create an array, but not sure how to go about it. Here is
my situation. We create a Catalogue for our customer so that the customer
can see what they pay for a particualr quanity of an item.

We receive quantity breaks for SubComponents that we purchase from vendors.
For example if we buy from 1 to 4 we pay $1 each, if we buy from 5-9 we pay $.
95 each if we buy 10-19 we pay $.90 each, etc.

These SubComponents roll up into a MEI (Major End Item) that we build. Then
we come up with quantity breaks at the MEI Level to pass on to a customer.
An MEI could have 1 SubComponent or it could have a 100. Also for each
SubComponent it could require more than 1 per MEI.

ex. To build one MEI-1 we need, SubComp1 @ QTY 2, SubComp2 @ QTY 1, SubComp3
@ QTY 6. So to build Two MEI-1 we'll need SC1 @ QTY 4, SC2 @ Qty 2 & SC3 @
QTY 12.

So depending on the SubComponents Quantity Breaks, the cost from Building 1
to 2 MEI can change dramatically or not much difference at all.

What Im trying to design is a program that will pass MEI Quantities (MEIQty)
from 1 to a Upper Bound Quantity - that can change depending on the upper
bounds of the SubComponents, i.e. if the highest Quanity break for all the
SubComponents is quantity of 500, that will be the upper bound for the MEI.

So, for this example I want to pass in the MEIQty 1 to 500 so that I can see
the MEI cost for each quantity, then look at the costs and "create" a
Quantity break for the MEI everytime there is a 3% drop in cost to the
customer.

Any suggestions would be greatly appreciated.

RHM

 

[CraigH]

Hi,
Without seeing your tables my answer will assume some things, and I used
BOM for the parent - your MEI, but then used MEI in a couple variables.
The tables I assume you have in some form:
tblBOMDetails 'how a MEI is made
BOMDetailID
BOMID - your MEI
ProdcutID
Quantity ' of each Product ID Needed for the MEI

Each Product has a low break Point and at least one with a
Low break Point an No High break point

ProductCostDetails
ProductID
LowerBreakPointNumber 1 5 10 20
HigherBreakPointNumber 4 9 19
Cost $1 .95 .90 .85

Then all you need to do is find which subPart Product ID in the particular
MEI has the Highest number in the Higher Break Point Number. And an SQL
statement can do that

strSQL = SELECT tblBOMDetails.BOMID,
Max(tblProductCostDetails.HighBreakPointNumber) AS MaxOfHighBreakPointNumber
FROM tblBOMDetails INNER JOIN tblProductCostDetails ON
tblBOMDetails.ProductID = tblProductCostDetails.ProductID
GROUP BY tblBOMDetails.BOMID
HAVING (((tblBOMDetails.BOMID)=1));

I'll put this in just in case:
Dim dbs as database
Dim rst as recordset
Dim rstCosts as recordset
Dim intLoop as integer

Set dbs = currentdb
set rst = dbs.Openrecordset(strSQL)
rst.movefirst
For intLoop = 1 to rst!MaxOfHighBreakPointNumber
'Process your calculations
'And this can also be done with a query see the next SQL


strSQL = "SELECT qrySubCost.BOMID, qrySubCost.MEIQty, " & _
"(Sum([totalSubQty]*[Cost]))/[meiQty] AS TotalSubCost " & _
"FROM (SELECT " & intLoop & " AS MEIQty, tblBOMDetails.BOMID,
tblBOMDetails.ProductID, " & _
"tblBOMDetails.Quantity, [MEIQty]*[Quantity] AS TotalSubQty, " & _
"tblProductCostDetails.LowerBreakPointNumber,
tblProductCostDetails.HighBreakPointNumber, " & _

"IIf([TotalSubQty]>=[LowerBreakPointNumber],IIf([TotalSubQty]<=[HighBreakPointNumber],True," & _
"IIf(Nz([HighbreakPointNumber],0)=0,True,False)),False) AS
FindBreak, " & _
"tblProductCostDetails.Cost " & _
"FROM tblBOMDetails INNER JOIN tblProductCostDetails ON " & _
"tblBOMDetails.ProductID=tblProductCostDetails.ProductID " & _
"WHERE (((tblBOMDetails.BOMID)=1))) AS qrySubCost " & _
"WHERE (((qrySubCost.FindBreak) = True)) " & _
"GROUP BY qrySubCost.BOMID, qrySubCost.MEIQty; "

This returns 1 row with the average cost for 1 MEI item with the appropriate
cost for each subItem at the specific level.
Set rstCosts = dbs.OpenRecordset(strSQL)
rstCosts.MoveFirst
' you will have to put in your checking when the price changes by 3% and
checks
Next intloop

I know the last SQL is "confusing well at least to me" I would be hard
pressed to make it without copying a pasting the SQL from the querys when I
used a query withing a query. So a little bit of explanation about it:

I started with the qrySubCost which limits it to BOMID 1, puts in the
MeiQty, and sees which cost I should use (FindBreak) with the Iif statements,
and extend the costs.
That then goes into another query, where I limit it to FindBreak = true,
group it so I can get a total and divide it by the meiQty so I get the
TotalSubCost.

Also: I only changed the Qty being calculated (intLoop) the BOMID/MEI will
have to be changed. As well as more coding

Hope that helps you out.

Craig

 

[RedHeadedMonster via AccessMonster.com]

Thanx a million!
I'll give it a whirl later today. Im the same with the SQL, so maybe Im not
as hopeless as I thought, LOL!
RHM

Hi,
Without seeing your tables my answer will assume some things, and I used
BOM for the parent - your MEI, but then used MEI in a couple variables.
The tables I assume you have in some form:
tblBOMDetails 'how a MEI is made
BOMDetailID
BOMID - your MEI
ProdcutID
Quantity ' of each Product ID Needed for the MEI

Each Product has a low break Point and at least one with a
Low break Point an No High break point

ProductCostDetails
ProductID
LowerBreakPointNumber 1 5 10 20
HigherBreakPointNumber 4 9 19
Cost $1 .95 .90 .85

Then all you need to do is find which subPart Product ID in the particular
MEI has the Highest number in the Higher Break Point Number. And an SQL
statement can do that

strSQL = SELECT tblBOMDetails.BOMID,
Max(tblProductCostDetails.HighBreakPointNumber) AS MaxOfHighBreakPointNumber
FROM tblBOMDetails INNER JOIN tblProductCostDetails ON
tblBOMDetails.ProductID = tblProductCostDetails.ProductID
GROUP BY tblBOMDetails.BOMID
HAVING (((tblBOMDetails.BOMID)=1));

I'll put this in just in case:
Dim dbs as database
Dim rst as recordset
Dim rstCosts as recordset
Dim intLoop as integer

Set dbs = currentdb
set rst = dbs.Openrecordset(strSQL)
rst.movefirst
For intLoop = 1 to rst!MaxOfHighBreakPointNumber
'Process your calculations
'And this can also be done with a query see the next SQL

strSQL = "SELECT qrySubCost.BOMID, qrySubCost.MEIQty, " & _
"(Sum([totalSubQty]*[Cost]))/[meiQty] AS TotalSubCost " & _
"FROM (SELECT " & intLoop & " AS MEIQty, tblBOMDetails.BOMID,
tblBOMDetails.ProductID, " & _
"tblBOMDetails.Quantity, [MEIQty]*[Quantity] AS TotalSubQty, " & _
"tblProductCostDetails.LowerBreakPointNumber,
tblProductCostDetails.HighBreakPointNumber, " & _

"IIf([TotalSubQty]>=[LowerBreakPointNumber],IIf([TotalSubQty]<=[HighBreakPointNumber],True," & _
"IIf(Nz([HighbreakPointNumber],0)=0,True,False)),False) AS
FindBreak, " & _
"tblProductCostDetails.Cost " & _
"FROM tblBOMDetails INNER JOIN tblProductCostDetails ON " & _
"tblBOMDetails.ProductID=tblProductCostDetails.ProductID " & _
"WHERE (((tblBOMDetails.BOMID)=1))) AS qrySubCost " & _
"WHERE (((qrySubCost.FindBreak) = True)) " & _
"GROUP BY qrySubCost.BOMID, qrySubCost.MEIQty; "

This returns 1 row with the average cost for 1 MEI item with the appropriate
cost for each subItem at the specific level.
Set rstCosts = dbs.OpenRecordset(strSQL)
rstCosts.MoveFirst
' you will have to put in your checking when the price changes by 3% and
checks
Next intloop

I know the last SQL is "confusing well at least to me" I would be hard
pressed to make it without copying a pasting the SQL from the querys when I
used a query withing a query. So a little bit of explanation about it:

I started with the qrySubCost which limits it to BOMID 1, puts in the
MeiQty, and sees which cost I should use (FindBreak) with the Iif statements,
and extend the costs.
That then goes into another query, where I limit it to FindBreak = true,
group it so I can get a total and divide it by the meiQty so I get the
TotalSubCost.

Also: I only changed the Qty being calculated (intLoop) the BOMID/MEI will
have to be changed. As well as more coding

Hope that helps you out.

Craig

I think I have to create an array, but not sure how to go about it. Here is
my situation. We create a Catalogue for our customer so that the customer

[quoted text clipped - 29 lines]

RHM