SUM Problem

[Guest]

If someone could help me with this it would be great.
It's probally something simple but after two days I need help. I'm running a
report for and invoice. Because of the difference in what is taxed and what
is not taxed I have calculated expressions on the invoice form to give me a
total. (I will have one item for "Labor" which is not taxed and possibly
several "Parts" that are taxed). Due to the calculations the invoice total is
not returned back to the table "tblInvoiceDetails". It would be even better
if I could get that "Total" back onto a table, but I believe that would
creates a circular calculation error. If that can be done somehow, please let
me know.
When I run a report, sorting for a particular date, I get all the invoices
for that date along with the individual charges. The problem arrises when I
try to total the Labor, it shows, and adds a labor charge for that Invoice
next to every part. When I do a SUM for the total labor charges for 3
invoices, I don't get the sum of the labor charges for the three invoices, I
get a total labor charge for labor times the number of parts I sold. If I
sold 20 items on the three invoices I get the sum of sixty labor charges.
It's easy to hide the duplicate values but it still adds them. Even if I
format and sort the data differently, I can get the Labor to show up only
once per invoice, but when I do the SUM it will still add it up for every
item sold.
The report is made up of the "tblInvoiceDetails" and "qryproducts". I need
the extra qry because the parts that are entered on the invoice is on a
subform that calculates the cost of the item times quanity.
All I want is to have a report that shows me the total for all invoices that
day.
I hope I've explained this simply enough, if not I will try again.

 

[Guest]

It would be even better if I could get that "Total" back onto a table,
No, calculated values should not be stored.

Is there something in the records that distingushes labor records from
material records. If so, then use an IIF statement in the computation for
taxes.

IIF([YourFieldForMaterial] = "YES", [YourValueField] * tAXrATE,
[YourValueField])

Post your SQL statement. Open the query in design view, click on menu VIEW
- SQL View, highlight, copy, and paste in a post.

 

[Guest]

Unfortunately not. Other than the parts are sold under the subform and placed
in the "tblProducts" table under the specific Invoice number. That way in the
future we can look up invoices by date, customer, or vehicle when we're
looking for part warrenty information. On the invoice form in addition to the
labor charge we also have towing & inspections which are not taxed, plus
MiscShopSupplies which is taxed. I feel like the more information I give, the
more complicated it sounds. All in all it's just a simple invoice form where
we sell parts and charge labor to put the parts on.
All I hope to do is to get the totals off a report so I can enter that
information into a ledger without having to bring up each invoice and
manually add the totals.
It may be too much to ask for, I thought of redesigning the invoice form but
couldn't get around the need for a subform to be able to enter a list of
parts, and have the total for those parts appear on the invoice form.
Regardless, I appreciate the help. If you can come up with a simple
solution, I would greatly appreciate it. Like I said before, I have spent two
days on it and just ran out of ideas.......Thanks............gumby
--
gumby

No, calculated values should not be stored.

Is there something in the records that distingushes labor records from
material records. If so, then use an IIF statement in the computation for
taxes.

IIF([YourFieldForMaterial] = "YES", [YourValueField] * tAXrATE,
[YourValueField])

Post your SQL statement. Open the query in design view, click on menu VIEW
- SQL View, highlight, copy, and paste in a post.

If someone could help me with this it would be great.
It's probally something simple but after two days I need help. I'm running a
report for and invoice. Because of the difference in what is taxed and what
is not taxed I have calculated expressions on the invoice form to give me a
total. (I will have one item for "Labor" which is not taxed and possibly
several "Parts" that are taxed). Due to the calculations the invoice total is
not returned back to the table "tblInvoiceDetails". It would be even better
if I could get that "Total" back onto a table, but I believe that would
creates a circular calculation error. If that can be done somehow, please let
me know.
When I run a report, sorting for a particular date, I get all the invoices
for that date along with the individual charges. The problem arrises when I
try to total the Labor, it shows, and adds a labor charge for that Invoice
next to every part. When I do a SUM for the total labor charges for 3
invoices, I don't get the sum of the labor charges for the three invoices, I
get a total labor charge for labor times the number of parts I sold. If I
sold 20 items on the three invoices I get the sum of sixty labor charges.
It's easy to hide the duplicate values but it still adds them. Even if I
format and sort the data differently, I can get the Labor to show up only
once per invoice, but when I do the SUM it will still add it up for every
item sold.
The report is made up of the "tblInvoiceDetails" and "qryproducts". I need
the extra qry because the parts that are entered on the invoice is on a
subform that calculates the cost of the item times quanity.
All I want is to have a report that shows me the total for all invoices that
day.
I hope I've explained this simply enough, if not I will try again.

 

[OfficeDev18 via AccessMonster.com]

Hello, gumby,

I have found the following rule ultimately liveable:

Never report while processing; never process while reporting.

That simple rule has saved me months worth of grief. What it means in
practical terms is if your data needs massaging before appearing on your
report, do all processing within a form's VBA or a module, but BEFORE you
open the report. After all the processing is completed, open the report,
assigning the table (or query) as the report's Record Source. DON'T open the
report first to complete your calculations on an already-assigned Record
Source.

If this means you have to make a new temporary table to report on, so be it.
It's very easy to delete data or tables in Access after running reports.

Hope this helps,

Sam

Unfortunately not. Other than the parts are sold under the subform and placed
in the "tblProducts" table under the specific Invoice number. That way in the
future we can look up invoices by date, customer, or vehicle when we're
looking for part warrenty information. On the invoice form in addition to the
labor charge we also have towing & inspections which are not taxed, plus
MiscShopSupplies which is taxed. I feel like the more information I give, the
more complicated it sounds. All in all it's just a simple invoice form where
we sell parts and charge labor to put the parts on.
All I hope to do is to get the totals off a report so I can enter that
information into a ledger without having to bring up each invoice and
manually add the totals.
It may be too much to ask for, I thought of redesigning the invoice form but
couldn't get around the need for a subform to be able to enter a list of
parts, and have the total for those parts appear on the invoice form.
Regardless, I appreciate the help. If you can come up with a simple
solution, I would greatly appreciate it. Like I said before, I have spent two
days on it and just ran out of ideas.......Thanks............gumby

No, calculated values should not be stored.

[quoted text clipped - 36 lines]