Results 1 to 11 of 11
  1. #1
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262

    Design for Material Amortization

    I apologize in advance if this is too complicated, but I just need to know if this can be done either with simple design methods or VBA/SQL.

    I currently have a database set up to record material information based on part numbers and price breaks. Right now, my table design is as follows:

    Click image for larger version. 

Name:	qtysdesign.png 
Views:	19 
Size:	12.3 KB 
ID:	19931

    Now I want to set up something to include amortized parts. Parts can be amortized in many ways, but the most common is by quantity. So if Part1 is $6.92 per unit, and it has a amortize amount of $50.00 over the quantity, which means take the amortize amount ($50.00), and divide it by the quantity, for example 10 pieces, which is $5.00, and add that to the unit price, which comes out to $11.92 per unit for 10 units. The amortize amount is determined by the user because of special circumstances, and so an input box would be useful here. A part in my Qtys table can have infinite amounts of amortizations depending on the vendor and any other special circumstances.

    What I want to do in the form of an algorithm: Have a boolean or something which labels each part amortized or not, have an input box for the amortize amount, and the range of quantities to calculate. Calculate amortized unit prices for every unit quantity between a range, say 11 to 100. If the amortized unit price matches (by two or three decimal places) for any range and group them together with a price break. For example, if the amortized price for 95 pieces is the same for 96, 97, 98, 99, and 100 pieces, I want my price break range to group these together and store them under one record that reads (pretend its a table):

    P/N PriceBreakBegin PriceBreakEnd AmortizedUnitPrice

    Part1 95 100 $4.50




    Now I already have a table set up to look like this. And I'm currently working on getting a 1-infinity relationship between Qtys and AmortizationTable.

    Is this design possible? I can provide more examples if needed.

    Thanks.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    When are you figuring the amoritization? when the item is received into inventory? Or is this just used in figuring costs to sell items to your customers?

    Is there an upper bound to your item amoritization (i.e. it doesn't change after 100 pieces)? does that upper bound vary by part number? (i.e. 100 for part A, 1000 for Part B)

    I'm a little concerned by your use of 'infinity' I am assuming you mean 1 to many relationships and that you do have both upper and lower bounds to your calculations (values at which your amoritization does not affect the price of the item any further).

    Here's an example database:

    Gaker10.zip

    On the form Select a part
    Amoritize Amt is the total amount you want to amoritize
    Amoritize Min and Max are the starting and ending value of item counts you want to use
    Group by Amt is the range of values that are acceptable for a group (i.e. .01$ or .001$)

    Didn't do a whole lot of error checking

  3. #3
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    The latter, rpeare. Figuring costs to sell items.

    The upper bound varies by part number. Really there is no upper bound to buying material of course. But, I would like Access to tell the user when the change in unit cost is negligible, if possible. I'm no procurement agent, I just work with them, so I can't particularly tell when a material's unit cost will plateau, especially if there's variation between material prices, as there often is.

    I tried your database, and I'm not sure I understand your logic. The Amortize Amount is a cost that is amortized across a given quantity. So, the amounts are not quite right in the result. The cost for a price break of 11 pieces should be $13.32 for PartB because the amortize amount is $100 (for example), the min is 1, and the max is 999. $100/11 = $9.09 + $4.23 = $13.32. Maybe I did not explain amortizing over a quantity very clearly. It is a little new to me but I understand how the numbers work. Otherwise this is exactly what I need How can I adjust this this to calculate the amortization as stated above?

  4. #4
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    UPDATE Post #3: Ok so I found your code and I'm going through it and I see:

    Code:
    iPartID = cboPart
    cBaseCost = BaseCost
    cAmorAmt = AmorAmt
    iAmorMin = AmorMin
    iAmorMax = AmorMax
    cGroupAmt = AmorDiff
    
    
    iMax = iAmorMax
    cCostMatch = (cAmorAmt / iAmorMax) + cBaseCost + cGroupAmt
    
    
    db.Execute ("DELETE * FROM tblAmoritize WHERE Item_ID = " & iPartID)
    
    
    Do While iAmorMax >= iAmorMin
        cAmorCost = (cAmorAmt / iAmorMax) + cBaseCost
    'Debug.Print iAmorMax & "  " & Format(cAmorCost, "$#,##0.0000")
        If cAmorCost <= cCostMatch Then
    
    
        Else
            iMin = iAmorMax
            If iAmorMax <> iAmorMin Then
                db.Execute ("INSERT INTO tblAmoritize (Item_ID, Item_LB, Item_UB, Item_Amor_Amt) VALUES (" & iPartID & ", " & iMin & ", " & iMax & ", " & cCostMatch & ")")
                'Debug.Print "              " & iAmorMax & "  " & Format(cAmorCost, "$#,##0.0000") & "  " & Format(cCostMatch, "$#,##0.0000") & "   " & iMin & "   " & iMax
            Else
                GoTo EXITLOOP
            End If
    So your calculations take the amortize amount over the maximum, when I need it to take the amount over the current quantity. Do I need to create another variable to keep track of the current quantity count?

  5. #5
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Or maybe I should track it with the AmorMin variable and reverse the Do While conditions?

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Change the SQL statement to be cAmorCost instead of cCostMatch if you want the highest possible value for the group

  7. #7
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Code:
    cCostMatch = (cAmorAmt / iAmorMax) + cBaseCost + cGroupAmt
    This statement?

    What do you mean by "highest possible value"?

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    no... the SQL statement, where the records are being appended to the amoritization table.

  9. #9
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Got it! Thank you so much!

    Is there any way to incorporate this into a Yes/No field? I have a table of parts and one of the fields is "Amortization?", which is a Yes/No. If it's a yes, I'd like to link your amortize database to it somehow. Like a disable/enable Amortize button feature or something.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't think I'd bother. I don't know what your business is but if you're not amoritizing a cost across a number of units then you just have a unit cost and don't need the amoritization table at all. You could limit what gets added to your amoritization table though by limiting the part selection combo box to only those parts that should be amoritized.

  11. #11
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    That's a good idea. Thank you

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter problem in Material Tracking database
    By bronson_mech in forum Programming
    Replies: 10
    Last Post: 11-23-2013, 12:47 PM
  2. VBA Reference material
    By Paintballlovr in forum Programming
    Replies: 3
    Last Post: 09-07-2013, 08:13 AM
  3. Material Tracking Database
    By bong in forum Programming
    Replies: 2
    Last Post: 08-19-2012, 07:54 PM
  4. Replies: 2
    Last Post: 08-10-2012, 02:11 PM
  5. Material Receiving Form
    By Solanthus in forum Forms
    Replies: 1
    Last Post: 01-26-2012, 08:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums