Totals and subtotals in forms, reports and queries

[jwrnana]

This original request had a subject to vague. I tried to send to Steve, but
message came back.
Thanks for your assistance. JR


Sent 7-27-03
Steve -- thank you so much for responding. I don't know why something that
should be so simple is so difficult for me. I used to pride myself in being
able to handle the computer, but now I have my doubts!

I have a query with the following: OrderDate (from Orders);
ShippingMethodID (from Orders); Account# (from customers);
PurchaseOrder#(from Orders); and
TotalPrice:[OrderDetails]![Quantity]*[OrderDetails]![UnitPrice]. This
gives me everything I am needing EXCEPT in the Total Price field, I would
like to have ONE line item for the ENTIRE invoice -- not each individual
line item totaled. Then, on the report produced from this, I want to total
the Total Invoice field.

Your assistance is greatly appreciated.

Joy Rose

jr,

Whereas it is possible to create a Totals Query to get the total
amount for each invoice, I would suspect this is not applicable to
your current needs. Assuming you have a Form for your Order/Order
Details information, you are best to display the invoice total by
adding an unbound textbox to the form footer, and in its controlsource
enter something like =Sum([Quantity]*[Unit Price])

Another thing to understand which might help you, is that Forms and
Reports don't have fields. They have controls, which may be bound in
order to interact with the fields in the tables, either directly, or
indirectly via a Query. If you need a field available via your form
or report, you first of all have to make sure it is included in the
Recordsource of the form or report.

Sorry I am not able to give very specific advice, without knowing more
about your database, but my comments above contain a number of key
words which you can look up in Access Help or in your book.

- Steve Schapel, Microsoft Access MVP

In Access, I opened Order entry database and built my information. This is
my first time so bear with me.

1.Query -- I have designed a query with multiple fields including invoice#,
unit price and Quantity. I can get total by line item, but I cannot get
TOTAL INVOICE amount as 1 line item. i.e. -- the invoice may have 7 line
items, I get 7 totals, and them I can total them, but I do not want to have
to do that. I only want the total of all items on one invoice.

2. On the predesigned Invoice report -- I have added the fields "LineItem"
and "Serial#". These have been added to orders and orders subforms as well
as the invoice design form. On LineItem I get "#NAME?" -- I brought this
over from the Form from OrderDetailsSubform and on Serial # I get #Error. I
have searched the answer wizard, but still cannot find my answer.

3. On the predesigned Sales by Customer report -- I have changed nothing on
this report; however, I am getting NULL?

I have done fairly well, except when it comes to creating totals on queries,
reports, forms, etc. For some reason, I cannot condense the info as needed.

Thanks for all the help to come.

jr

 

[Duane Hookom]

If you want some values totaled by invoice, there should be a field that
identifies "Invoice". Then on a report you could create an Invoice group
footer and total your values there. If your form is filtered by the Invoice
then add text boxes with control sources like Steve suggested should work
great.

Take a look at the Northwind.mdb that gets installed with Office/Access.

--
Duane Hookom
MS Access MVP

This original request had a subject to vague. I tried to send to Steve, but
message came back.
Thanks for your assistance. JR


Sent 7-27-03
Steve -- thank you so much for responding. I don't know why something that
should be so simple is so difficult for me. I used to pride myself in being
able to handle the computer, but now I have my doubts!

I have a query with the following: OrderDate (from Orders);
ShippingMethodID (from Orders); Account# (from customers);
PurchaseOrder#(from Orders); and
TotalPrice:[OrderDetails]![Quantity]*[OrderDetails]![UnitPrice]. This
gives me everything I am needing EXCEPT in the Total Price field, I would
like to have ONE line item for the ENTIRE invoice -- not each individual
line item totaled. Then, on the report produced from this, I want to total
the Total Invoice field.

Your assistance is greatly appreciated.

Joy Rose

jr,

Whereas it is possible to create a Totals Query to get the total
amount for each invoice, I would suspect this is not applicable to
your current needs. Assuming you have a Form for your Order/Order
Details information, you are best to display the invoice total by
adding an unbound textbox to the form footer, and in its controlsource
enter something like =Sum([Quantity]*[Unit Price])

Another thing to understand which might help you, is that Forms and
Reports don't have fields. They have controls, which may be bound in
order to interact with the fields in the tables, either directly, or
indirectly via a Query. If you need a field available via your form
or report, you first of all have to make sure it is included in the
Recordsource of the form or report.

Sorry I am not able to give very specific advice, without knowing more
about your database, but my comments above contain a number of key
words which you can look up in Access Help or in your book.

- Steve Schapel, Microsoft Access MVP

In Access, I opened Order entry database and built my information. This is
my first time so bear with me.

1.Query -- I have designed a query with multiple fields including invoice#,
unit price and Quantity. I can get total by line item, but I cannot get
TOTAL INVOICE amount as 1 line item. i.e. -- the invoice may have 7 line
items, I get 7 totals, and them I can total them, but I do not want to have
to do that. I only want the total of all items on one invoice.

2. On the predesigned Invoice report -- I have added the fields "LineItem"
and "Serial#". These have been added to orders and orders subforms as well
as the invoice design form. On LineItem I get "#NAME?" -- I brought this
over from the Form from OrderDetailsSubform and on Serial # I get

#Error.

I

nothing

on

 

[Duane Hookom]

Just noticed Steve had replied to your original thread. You shouldn't start
a new thread on the same question. Please go back to the previous thread for
all responses.

--
Duane Hookom
MS Access MVP

If you want some values totaled by invoice, there should be a field that
identifies "Invoice". Then on a report you could create an Invoice group
footer and total your values there. If your form is filtered by the Invoice
then add text boxes with control sources like Steve suggested should work
great.

Take a look at the Northwind.mdb that gets installed with Office/Access.

--
Duane Hookom
MS Access MVP

This original request had a subject to vague. I tried to send to Steve, but
message came back.
Thanks for your assistance. JR


Sent 7-27-03
Steve -- thank you so much for responding. I don't know why something that
should be so simple is so difficult for me. I used to pride myself in being
able to handle the computer, but now I have my doubts!

I have a query with the following: OrderDate (from Orders);
ShippingMethodID (from Orders); Account# (from customers);
PurchaseOrder#(from Orders); and
TotalPrice:[OrderDetails]![Quantity]*[OrderDetails]![UnitPrice]. This
gives me everything I am needing EXCEPT in the Total Price field, I would
like to have ONE line item for the ENTIRE invoice -- not each individual
line item totaled. Then, on the report produced from this, I want to total
the Total Invoice field.

Your assistance is greatly appreciated.

Joy Rose

jr,

Whereas it is possible to create a Totals Query to get the total
amount for each invoice, I would suspect this is not applicable to
your current needs. Assuming you have a Form for your Order/Order
Details information, you are best to display the invoice total by
adding an unbound textbox to the form footer, and in its controlsource
enter something like =Sum([Quantity]*[Unit Price])

Another thing to understand which might help you, is that Forms and
Reports don't have fields. They have controls, which may be bound in
order to interact with the fields in the tables, either directly, or
indirectly via a Query. If you need a field available via your form
or report, you first of all have to make sure it is included in the
Recordsource of the form or report.

Sorry I am not able to give very specific advice, without knowing more
about your database, but my comments above contain a number of key
words which you can look up in Access Help or in your book.

- Steve Schapel, Microsoft Access MVP


In Access, I opened Order entry database and built my information.

This
is

my first time so bear with me.

1.Query -- I have designed a query with multiple fields including invoice#,
unit price and Quantity. I can get total by line item, but I cannot get
TOTAL INVOICE amount as 1 line item. i.e. -- the invoice may have 7 line
items, I get 7 totals, and them I can total them, but I do not want

to
have

to do that. I only want the total of all items on one invoice.

2. On the predesigned Invoice report -- I have added the fields "LineItem"
and "Serial#". These have been added to orders and orders subforms

as
well

as the invoice design form. On LineItem I get "#NAME?" -- I brought this
over from the Form from OrderDetailsSubform and on Serial # I get

#Error.

I
have searched the answer wizard, but still cannot find my answer.

3. On the predesigned Sales by Customer report -- I have changed

nothing

on
this report; however, I am getting NULL?

I have done fairly well, except when it comes to creating totals on queries,
reports, forms, etc. For some reason, I cannot condense the info as needed.

Thanks for all the help to come.

jr