Formula Help On A Report Footer

[JCMcAbee]

This is probably too much information, but I wanted to be as clear as I could.

Let's say I have a company that delivers widgets for a local widget maker, and these widgets are very bulky.

My agreement with the widget maker is that I will deliver UP TO 60 widgets to a location at the normal rate. However, if any single location exceeds 60, the widget maker incurs an additional delivery charge.

Now let's say 20 of the widget makers 100 customers have ordered more than 60 widgets each. Some may have ordered only 65 while others may have ordered 120.

I record in the database the recipients information, including the number of widgets to be delivered. So now, on this particular day I have manually counted 20 locations that will receive more than the allotted 60.

How can I get the report to tell me following:
1. The number of recipients that exceeded 60 (in this case 20).
2. The sum of widgets delivered to those 20 locations and how many deliveries to charge the widget maker for. (I already have the sum of widgets for the entire day)

Example: 2000 widgets were delivered to 20 locations that exceed the 60 limit, and the widget maker is to be charged an 'additional delivery fee' of 33.33333 (2000 / 60)

Thanks for any help provided on this.

 

[dhookom]

1. The number of recipients that exceeded 60 (in this case 20).
=Sum(Abs([WidgetCount]>60))

2a. The sum of widgets delivered to those 20 locations
=Sum(Abs([WidgetCount]>60) * [WidgetCount])

2b. and how many deliveries to charge the widget maker for.
Isn't this that same as question 1?



Duane
Hook'D on Access
MS Access MVP

 

[JCMcAbee]

Duane - Thanks very much for the help, that is exactly what I needed. I struggled with this one. Yes, I guess, in a way 2b was the same question as #1.

Thanks again.