If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sub Query?
I have a select query that adds up monthly sales for products both by case
and single items for a commission report. An example would be: Widget A Single Qty Single Cost Total Case Qty Case Cost Total Grand Total 1 .99 .99 2 5 10 10.99 Widget B 1 .50 .50 1 5 5 5.50 16.49 I need to total the monthly sales at the bottom of the report, but right now I get the whole total for both products. IE: Widget A $16.49 Widget B $16.49 which gives me a total of $32.98. The more products on the invoice, the more duplicate totals and bigger grand total at the bottom. I need to have it add up the total for the entire invoice once per invoice number in the report footer. I thought about using a sub query, but I can't figure out how to do it. Anyone have any suggestions? Thanks Jim |
#2
|
|||
|
|||
Sub Query?
Are you grouping on Widget field and summing in the group footer?
-- Build a little, test a little. "Jim" wrote: I have a select query that adds up monthly sales for products both by case and single items for a commission report. An example would be: Widget A Single Qty Single Cost Total Case Qty Case Cost Total Grand Total 1 .99 .99 2 5 10 10.99 Widget B 1 .50 .50 1 5 5 5.50 16.49 I need to total the monthly sales at the bottom of the report, but right now I get the whole total for both products. IE: Widget A $16.49 Widget B $16.49 which gives me a total of $32.98. The more products on the invoice, the more duplicate totals and bigger grand total at the bottom. I need to have it add up the total for the entire invoice once per invoice number in the report footer. I thought about using a sub query, but I can't figure out how to do it. Anyone have any suggestions? Thanks Jim . |
#3
|
|||
|
|||
Sub Query?
Yes I'm grouping on the widget field, but I don't have the group footer
turned on. I was trying to total it in the report footer. "KARL DEWEY" wrote in message ... Are you grouping on Widget field and summing in the group footer? -- Build a little, test a little. "Jim" wrote: I have a select query that adds up monthly sales for products both by case and single items for a commission report. An example would be: Widget A Single Qty Single Cost Total Case Qty Case Cost Total Grand Total 1 .99 .99 2 5 10 10.99 Widget B 1 .50 .50 1 5 5 5.50 16.49 I need to total the monthly sales at the bottom of the report, but right now I get the whole total for both products. IE: Widget A $16.49 Widget B $16.49 which gives me a total of $32.98. The more products on the invoice, the more duplicate totals and bigger grand total at the bottom. I need to have it add up the total for the entire invoice once per invoice number in the report footer. I thought about using a sub query, but I can't figure out how to do it. Anyone have any suggestions? Thanks Jim . |
Thread Tools | |
Display Modes | |
|
|