HELP

  • Thread starter Thread starter jwrnana
  • Start date Start date
J

jwrnana

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

Steve Schapel said:
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
Click to expand...
Click to expand...
 
Joy Rose,

Since your Query includes the OrderDetails table, then it will produce
one row for each order detail item. Your calculated field TotalPrice
will relate to each item. It is not possible to see the line items
totals *and* the invoice totals in the same query. If you want to see
the invoice totals (I presume this relates to each PurchaseOrder?),
you should make your query into a Totals Query, by selecting Totals
from the View menu of your query design window. Leave 'Group By' in
the Totals row of the qhery design grid for all fields except
TotalPrice, where you would put Sum in the Totals row.

By the way, just a couple of fine-tuning points:
1. [OrderDetails]![Quantity]*[OrderDetails]![UnitPrice] is not really
correct syntax. The separator should be a . not a ! so...
[OrderDetails].[Quantity]*[OrderDetails].[UnitPrice]
However, unless you have a Quantity Field or a UnitPrice field in one
of the other tables in the query, as well as OrderDetails, it is not
necessary to include the table qualifier anyway, so...
TotalPrice: [Quantity]*[UnitPrice]
2. It is not a good idea to include a # as part of a field name.

As regards your report, do you mean the report is a list of all
invoices/orders for a particular customer, or for a particular time
period, or some such. If you make the totals query as outlined above,
and then base your report on it, and then you want the overall total
of all invoices included in the report, you should put an unbound
textbox in the rerport footer section, and then in its controlsource
put =Sum([SumOfTotalPrice])

- Steve Schapel, Microsoft Access MVP
 
Steve - I tried to create a new query without order details; however, no
where do I see invoice total in any table. I tried to create a
Sum([SumOfTotalPrice]), but get the message that i.e. - order date, shipping
method, etc. is not included with the expression. I know that on the actual
invoice, of course, there is a total invoice amount. On the table there is
not a field for invoice total. On the Orders table, there is not an invoice
total field. On a predesigned report, Sales by Customer query, there is a
field for total -- however, this report gives me a NULL when I try to run
it. I have copied that expression to no avail either. JR (Your direct
email address does not work)

Steve Schapel said:
Joy Rose,

Since your Query includes the OrderDetails table, then it will produce
one row for each order detail item. Your calculated field TotalPrice
will relate to each item. It is not possible to see the line items
totals *and* the invoice totals in the same query. If you want to see
the invoice totals (I presume this relates to each PurchaseOrder?),
you should make your query into a Totals Query, by selecting Totals
from the View menu of your query design window. Leave 'Group By' in
the Totals row of the qhery design grid for all fields except
TotalPrice, where you would put Sum in the Totals row.

By the way, just a couple of fine-tuning points:
1. [OrderDetails]![Quantity]*[OrderDetails]![UnitPrice] is not really
correct syntax. The separator should be a . not a ! so...
[OrderDetails].[Quantity]*[OrderDetails].[UnitPrice]
However, unless you have a Quantity Field or a UnitPrice field in one
of the other tables in the query, as well as OrderDetails, it is not
necessary to include the table qualifier anyway, so...
TotalPrice: [Quantity]*[UnitPrice]
2. It is not a good idea to include a # as part of a field name.

As regards your report, do you mean the report is a list of all
invoices/orders for a particular customer, or for a particular time
period, or some such. If you make the totals query as outlined above,
and then base your report on it, and then you want the overall total
of all invoices included in the report, you should put an unbound
textbox in the rerport footer section, and then in its controlsource
put =Sum([SumOfTotalPrice])

- Steve Schapel, Microsoft Access MVP


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
Click to expand...
Click to expand...
 
Joy Rose,

If you make a query without the Order Details, you will not be able to
get an invoice total, because the data required for this (i.e.
Quantity and Unit Price) are in the Order Details. It is correct that
you can not see any total invoice amount in any table. This is a
calculated amount ans should not be stored in a table at all. It
should be the result of a calculation either in a query, or directly
in the controlsource of a textbox on a form or report. My suggestion
was to make your existing query into a Totals Query, using the
instructions I suggested previously, and then use this query as the
RecordSource of your report. If you do this, then the query itself
will return one row for each invoice, and show the invoice total in
the TotalPrice (calculated) field. Therefore your report will have a
TotalPrice field, if the report is based on this query. Therefore you
can get a total of the invoice totals for all invoices shown on the
report by the unbound textbox in the report footer. However, I did
tell you wrong before. If you follow these directions, your textbox's
controlsource should be =Sum([TotalPrice])

As regards my return email address, I know it does not work. This is
by design.

- Steve Schapel, Microsoft Access MVP
 
Joy Rose,

Can you please explain where you entered the expression, and what you
were doing when you got the error message?

- Steve Schapel, Microsoft Access MVP
 
Joy Rose,

Did you try the suggestion I gave in a previous reply?
"If you want to see the invoice totals (I presume this relates to each
PurchaseOrder?), you should make your query into a Totals Query, by
selecting Totals from the View menu of your query design window.
Leave 'Group By' in the Totals row of the query design grid for all
fields except TotalPrice, where you would put Sum in the Totals row."

This applies to your original query. I am not sure it will owrk with
the new query, because I don't know what the Order ID filed is. If
this is a separate value for each order item, then I can't really see
why you included it in the query, and for the tatals query to work,
you will need to remove it.

- Steve Schapel, Microsoft Access MVP
 
Finally! When I took Unit Price and Quantity out of the query, it worked.

Steve Schapel said:
Joy Rose,

Did you try the suggestion I gave in a previous reply?
"If you want to see the invoice totals (I presume this relates to each
PurchaseOrder?), you should make your query into a Totals Query, by
selecting Totals from the View menu of your query design window.
Leave 'Group By' in the Totals row of the query design grid for all
fields except TotalPrice, where you would put Sum in the Totals row."

This applies to your original query. I am not sure it will owrk with
the new query, because I don't know what the Order ID filed is. If
this is a separate value for each order item, then I can't really see
why you included it in the query, and for the tatals query to work,
you will need to remove it.

- Steve Schapel, Microsoft Access MVP


I also created a new query as follows:
Account#(CustomerTable) Purchase Order Number(OrderTable) Order
ID(OrderDetailTable) and OrderTotal:[Quantity]*[UnitPrice] -- this gave me
line item totals, not totals by PO. Therefore, I added a field
SUM[OrderTotal] and got the error"You may have entered an operand without an
operator. I changed this to SUM([OrderTotal]) and got the error "Cannot
have aggregate function in expression (Sum(SUM([OrderTotal])))"
Click to expand...
Click to expand...
 
Back
Top