Access 2007 - Sum() Function Against Query

[ddeford]

Hello.

I'm a fairly advanced .Net programmer and have a few years of experience with Access as well. I'm currently working on a complex Access 2007 DB for a customer and ran into a snag. Hopefully someone has seen this and can offer an easy work-around.

I'll try to keep this as simple as possible:

It seems as though the Sum() function, when applied to a textbox, doesn't work when applied against a query. Here's the SQL of my Query:

SELECT
  tblOrders.WRID,
  tblOrders.WBS,
  tblOrders.StartDate,
  tblOrders.Initiator,
  (SELECT Sum([qryLaborCost].[PassedCost]) FROM qryLaborCost WHERE qryLaborCost.WR = tblOrders.WRID) AS LaborCostQry,
  (SELECT Sum([qryMaterialCost].[ItemPrice]) FROM qryMaterialCost WHERE  qryMaterialCost.WR = tblOrders.WRID) AS MaterialCostQry,
  (SELECT Sum([qryTravelCost].[Cost]) FROM qryTravelCost WHERE qryTravelCost.WR = tblOrders.WRID) AS TravelCostQry,  
  IIf(IsNull([TravelCostQry]),0,[TravelCostQry]) AS TravelCost, [LaborCost]+[MaterialCost]+[TravelCost] AS SubTotal
FROM tblOrders;

The final "IIf" statement ensures that there is some kind of data in there in the event of a Null field (in this case, a zero), but creates a SubTotal of the row.

Here's some sample data that the query shows:

WRID        WBS            StartDate    Initiator        LaborCost        MaterialCost    TravelCost    SubTotal
2012000001        12345.ABC        04-Sep-12    Dan            0            5.075        0            $5.08
2012000002        12345.ABC        04-Sep-12    Dan            1275        0            $18.75        $1,293.75
2012000003        12345.ABC        05-Sep-12    Dan            900            0            $35.00        $935.00

This is all fine and works as expected.

Now, on the sub form that displays this data, I have created a text box in the form footer with the following for the "Datasource":

=Sum([SubTotal])

Here's the kicker - the textbox shows a value of $15.23, which is the first value in the result set, three times (5.08 x 3). If I delete one of the other values, that number becomes the first "subtotal" in the result set multiplied by two. Which leads me to believe that the logic behind the Sum() function is wrong. It appears to be a "for" loop that never moves to the next record, and uses the number of records in the result set as the exit variable.

Any help would be appreciated. Maybe just an MS Office update that needs to be run, perhaps??

Thanks,

Dan

 

[AlexHedley]

If you change the textbox source to just subtotal on the sub form footer what does it show?

 

[ddeford]

If you change the textbox source to just subtotal on the sub form footer what does it show?

As expected, it shows the first SubTotal amount.

I'm working on doing it via VBA and I'm having much better luck. Just a little snag I'm working through now:

Since it's a subform, my loop errors out when there are no records associated with the parent form. But it works beautifully when there are ANY records associated with the parent form.

I'll post my code when I get it figured out.

Thanks!!

 

[AlexHedley]

Strange behaviour, not that you need to but if you create a second query to sum up your subtotal what value does it return.

I do prefer coding stuff but doesn't fill you full of confidence if it doesn't work.

 

[ddeford]

OK, here's what I did:

1. I created a text box named txtTotalCosts on the parent form
2. On the parent form, I placed the following code in the Form_Current() event (obviously, rename the [SubFormName] sections to your own sub form names):

Private Sub Form_Current()
    Dim dblTotalCost As Double
    dblTotalCost = 0
    If [SubFormName].Form.RecordsetClone.RecordCount = 0 Then
       'Do Nothing
    Else
        [SubFormName].Form.Recordset.MoveFirst
        Do Until [SubFormName].Form.Recordset.EOF
            dblTotalCost = dblTotalCost + [SubFormName].Form.Subtotal.Value
            [SubFormName].Form.Recordset.MoveNext
        Loop
       [SubFormName].Form.Recordset.MoveFirst
    End If
    Me.txtTotalCosts = dblTotalCost
End Sub

3. Breathed a sigh of relief.

 

[AlexHedley]

Glad to see you got it working

 

[vbaInet]

I suspect you have a significant number of records, i.e. at least 2000 records, which may be causing the behaviour you see. Instead of looping through the recordset to get a Sum, you could have:

1. Performed a MoveLast on the Recordset (not the clone) when the form loads
2. Then MoveFirst

From then on the Sum function should display correctly.

Or you can use the DSum() function

Or the quickest way would be to create a recordset based on an SQL statement that includes the summed result, and return that field's value to the textbox.