subtotal and average
Can anyone tell me how/if I could using query400 take a list of records, subtotal and create a file with only the subtotals for export to excel? My file is all orders, with line items and $$ for each line. I need to subtotal the orders by order # and create a file listing only the customer #, order# and the order total for export to excel. Is there a way/how I can create a result field to hold the subtotal amount? I can sum the line item $$ but it puts it in its own row and breaks. My fields/columns are (A)Cust# (all the same customer #), (B)Order#, (C)part#, (D) line $$. Orders have a variety of line items. I have just over 5000 records. I want one file with (A)Customer#, (B)Order#, (C)Order total. Any tips or hints how I can accomplish this? Can it be done with query? Thanks, I appreciate any help.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 12, 2009 3:48 PM
UPDATED:
March 12, 2009 4:54 PM
Answer Wiki:
Hi,
You'll need to do something like the following :-
In report summary functions - specify total for your $$.
In report breaks - specify all the fields that you need in your file (including order#) at the same level (eg 1).
In output type and form - specify database file and summary only.
When you run your query it should create your file with one record per order# with an order total and a "break level" (what you specified in report breaks). It will also have a record with a "break level" of 0 which is the total for the complete query.
In your excel, you should be able to remove the break level 0 record (or your could use UPDDTA or STRSQL to remove it).
You can use CPYTOIMPF to convert the resulting file to .CSV for import into excel.
Regards,
Martin Gilbert.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
