Confussed About Calculations

[Guest]

I have a query that determines return item price based on 1. the correct
price level for the customer and 2. the packing type returned (each, pack,
box, brick).

The query calculates the total of the return of each item.

My confussion lies in calculating the total for the group of items returned
in each box (which is individually bar coded). And then the total for the
overall return for th client.

Each client may return many boxes and each box may have many items.

When I try to sum my expression total for each item by the group (box it is
in) in my report it only calculates the last item listed. If i do it as sum
of all then I get the previous box total plus the sum of the last item in the
current box.

Report is as such:

Box: 495858

4549 Bubble Smoke- Blue 15 Each * $1.59 = $23.85
27 Pop Goes The Wesle 3 Pack * $7.99 = $23.97

Box: 495859

9794 Booby Call 4 Each * $59.95 = $239.80

Box 495860

10050 Firecrackers 11 Bricks * $16.95 = $186.45
2945 Snakes 255 Boxes * $ 0.75 = $191.25

I need to put a total for each box and then at the end of the report a grand
total of all boxes with a number of boxes so I can produce an average amount.

 

[Guest]

Hello again,

Glad to see that you decided to use the query method. Much easier than the
other way. Now to get the totals that you want, you should be able to use
unbound controls in the footer sections of your report.

If you put an unbound text box in the group footer section and make its
control source =Sum(NameOfYourCalculatedField) then it should give you a sum
for each group (which in this case is a box). Likewise, if you put one in the
report footer section, it should give an overall total for the entire report.

HTH

 

[Guest]

Thanks - i had to think out side the box to get the query right but it does
work much better - on my totals in my footers how can i make them show as a
currency format?

 

[John W. Vinson]

Thanks - i had to think out side the box to get the query right but it does
work much better - on my totals in my footers how can i make them show as a
currency format?

you'll hate this...

Set the Format property of the textbox to Currency.

John W. Vinson [MVP]

 

[Guest]

I knew that LOL - another question almost there - i have a count for the
number of boxes on the report - how can i take the total number of boxes and
average it with the grand total of returns to get an average per box - (its
something the accounts want.). Since the count isnt a permenant field and
the grand total is a sum of the all the group Box totals how can i accomplish
this?

 

[John W. Vinson]

I knew that LOL - another question almost there - i have a count for the
number of boxes on the report - how can i take the total number of boxes and
average it with the grand total of returns to get an average per box - (its
something the accounts want.). Since the count isnt a permenant field and
the grand total is a sum of the all the group Box totals how can i accomplish
this?

A totals query would be one way; another would be a textbox in the report
footer with a control source like

=Sum([returns]) / Count(*)


John W. Vinson [MVP]