Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sub-Report or another technique?

Status
Not open for further replies.

EdAROC

Programmer
Aug 27, 2002
93
US
I have to create a report that shows open orders, the maximum that can be shipped (there's an over and under allowance), the value if we ship the max and the shipping releases for the orders (there can be multiple releases for an order).

Column Definitions (with examples):
Ord# - Order#
Qty - Quantity Ordere
UP - Unit Price
Ovr% - Allowed Qty to overship, as a percentage of ordered
Max - max we can ship
Value - Sales value of the max
Date - Planned shipping date
QtyShp - Qty to ship (as ordered, do not add overage allowance)
---------------------------------------------
[ABC] <--- Grouping per customer
Ord# Qty UP Ovr% Max Value Date QtyShp
1234 100 1.00 5% 105 105 08/20 60
09/01 40 <--2nd Rls of above order
1244 200 .50 2% 204 102 09/01 200

Customer ABC Total ... 207
----------------------------------------------
Value is totalled for each customer (grouping).

The significant tables are ORDERS and SHIP_RLS. I built a query to capture the information. Of course, for each release there is a row (which is why the Value gets messed up). I tried something I knew wouldn't work, but, had to do try, right? - I selected "Yes" to Hide Duplicates for the Value column. Of course, the Total comes to 312.

Is this a situation for a sub-report? If so, how. If not, what's the technique?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top