Hi Al, The only way you can get round this is to use the Average, Minimum or Maximum summary function for the on hand column. Kind regards, David. -----Original Message----- From: mapics-l-bounces@xxxxxxxxxxxx [mailto:mapics-l-bounces@xxxxxxxxxxxx] On Behalf Of Al Gershen Sent: 04 August 2006 18:48 To: MAPICS ERP System Discussion Subject: Re: [MAPICS-L] Query/400: How do you removeduplicaterecordinformation on a Report? Hi Tom E. Stieger and David Unwin: Thank your both for your postings and to you, David, for your direct email to me with the attachment .doc file. I ran a Query/400 example using a Finished Goods Item Number that has more than one open Customer Order and has a current Quantity On Hand. I use the Break Level on the Item Number and the Quantity On Hand fields. The result were interesting: 1. In the detail about the Item number and the three (3) open Customer Orders, it only shows one Quantity On Hand reporting of 32 pieces. 2. In the Break Sub-Total for the Item Number and in the Final Report Totals, the Quantity On Hand Total is shown as 96 pieces. (This is computed as: 32 actual pieces on hand time 3 Open Customer Order records equals 96 pieces.) Is there a way to have the Break Sub-Total and Report Totals also show the Quantity On Hand at 32 pieces? Your additional comments would be appreciated. Regards, Al Gershen Cost Accountant Lead ECS Composites 3560 Rogue River Hwy Grants Pass, OR 97527 Tele (541) 476-8871, x272 Fax (541) 474-2479 aldg3@xxxxxxxxx --- "Tom E. Stieger" <TStieger@xxxxxxxxxx> wrote thru the Mapics ML on Thu, 3 Aug 2006 10:29:24 -0700:
Al- Here is what I think you are looking for: Quote/order Item number Original release Quantity number quantity On hand 9053 5191309 100.000 55.000 9053 100.000 9053 100.000 9054 5191310 250.000 383.000 9054 250.000 9054 200.000 9054 200.000 9058 5191314 100.000 110.000 9058 100.000 9058 100.000 (In our system we have pretty much customer specific item numbers, so we don't have lots of different orders per item number.) As you can see with the break level set to 1 on the Item number and quantity on hand it only shows that info once. While in query if you hit F5 to preview the results, it will not show the breaklevels, but they are there when you print the report. Here is part of the query definition: Selected files ID File Library Member Record Format T01 ITEMBL AMFLIBP *FIRST ITEMBLMC T02 MBADREP AMFLIBP *FIRST MBADREP1 T03 MBC6REP AMFLIBP *FIRST FAVRECM Join tests Type of join . . . . . . . . . . . . . Matched records Field Test Field T01.ITNBR EQ T02.ADAITX T02.ADCVNB EQ T03.C6CVNB Ordering of selected fields Field Sort Ascending/ Break Field Name Priority Descending Level Text T03.C6CANB Customer number T02.ADCVNB Quote/order number T01.ITNBR 1 Item number T02.ADAAN6 Original release quantity T01.MOHTQ 1 On hand total quantity Hope this helps. Let me know if you need anymore info. -Tom Stieger Estimator/Engineer CFW/Precision Metal Components 805-489-8750 tstieger@xxxxxxxxxx
--- "Unwin, David" <dunwin@xxxxxxxxxx> wrote thru the Mapics ML on Fri, 4 Aug 2006 09:20:45 +0100:
Hello Al, No problem, I'll send you an attachment (directly to your email address) with the screen and printed outputs for you to review. Kind regards, David.
--- "Unwin, David" <dunwin@xxxxxxxxxx> wrote a direct email to me with an attachment on Fri, 4 Aug 2006 09:23:28 +0100:
Hi Al, The attached file has the on screen and printed versions of a query joining ITEMBL, ITEMASA and MBCDREP. There are unique records for Item, Description and Quantity, and then the multiple records for Order No, Order Quantity and Due Date. Kind regards, David. Attachment: <<Query Output.doc>>
Regards, Al Gershen Cost Accountant Lead ECS Composites Grants Pass, OR aldg3@xxxxxxxxx __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com _______________________________________________ This is the MAPICS ERP System Discussion (MAPICS-L) mailing list To post a message email: MAPICS-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/mapics-l or email: MAPICS-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/mapics-l.
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.