Creating partial totals in a subform 1

[doctorswamp]

Hi

I have a form + subform to record movements of batches of garden plants from stock. The Batches in the subform can be categorised in a BatchType field as Saleable, Growing or NewlySeeded.

I'd like to total the Saleable items only, and have tried creating an unbound field in the subform footer and setting its ControlSource property to an SQL statement that looks like this

="SELECT me.Batches.ItemID, me.Batches.BatchType, Sum(me.Batches.Quantity) AS TotalQuantity FROM Batches GROUP BY me.Batches.ItemID, me.Batches.BatchType HAVING (((me.Batches.BatchType)="Saleable"))"

It gives me error messages about syntax.

Is this a sensible way to be trying to arrive at the total, and if so what might the syntax error be?

Thanks

 

[Darrylles]

Doctor,

It might be helpful for you to state the syntax error at this stage, rather than us having to ask you for it.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[TheAceMan1]

How are ya doctorswamp . . .

Hmmmm . . . the entire SQL is in error!

When you get the SQL right you need to return the result thru a function.

In the query window start a new query but don't select any tables. Goto SQL view and paste over [blue]Select;[/blue] with the following:

[blue]SELECT Sum(Quantity) AS TotalQuantity
FROM Batches
GROUP BY ItemID, BatchType
HAVING (BatchType="Saleable");[/blue]

See if it works. The Idea is construct a Sum Query that works first! . . .

See Ya! . . . . . .

 

[Darrylles]

Ace,

I bow of course ...

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[TheAceMan1]

Howdy Darrylles! . . .

No Need! . . . [blue]you just keep getting better instead![/blue]

[blue]You take care . . . Ya Hear![/blue]

See Ya! . . . . . .

 

[doctorswamp]

Hi Ace - well named.

OK, have the sum query working properly. Next question is using it in the subform footer, and you gave a hint about using the SQL version to create a function. May need another hint or two.

Your help is much appreciated as ever, so you take care too, ya hear!

 

[TheAceMan1]

doctorswamp . . .

The function would look like:

[blue]Public Function SaleableTlt()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "[purple][b][i]Your SQL Statement[/i][/b][/purple]"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      SaleableTlt = rst![purple][b][i]FieldNameOfInterest[/i][/b][/purple]
   End If
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

Then in the control source of an unbound textbox:

[blue]=SaleableTlt()[/blue]

[blue]Post back the SQL[/blue] of the working query . . .

See Ya! . . . . . .

 

[doctorswamp]

Hiya Aceman

This is the SQL straight from a query with limited fields compared with the one feeding the subform. If I put more fields in it doesn't total but shows the separate items.

SELECT Batches.ItemID, Batches.BatchType, Sum(Batches.Quantity) AS SumOfQuantity
FROM Batches
GROUP BY Batches.ItemID, Batches.BatchType
HAVING (((Batches.BatchType)="S"));

Have tried this as a simple long string without returns, with 'S' instead of "S", without the final ; and enclosed in quotes.

Then have tried various things as your FieldNameofInterest, but they give #Error.

Sorry to be so slow.

 

[PHV]

Why not simply use the DSum function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

Howdy PHV! . . .

Couldn't agree more with the [blue]simplicity of DSum[/blue].

However due to growing record count of four of my clients and complaints of the DB slowing, I've spent quite a bit of time converting enough DSums to faster methods. Boy did I use it abundantly!

Having paid the price I've become reluctant to use it unless I know record counts will be on the lower side . . .

See Ya! . . . . . .

 

[TheAceMan1]

doctorswamp . . .

Try This:

[blue]Public Function SaleableTlt()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Sum(Batches.Quantity) AS [purple][b]SumOfQuantity[/b][/purple] " & _
         "FROM Batches " & _
         "GROUP BY Batches.ItemID, Batches.BatchType " & _
         "HAVING (Batches.BatchType='S');"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      SaleableTlt = rst![purple][b]SumOfQuantity[/b][/purple]
   End If
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

And PHV's suggestion:

[blue]= =dsum("[Quantity]","Batches","BatchType='S'")[/blue]

BTW: Do you have a batch type of S?

See Ya! . . . . . .

 

[doctorswamp]

AceMan

Joy and rapture, it works. So huge thanks. And yes, I abbreviated Saleable to S as a batch type.

AceMan and PHV
On the DSum approach, the problem with

=dsum("[Quantity]","Batches","BatchType='S'")

was that it totalled all batch quantities in the subform, irrespective of batch type.

I tried this modifed version, which worked too, but will take AceMan's advice and use the function approach.

=DSum("Quantity","qryBatches-All","[ItemID] = " & [ItemID] & " AND BatchType='S'")

There's a related question but I'll check FAQs and post another thread if need be.

Many thanks again.

 

[doctorswamp]

Hiya AceMan/PHV

Have discovered that the function produces a total that isn't specific to the current ItemID. Going to the next record it immediately shows the previous record's total.

The DSum approach works with a minor hitch that in starting to create a new entry in the subform the total changes to #ERROR until something is entered in any of the subform fields.

One of the additional subform fields is a date and making this =Date() fixes it. This isn't very convenient but putting a default of say S in BatchType doesn't fix the problem.

It's more intriguing than a show stopper. I've tried putting in an IIF(IsNull ...) line but to no avail.

 

[TheAceMan1]

doctorswamp . . .

You need to include the [blue]ItemID[/blue] in the [blue]Havimg Clause[/blue] in SQL of the function same as you did for DSum.

See Ya! . . . . . .