Sum Calculated fields on Continuous form

[Guest]

Morning All

I really hope someone can help with this as its driving me mad!!

I have a continuous form which contains all week ending dates returned from
the query and the sub total for each week - the sub total is calculated using
=DSum("PltTotal","Union_PrevPltDtl"). This is taking the total from another
query, which uses the week ending date returned on this subform to calculate
the correct total. The sub totals returned are correct.

My problem is I need to show a total in the footer of the form of all the
sub totals and I can't seem to do it. If I Sum the calculation above in the
footer all it does is multiply the 1st total returned by the number of
records returned. I have tried various options and can't get the total
correct. Basically I need to add all totals on the subform but because they
are calculated fields based on the w/e date I can't.

Any help would be most appreciated.

Thank You

Regards

Sonya

 

[Wayne Morgan]

I suspect that if you check, the subtotal that is returned is for the
current record. If you move to the next record, all of the subtotals will
update to show the subtotal for the next record.

To do what you're asking, you need to create the subtotals in the query
feeding the continuous form using a calculated field in the query. This will
give you a field to bind the subtotal control to so that it will be
different for each record. You will also then be able to do a sum on this
field in the form's footer.

 

[Guest]

No, the subtotal returned is based on the week ending date on the sub form
and doesn't change when moving from the current record.

I can't create the calculated field in the query in the continuous form as
it uses the dates returned in the query for the continuous form to calculate
the subtotals in the continuous form DSum field.

I don't know if this is making sense but it won't work to do as Wayne
advised. Any other suggestions???

Thanks

Sonya

 

[Wayne Morgan]

Is this Subtotal control in the main part (the continuous part) of the
subform? Does each record in the subform have a week ending date? If so,
then I assume that in "=DSum("PltTotal","Union_PrevPltDtl")" that
Union_PrevPltDtl is a query and that query refers to the week ending date.
(Please post the SQL view of the query.) If not, then how does the query
know which sum goes with which week? If it does take the date or other
information from the record in the subform, then it will take the
information from the current record, which is the problem I mentioned to
start with.

Is the week ending date part of the continuous part of the subform, in the
subform's form header or, part of the main form?

 

[Guest]

Yes the control is in the continuous part of the subform and each record does
have a week ending date which is in the detail continuous part of the
subform. Yes Union_PrevPltDtl is a query that uses the week ending date
returned in the subform to calculate the total by using a function.

The subform is populated by 1 query that returns the w/e dates and the Union
query uses those dates to return the sum in the continuous form. the SQL
query is below for this.

SELECT HiredPlt_Tbl.HP_PO, HiredPlt_Tbl.HP_TaskNum,
HiredPlt_Tbl.HP_Supplier, HiredPlt_Tbl.HP_PlantCode,
HiredPlt_Tbl.HP_Description, HiredPlt_Tbl.HP_UOM, HiredPlt_Tbl.HP_Qty,
Format([HP_UnitPrice],"Currency") AS Expr1,
Format(HPlntCalc([HP_UOM],[HP_OnHire],[HP_OffHire],Forms!Frm_Measure!SubFrm_PrevLTPlt!PLTPltDate,[HP_UnitPrice],[HP_Qty]),"Currency")
AS HPTotal, DLookUp("LT_WEDATE","Qry_PrevLtPltDetail") AS HPWeDate
FROM HiredPlt_Tbl
WHERE
(((HiredPlt_Tbl.HP_TaskNum)=Left([Forms]![Frm_Measure]![TaskNo],2)+Right([Forms]![Frm_Measure]![TaskNo],5))
AND ((HiredPlt_Tbl.HP_OnHire)<[Forms]![Frm_Measure]![WDate]-7 And
(HiredPlt_Tbl.HP_OnHire)<=[Forms]![Frm_Measure]![SubFrm_PrevLTPlt]![PLTPltDate])
AND
((HiredPlt_Tbl.HP_OffHire)>[Forms]![Frm_Measure]![SubFrm_PrevLTPlt]![PLTPltDate]
Or (HiredPlt_Tbl.HP_OffHire) Between
[Forms]![Frm_Measure]![SubFrm_PrevLTPlt]![PLTPltDate]-7 And
[Forms]![Frm_Measure]![SubFrm_PrevLTPlt]![PLTPltDate]-2));

This query uses the week ending date on the subform above to calculate the
total out - can the date returned from the subform recordsource query be used
instead of the one on the subform? When I tried this it only did it for the
1st date returned not the others.

On the subform the date returned looks like this:-

W/E Date Total
04/06/06 £1000
28/05/06 £500

Total (in footer) should be £1500 but it changes when the above record is
current (the totals above do not change as they are calculated in the
Union_PrevLtPlt query)

Thanks for your time Wayne - I don't know if this is too complicated to
understand but hope not.

 

[Wayne Morgan]

The query you've posted appears to be the one for the subform, correct?
Also, it is using Qry_PrevLtPltDetail to get the w/e date.

Everything you send me leads to somewhere else. This could go on forever.
Will you please zip the file and mail it to (e-mail address removed)? Add a note
telling me which form and controls to look at.

Thanks,