Total and query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SG - 17 Nov 2007 15:26 GMT

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

Reply to this Message

Marshall Barton - 18 Nov 2007 00:52 GMT

>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
[quoted text clipped - 9 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

SG - 18 Nov 2007 09:35 GMT

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
[quoted text clipped - 30 lines]
> each question separately to a specific forum instead of
> crossposting multiple questions to multiple forums.

Reply to this Message

Marshall Barton - 18 Nov 2007 14:21 GMT

>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Wolfgang Kais - 18 Nov 2007 00:53 GMT

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])

Signature

Regards,
Wolfgang

> 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
[quoted text clipped - 15 lines]
>
> S

Reply to this Message