Can access calculate the amount consumed per month when data is not linear?

[AL Engineer]

Hello All,

I am using access 2007 to record the date and quantity of a product when it is staged on our production floor. I know when the first quantity is consumed when the next record is entered. The quantity of product staged and the number of days to consume the product can vary. I need to know how much of the product is used per calandar month. Here's an example of the table "Consumptio n" and the three fields:

Gallons - Load - Empty
23 - 01/01/10 - 01/14/10
32 - 01/14/10 - 01/28/10
32 - 01/28/10 - 02/13/10
32 - 02/13/10 - 02/25/10
12 - 02/25/10 - 03/17/10
32 - 03/17/10

[TheSmileyCoder]

Yes, it is possible to calculate that.

The easiest approach however is to calculate it per period, the question is if that is accurate enough for you? In terms of accuracy its actually the most accurate you can get.

If you MUST have it by months you then have to calculate the average for each period, and then adjust according to the number of days in each month. Its possible and not terribly complicated but does involve some coding skills. You just have to make sure your not overdoing the problem.

[AL Engineer]

Let's assume I have fair coding skills. Would you be able to elaborate on your suggestion?

[mshmyob]

Just subscribing.

[mshmyob]

Assuming you have a form with a text box called txtMyDate where you enter a date in the form of mm/dd/yy you may use this code.

Assumptions are that gallons consumed are at the start of the day - ie: a ConsumptionLoad date of 1/28/2010 assumes 4 days of consumption until the end of the month.

Gallons consumed are averaged out per day when spanning multiple months

I created a table called tblConsumption with an autonumber PK. 3 fields are ConsumptionGall ons, ConsumptionLoad , and ConsumptionEmpt y. Data types are number, date, and date.

This code was put in the after update event of the text box control:

Dim dtemyDate As Date

dtemyDate = Me.txtMyDate

' ----- determines the total number of days in a given month ------------------
Dim dteNextMonth, dteEndofMonth As Date
Dim intDaysInMonth As Integer

dteNextMonth = DateAdd("m", 1, dtemyDate)
dteEndofMonth = dteNextMonth - DatePart("d", dteNextMonth)
intDaysInMonth = DatePart("d", dteEndofMonth)
'------- End -------------------

' --------- filter the recordset of consumption to the given month and year ----------
Dim db As dao.Database
Dim rstConsumption As dao.Recordset

strSQL = "SELECT * FROM tblConsumption WHERE month(tblConsumption.ConsumptionLoad)="
strSQL = strSQL & Month(dtemyDate) & "OR month(tblConsumption.ConsumptionEmpty)=" & Month(dtemyDate) & " "
strSQL = strSQL & "ORDER BY tblConsumption.ConsumptionLoad;"
Set db = CurrentDb
Set rstConsumption = db.OpenRecordset(strSQL)
' ----------- End -----------------

' ----------------- calculate the gallons consumed for the given month and year --------------
Dim dblGallonsConsumed, dblAvgConsumptionDay As Double
Dim intDaystoConsume As Integer
Dim intMonth As Integer

Do While Not rstConsumption.EOF
    
    ' this eliminates errors with NULLS if no ConsumptionEmpty date
    If IsNull(rstConsumption!ConsumptionEmpty) Then
        intMonth = Month(dtemyDate)
    Else
        intMonth = Month(rstConsumption!ConsumptionEmpty)
    End If
    
    If intMonth > Month(dtemyDate) Or Month(rstConsumption!ConsumptionLoad) < Month(dtemyDate) Then
        ' number of days to consume - gallons consumed are assumed to start at beginning of day, hence + 1
        ' ie: Jan 28, 2010 is 4 days of consumption till end of month
        intDaystoConsume = DateDiff("d", rstConsumption!ConsumptionLoad, rstConsumption!ConsumptionEmpty) + 1
        ' average consumption per day
        dblAvgConsumptionDay = rstConsumption!ConsumptionGallons / intDaystoConsume
        ' gallons consumed till end of month
        If Month(rstConsumption!ConsumptionEmpty) > Month(dtemyDate) Then
            dblAvgConsumptionDay = dblAvgConsumptionDay * (intDaysInMonth - Day(rstConsumption!ConsumptionLoad) + 1)
        Else
            dblAvgConsumptionDay = dblAvgConsumptionDay * (Day(rstConsumption!ConsumptionEmpty))
        End If
        ' running total consumed for the month/year
        dblGallonsConsumed = dblGallonsConsumed + dblAvgConsumptionDay
    Else
        ' running total consumed for the month/year
        dblGallonsConsumed = dblGallonsConsumed + rstConsumption!ConsumptionGallons
    End If
    
    rstConsumption.MoveNext
Loop
' --------- End ---------------------

'display the results
MsgBox (FormatNumber(dblGallonsConsumed, 2) & " gallons were consumed in " & Format(dtemyDate, "mmmm") & " " & Year(dtemyDate))

If you have any questions please do not hesitate to ask.

cheers,

[mshmyob]

I forgot to change back the SQL filter - the one I supplied was for testing only a specific year. Below is the change to the SQL filter that includes any year.

I also want to add one more assumption and that is that the text box control on the form expects any month or year but expects to use the 1st day of the month. ie:1/1/10 would give the gallons consumed in Jan of 2010

Sorry for the confusion.

Dim dtemyDate As Date

' date must be entered as the first day of any month and year - ie: 1/1/10 for Jan 1 2010
dtemyDate = Me.txtMyDate

' ----- determines the total number of days in a given month ------------------
Dim lngNextMonth, lngEndofMonth As Date
Dim intDaysInMonth As Integer

dteNextMonth = DateAdd("m", 1, dtemyDate)
dteEndofMonth = dteNextMonth - DatePart("d", dteNextMonth)
intDaysInMonth = DatePart("d", dteEndofMonth)
'------- End -------------------

' --------- filter the recordset of consumption to the given month and year ----------
Dim db As dao.Database
Dim rstConsumption As dao.Recordset

strSQL = "SELECT * FROM tblConsumption WHERE tblConsumption.ConsumptionLoad <= #" & dteEndofMonth
strSQL = strSQL & "# AND (tblConsumption.ConsumptionEmpty >= #" & dtemyDate
strSQL = strSQL & "# OR isnull(tblConsumption.ConsumptionEmpty))"
strSQL = strSQL & " ORDER BY tblConsumption.ConsumptionLoad;"

Set db = CurrentDb
Set rstConsumption = db.OpenRecordset(strSQL)
' ----------- End -----------------

' ----------------- calculate the gallons consumed for the given month and year --------------
Dim dblGallonsConsumed, dblAvgConsumptionDay As Double
Dim intDaystoConsume As Integer
Dim intMonth As Integer

Do While Not rstConsumption.EOF
    
    ' this eliminates errors with NULLS if no ConsumptionEmpty date
    If IsNull(rstConsumption!ConsumptionEmpty) Then
        intMonth = Month(dtemyDate)
    Else
        intMonth = Month(rstConsumption!ConsumptionEmpty)
    End If
    
    If intMonth > Month(dtemyDate) Or Month(rstConsumption!ConsumptionLoad) < Month(dtemyDate) Then
        ' number of days to consume - gallons consumed are assumed to start at beginning of day, hence + 1
        ' ie: Jan 28, 2010 is 4 days of consumption till end of month
        intDaystoConsume = DateDiff("d", rstConsumption!ConsumptionLoad, rstConsumption!ConsumptionEmpty) + 1
        ' average consumption per day
        dblAvgConsumptionDay = rstConsumption!ConsumptionGallons / intDaystoConsume
        ' gallons consumed till end of month
        If Month(rstConsumption!ConsumptionEmpty) > Month(dtemyDate) Then
            dblAvgConsumptionDay = dblAvgConsumptionDay * (intDaysInMonth - Day(rstConsumption!ConsumptionLoad) + 1)
        Else
            dblAvgConsumptionDay = dblAvgConsumptionDay * (Day(rstConsumption!ConsumptionEmpty))
        End If
        ' running total consumed for the month/year
        dblGallonsConsumed = dblGallonsConsumed + dblAvgConsumptionDay
    Else
        ' running total consumed for the month/year
        dblGallonsConsumed = dblGallonsConsumed + rstConsumption!ConsumptionGallons
    End If
    
    rstConsumption.MoveNext
Loop
' --------- End ---------------------

'display the results
MsgBox (FormatNumber(dblGallonsConsumed, 2) & " gallons were consumed in " & Format(dtemyDate, "mmmm") & " " & Year(dtemyDate))

cheers,

[AL Engineer]

Mshmyob,

I made it through the holidays and finally got to try out your code. It works great! Would you entertain another another question?
We use more than one chemical. I have these chemicals in a table "Inventory" With fields "Chemical_Name" , and "Inventory_ID"( text, autonumber). This is linked to the Consumption table by the Inventory_ID.
Is there a way to run your code to return the amount of each chemical consumed in a month?

Many Thanks

[mshmyob]

I am sure it can.

Add a combo box to the form and call it something like cboChemical (display the chemical name - first bound column to chemical ID and 2nd column to chemical name) You do not need to bind the combo box control to anything.

Change your code like so:

Dim dtemyDate As Date
' add this
Dim lngChemicalID As Long

' date must be entered as the first day of any month and year - ie: 1/1/10 for Jan 1 2010
dtemyDate = Me.txtMyDate

' add this
' get the chemical ID to filter consumption to a specific chemical
lngChemicalID = Me.cboChemical.Column(0)

' ----- determines the total number of days in a given month ------------------
Dim lngNextMonth, lngEndofMonth As Date
Dim intDaysInMonth As Integer

dteNextMonth = DateAdd("m", 1, dtemyDate)
dteEndofMonth = dteNextMonth - DatePart("d", dteNextMonth)
intDaysInMonth = DatePart("d", dteEndofMonth)
'------- End -------------------

' --------- filter the recordset of consumption to the given month and year ----------
Dim db As dao.Database
Dim rstConsumption As dao.Recordset

strSQL = "SELECT * FROM tblConsumption WHERE tblConsumption.ConsumptionLoad <= #" & dteEndofMonth
strSQL = strSQL & "# AND (tblConsumption.ConsumptionEmpty >= #" & dtemyDate
strSQL = strSQL & "# OR isnull(tblConsumption.ConsumptionEmpty))"
' this line added for specific chemical filter
strSQL = strSQL & "AND tblConsumption.InventoryID=" & lngChemicalID
strSQL = strSQL & " ORDER BY tblConsumption.ConsumptionLoad;"

Set db = CurrentDb
Set rstConsumption = db.OpenRecordset(strSQL)
' ----------- End -----------------

' ----------------- calculate the gallons consumed for the given month and year --------------
Dim dblGallonsConsumed, dblAvgConsumptionDay As Double
Dim intDaystoConsume As Integer
Dim intMonth As Integer

Do While Not rstConsumption.EOF
    
    ' this eliminates errors with NULLS if no ConsumptionEmpty date
    If IsNull(rstConsumption!ConsumptionEmpty) Then
        intMonth = Month(dtemyDate)
    Else
        intMonth = Month(rstConsumption!ConsumptionEmpty)
    End If
    
    If intMonth > Month(dtemyDate) Or Month(rstConsumption!ConsumptionLoad) < Month(dtemyDate) Then
        ' number of days to consume - gallons consumed are assumed to start at beginning of day, hence + 1
        ' ie: Jan 28, 2010 is 4 days of consumption till end of month
        intDaystoConsume = DateDiff("d", rstConsumption!ConsumptionLoad, rstConsumption!ConsumptionEmpty) + 1
        ' average consumption per day
        dblAvgConsumptionDay = rstConsumption!ConsumptionGallons / intDaystoConsume
        ' gallons consumed till end of month
        If Month(rstConsumption!ConsumptionEmpty) > Month(dtemyDate) Then
            dblAvgConsumptionDay = dblAvgConsumptionDay * (intDaysInMonth - Day(rstConsumption!ConsumptionLoad) + 1)
        Else
            dblAvgConsumptionDay = dblAvgConsumptionDay * (Day(rstConsumption!ConsumptionEmpty))
        End If
        ' running total consumed for the month/year
        dblGallonsConsumed = dblGallonsConsumed + dblAvgConsumptionDay
    Else
        ' running total consumed for the month/year
        dblGallonsConsumed = dblGallonsConsumed + rstConsumption!ConsumptionGallons
    End If
    
    rstConsumption.MoveNext
Loop
' --------- End ---------------------

'display the results
MsgBox (FormatNumber(dblGallonsConsumed, 2) & " gallons were consumed in " & Format(dtemyDate, "mmmm") & " " & Year(dtemyDate))

Note that this has not been tested.

I added in the first few lines to pick up the chemical ID. I then changed the SQL statement to filter based on that chemical ID.

See if this gets you anywhere. If not I will try to do some testing.

cheers,