David,
Last week someone suggested the solution of download to Excel and then use a pivot table. I concur. If you are using I-Series Access for windows, the addon for that function is probably already on your PC and may be active. (Alt D, R) to verify. I find that most users have good skills in Excel. If you give them the the results of a joined file extract as you describe, they can add column titles then sort or filter or group or subtotal or v-lookup to fit their specific needs. That way one general extract can be used to satisfy multiple requests. If you are unfamiliar with pivot table & subtotal & filter functions, I am including a very simple example below. Just substitute Qty and Planner with your $ales and Salesman, it is the same principle.
RAW DATA
Item
Qty
Planner
Warehouse
MPROD
QTY
ICPLNC
LWHS
AT403
5582
R8
C3
AT403
-300
R8
C5
AD008
0
R8
CM
AD008
105
R8
C3
AD008
4
R8
C5
AD009
0
R5
C5
AD012
762
M6
C3
AD012
69
M6
C5
AD014
2
M6
C3
AD014
1
M6
C5
AN907
0
R8
CM
AN907
50
R8
C3
AN907
58
R8
C5
PIVOT BY WHS (Alt D , P)
Sum of QTY
LWHS
MPROD
C3
C5
CM
Grand Total
AD008
105
4
0
109
AD009
0
0
AD012
762
69
831
AD014
2
1
3
AN907
50
58
0
108
AT403
5582
-300
5282
Grand Total
6501
-168
0
6333
Or by planner
Sum of QTY
ICPLNC
MPROD
M6
R5
R8
Grand Total
AD008
109
109
AD009
0
0
AD012
831
831
AD014
3
3
AN907
108
108
AT403
5282
5282
Grand Total
834
0
5499
6333
SUBTOTAL BY QTY (Alt D , B)
Item
Qty
Planner
Warehouse
MPROD
QTY
ICPLNC
LWHS
AT403Total
5282
AD008 Total
109
AD009 Total
0
AD012 Total
831
AD014 Total
3
AN907 Total
108
Grand Total
6333
FILTER only R8 & C3 (Alt D, F, F)
Item
Qty
Planner
Warehouse
MPROD
QTY
ICPLNC
LWHS
AT403
5582
R8
C3
AD008
105
R8
C3
AN907
50
R8
C3
Questions > offline
Larry Costain
-----Original Message-----
From: David.ANTONISSE@xxxxxxxxxxxxxxxx
To: BPCS ERP System <bpcs-l@xxxxxxxxxxxx>
Sent: Mon, May 10, 2010 1:01 am
Subject: Re: [BPCS-L] Question about queries
Dear consultants,
the 'problem' is as followed:
I use the following files:
IL
SM
CM
IM
2. Matched records with primary file
LSAL1 EQ SSAL
LCUSB EQ CCUST
LPROD EQ IPROD
Select records:
ILPROD LIKE 'F%'
ND ILDATE GE 20100501
ND ILWHS LIKE 'Q%'
ND SNAME LIKE '%F%'
As output I want to see these fields but than summarized on quantity per
tem, while I have more lines for the same item.
0 T01.ILQTY Quantity
0 T02.SSAL Salesman Number
0 T02.SNAME Salesman Name
0 T01.ILCUSB Billing Customer
0 T03.CNME Customer Name
0 T01.ILPROD Item
0 PIECE ilrev/ilqty
0 T04.IDESC Item Description
0 T04.IDSCE Extra Description
The output I want to use in another query.
Thank you very much for your help already!
Rgds,
avid AntonissePlease consider the environment before printing this e-mail.
--------------------------------------------------------------------------------------------------------------------
iscover Impress on
http://www.impressgroup.com The information contained in
his e-mail and any attachments is strictly confidential and may be legally
rivileged.It is intended solely for the use of the individual or entity to whom
t is addressed. If you are not the named addressee, you are hereby notified
hat it is prohibited and may be unlawful to disclose, distribute, store, copy
he information in any medium or usage for another purpose. ---------------------------------------------------------------------------------------------------------------------
midrange.com
