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

[Al Gershen <aldg3@xxxxxxxxx>]

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