Formula to calculate average monthly consumption (AMC) not working

[kihoro]

Hi members,
i have an access tool which calculates Average monthly consumption (AMC) using the following formula > > Adjusted AMC = (SUM of Consumption x 30) divided by ((consumption data period in months x 30) minus SUM of days stocked out). the problem is that i don't seem to get it right... how do i apply this formula.
the one i have done is on monthR from - function generate report - please see attached tool
consumption data period is the number of months input (input before proceeding to the data entry screen, sum of days stocked out is the sum of number of days out of stock input at the data entry screen. consumption is units consumed entered at the data entry screen.


[code]

Period = txtPeriod
QDate = CDate(txtQDate)
lastQMonthDate = QDate 'DateAdd("M", -1, qdate)
bufferMonths = txtBufferStock

sql = "INSERT INTO Buffer ( CategoryID, ProductID, AMC, [Month], StockOnHand, VenClassID, UnitPrice) SELECT MonthlyQ.CategoryID, MonthlyQ.ProductID, Sum(([MonthlyQ].[Qty])*30/((90)-[DayOFS])) AS AMC, " & txtPeriod & " AS Mnth, last(StockOnHand) as SOH, VenClassID, first(UnitPrice) as UnitPrc FROM MonthlyQ where ((((([MonthlyQ].[Qty])*30/((90)-[DayOFS]))) Is Not Null) AND ((MonthlyQ.Date) Between #" & LastDayInMonth(DateAdd("M", counter * -1, lastQMonthDate)) & "# And #" & LastDayInMonth(DateAdd("M", 1 * -1, lastQMonthDate)) & "#)) GROUP BY MonthlyQ.CategoryID, MonthlyQ.ProductID, " & txtPeriod & ", VenClassID ;"
Debug.Print sql
DoCmd.SetWarnings False
DoCmd.RunSQL sql
sql = "INSERT INTO Buffers ( CategoryID, Month, ProductID, AMC, Buffer,StockOnHand, VenClassID, UnitPrice ) SELECT Buffer.CategoryID, " & Period & ", Buffer.ProductID, Sum(Buffer.AMC) AS AMC, Sum(([Buffer].[Amc])*(" & bufferMonths & ")) AS Buffr, last(StockOnHand)as SOH, VenClassID as VenClass, first(UnitPrice) as UnitPrc FROM Buffer GROUP BY Buffer.CategoryID, Buffer.ProductID, VenClassID;"
Debug.Print sql
DoCmd.RunSQL sql

[code]

[ssanfu]

Welcome to the forum.......


Some of the things that I see wrong:
These two lines should be at the top of every code module:
Option Compare Database
Option Explicit
--------------------------------------

You have used look up fields :
see The Evils of Lookup Fields in Tables
http://access.mvps.org/access/lookupfields.htm

Also http://access.mvps.org/access/tencommandments.htm
--------------------------------------

"Date" and "Month" are reserved words in Access and shouldn't be used for object names.
Plus they are not very descriptive. "Date" of what? Birthday??
see http://www.allenbrowne.com/AppIssueBadWord.html
--------------------------------------

In the procedure "GenerateReportData", there are three
DoCmd.SetWarnings False lines, but warnings are never turned back on.

I never use "DoCmd.RunSQL sql";
I only use "Currentdb.Execute sql, dbfailonerror"
--------------------------------------

This doesn't do what you think it is doing.

Dim Period, counter, bufferMonths As Long

The variables "Period" and "counter" are declared as Variant, only "bufferMonths" is delcared as Long.
--------------------------------------

In table "MonthlyQ", the field type of "Month" is LONG.
Shouldn't it be type "Date/Time"?
I think this a major problem because you have:

<snip>..where ((((([MonthlyQ].[Qty])*30/((90)-[DayOFS]))) Is Not Null) AND 
((MonthlyQ.Date) Between #" & LastDayInMonth(DateAdd("M", counter * -1, lastQMonthDate))..<snip>

Here, "Date" is a number, not a date.

Try setting a DEBUG.PRINT sql line, single step through the code and see what the string looks like at this point. (immediate window)
--------------------------------------

This is a personal thing, but you have lines like: Period = txtPeriod

I always use: Period = Me.txtPeriod
This should tell anyone that the value comes from a control on a form, not a variable in code.
To me, it helps understand where the data comes from.

[kihoro]

Hi steve,
thanks alot for the heads up. i truly do appreciate it.
however i am a bit of a novice in access programming. would you please guide me on how to apply this formula to access..in the context of my tool?
AMC = (SUM of Consumption x 30) divided by ((consumption data period in months x 30) minus SUM of days stocked out)
the AMC is calculated in the table buffer and then inserted into table buffers.

[ssanfu]

My apologies.... I don't have enough time to learn how your tool works.

I would start by fixing the tables: removing lookup fields, correct field names (no reserved words), etc.
Why does table "MonthlyQ" have the PK field (ID) as a GUID instead of an Autonumber???


In the formula, there is a variable "Counter", but I can't see where it is ever initialized.
For the formula, I would get the SELECT statement correct first, then get the insert working.

SELECT MonthlyQ.CategoryID, MonthlyQ.ProductID, Sum(([MonthlyQ].[Qty])*30/((90)-[DayOFS])) AS AMC,
 " & txtPeriod & " AS Mnth, last(StockOnHand) as SOH, VenClassID, first(UnitPrice) as UnitPrc 
FROM MonthlyQ 
WHERE ((((([MonthlyQ].[Qty])*30/((90)-[DayOFS]))) Is Not Null) AND ((MonthlyQ.Date) 
Between #" & LastDayInMonth(DateAdd("M", counter * -1, lastQMonthDate)) & "# And #" & LastDayInMonth(DateAdd("M", 1 * -1, lastQMonthDate)) & "#)) 
GROUP BY MonthlyQ.CategoryID, MonthlyQ.ProductID, " & txtPeriod & ", VenClassID ;"

Manually calculate a value for AMC using the numbers that the query would use and compare your value to the query calculation value.

I'll try and modify your function as I would write it to give you an idea of how I format the VBA code... (Not the only way, just my style that I find easy to read)