Re: Query/400: How do you removeduplicaterecordinformation on a Report?

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.