Total and query

[SG]

I have a form frmreferb which has a sunbform called frmreferbdetails
(Continues Form) on the subform I have a text box called quantity and a
Priceperunit text box. I have a Total Text box with the control source set
to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I
would also like to do is calculate the total text box to give me a grand
total on the frmreferb form. Does anyone have any ideas?

The other thing I am trying to do is I have created a query called
ReferbTotalsQuery this contains the quantity and total of each item. I need
to calculate the total of each line ad then calculate the totals to create a
grand total of which I would like to display in a text field on the
frmreferb form. I have tried to get my head around this but I am struggling
with not storing the total of each invoice.

Any suggestions or advise would be gratefully received!

KR

S

 

[Marshall Barton]

I have a form frmreferb which has a sunbform called frmreferbdetails
(Continues Form) on the subform I have a text box called quantity and a
Priceperunit text box. I have a Total Text box with the control source set
to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I
would also like to do is calculate the total text box to give me a grand
total on the frmreferb form. Does anyone have any ideas?

The other thing I am trying to do is I have created a query called
ReferbTotalsQuery this contains the quantity and total of each item. I need
to calculate the total of each line ad then calculate the totals to create a
grand total of which I would like to display in a text field on the
frmreferb form. I have tried to get my head around this but I am struggling
with not storing the total of each invoice.

You can calulate the total if the subform records in a text
box in the subform's header or footer section. Then text
box's expression sould be:
=Sum([Quantity]*[PricePerUnit])

If you really need to display that total on the main form
(and make the subform's header/footer invisible, then use a
main form text box with an expression like:
=subformcontrol.Form.textboxinsubform

I don't understand what you want from the query in your
second question. It sounds to me like the same calculation
as in the subform.

You are more likely going to get better responses by posting
each question separately to a specific forum instead of
crossposting multiple questions to multiple forums.

 

[Wolfgang Kais]

Hello SG.
Second question:
You can't display/calculate item values AND a grand total in only one query.
Use a report for that.
First question:
I guess that you have tried to use a textbox in the form footer calculating
the sum of the "Total Textbox"? Well, this won't work. Aggregate functions in
forms/reports require fields from the record source, in this case:
=Sum([Quantity]*[PricePerUnit])

 

[SG]

Marshall,

The second question is to enable me to display a running total of 'Ongoing'
costs of all jobs within my database. This would then highlight to the user
costs to date. Is this possible?

KR

S

I have a form frmreferb which has a sunbform called frmreferbdetails
(Continues Form) on the subform I have a text box called quantity and a
Priceperunit text box. I have a Total Text box with the control source set
to =[Quantity]*[PricePerUnit] this gives me the total of each line. What I
would also like to do is calculate the total text box to give me a grand
total on the frmreferb form. Does anyone have any ideas?

The other thing I am trying to do is I have created a query called
ReferbTotalsQuery this contains the quantity and total of each item. I
need
to calculate the total of each line ad then calculate the totals to create
a
grand total of which I would like to display in a text field on the
frmreferb form. I have tried to get my head around this but I am
struggling
with not storing the total of each invoice.

You can calulate the total if the subform records in a text
box in the subform's header or footer section. Then text
box's expression sould be:
=Sum([Quantity]*[PricePerUnit])

If you really need to display that total on the main form
(and make the subform's header/footer invisible, then use a
main form text box with an expression like:
=subformcontrol.Form.textboxinsubform

I don't understand what you want from the query in your
second question. It sounds to me like the same calculation
as in the subform.

You are more likely going to get better responses by posting
each question separately to a specific forum instead of
crossposting multiple questions to multiple forums.

 

[Marshall Barton]

The second question is to enable me to display a running total of 'Ongoing'
costs of all jobs within my database. This would then highlight to the user
costs to date.

For a form, you have to do it in the form's record source
query. Here's an air code example:

SELECT T.id, T.job. T.cost, T.sort, ...,
(SELECT Sum(X.cost)
FROM table As X
WHERE X.job = T.job
And X.sort <= T.sort) As RunTotal
FROM table As T

FYI: a running total query is logically the same as a
ranking query.

A running total in a report is easier because you can use a
text box with the RunningSum property.