summarizing unique records -- frustrated!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Company: Silly Widgets
No. Orders: ** how do I get this to reflect a total on only unique order numbers?

Division: East
Orders from Division: **how do I get this to reflect 5 orders from this division instead of 7 orders?

Store: First Street
Order number: Item:
101 Thingummy
102 Widget
Total First Street orders: 2

Store: Last Street
Order number: Item:
103 Thingummy
104 Thingummy
104 Widget
105 Thingummy
105 Widget
Total Last Street orders: 3 (this is calculated setting orderid =1 and calculating running sum over all)

There are two orders taken by the Last Street store that sold multiple items per order number. At the Store grouping level, I can calculate the unique orders. How do I get this to carry over to the division and then the company overall level? I have been VERY creative and can't seem to figure this out. Can someone help?

Thank you!

PS -- The VPs are getting VERY upset that this report is not yet automated -- so thanks to any and all! :)
 
The way I would approach it is to group the report by
order number, place the detail in the detail band and
place the count etc in the group band.
Give it a a try. Hope this gepts the VIP off your back.
Fons
-----Original Message-----

Company: Silly Widgets
No. Orders: ** how do I get this to reflect a total on only unique order numbers?

Division: East
Orders from Division: **how do I get this to reflect
Click to expand...
5 orders from this division instead of 7 orders?
Store: First Street
Order number: Item:
101 Thingummy
102 Widget
Total First Street orders: 2

Store: Last Street
Order number: Item:
103 Thingummy
104 Thingummy
104 Widget
105 Thingummy
105 Widget
Total Last Street orders: 3 (this is calculated
Click to expand...
setting orderid =1 and calculating running sum over all)
There are two orders taken by the Last Street store that
Click to expand...
sold multiple items per order number. At the Store
grouping level, I can calculate the unique orders. How do
I get this to carry over to the division and then the
company overall level? I have been VERY creative and can't
seem to figure this out. Can someone help?
Thank you!

PS -- The VPs are getting VERY upset that this report is
Click to expand...
not yet automated -- so thanks to any and all! :)
 
Thank you!

That is exactly the way I have it set up currently. It works great for the detail and the first group (the "store" level where the running sum is located.) But at the next group (the "division" level) I can't seem to get an accurate count. In this example, the order numbers would be displayed as 7 because each instance of an order number would be counted.

Is there a way to sum on the results returned at the store level? Every time I try, I get an error message.

Thanks! I am trying every and all ideas that I can find! :)

PS -- I'm currently hand generating this and amending the results so they are correct. This won't last long! I'm afraid that I'm going to need to create the groupings and perform the calculations in the data source rather than in the report. Which is a bummer, because I'm sorting the same data several different ways based on user input and each of these sorts would then require a different data source.
 
Mal.
Can you provide more information as to exactly what you
have so far. If needed send me a copy of your mdb with
some sample data, I'll gladly look at it and see what
could be done.
The approach I would take is grouping by store and a
second group by division, each with a group footer. Than
you can summarize in each footer band.
f o n s p o n s i o @ s b c g l o b a l . n e t
Fons
-----Original Message-----
Thank you!

That is exactly the way I have it set up currently. It
Click to expand...
works great for the detail and the first group
(the "store" level where the running sum is located.) But
at the next group (the "division" level) I can't seem to
get an accurate count. In this example, the order numbers
would be displayed as 7 because each instance of an order
number would be counted.
Is there a way to sum on the results returned at the
Click to expand...
store level? Every time I try, I get an error message.
Thanks! I am trying every and all ideas that I can find! :)

PS -- I'm currently hand generating this and amending the
Click to expand...
results so they are correct. This won't last long! I'm
afraid that I'm going to need to create the groupings and
perform the calculations in the data source rather than in
the report. Which is a bummer, because I'm sorting the
same data several different ways based on user input and
each of these sorts would then require a different data
source.
 
Back
Top