Print Page | Close Window

Duplicate recordes

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=4017
Printed Date: 02 May 2024 at 3:06am


Topic: Duplicate recordes
Posted By: mavrik
Subject: Duplicate recordes
Date Posted: 18 Aug 2008 at 10:25am

Hi,

I have a table in my report that has date column and exchange rate column.  I have other tables in the report that shows products quantities and pricing to be used in batches.  My problem is if I have more then one record in the exchange table, the products duplicates in the report according to the number of entries in the exchange table.  So my report only works correctly if I have 1 entry in the exchange table.

Thanks


Replies:
Posted By: rahulwalawalkar
Date Posted: 18 Aug 2008 at 10:35am
Hi,
 
You need to check the Join i.e. how products and exchnage table is joined ,use Select Distinct ID  in your sql query and see does it make any difference.
 
Or you can post sample data from both the tables
Cheers
Rahul

Posted By: mavrik
Date Posted: 18 Aug 2008 at 10:44am
Sorry, I forgot to mension that the products and exchange table are not joined as there is no common data in the exchange table that I can join with any other table
Thanks

Posted By: mavrik
Date Posted: 18 Aug 2008 at 1:34pm
To update my question, I am only using the data from the exchange table in a formula.  Can I pull 1 record from a table just as a refrence, and use that refrence in a header, and use it later in a formula?

Posted By: rahulwalawalkar
Date Posted: 18 Aug 2008 at 1:43pm
Hi,
 
Can you explain with some sample data .... as what you want in the report.
 
Cheers
Rahul

Posted By: mavrik
Date Posted: 18 Aug 2008 at 3:05pm
My exchange table is as follows.  I need to pull for the report the last date and the last exchange rate;
Curr_Id   Curr_date    BuyRate
USD        8/12/2008  1.045000
USD        8/13/2008  1.054700
USD        8/15/2008  1.060000
 
and an example of the report is
Line Item Key Quantity Unit Price/KG Currency % in formula Cost per Item  
1 S-6257 0.5800 LTS $1.35 CND 1.00 $0.78
2 R-5898 5.2164 KGS $5.37 CND 10.36 $28.01
4 A-1478 0.1361 KGS $6.18 US 0.27 $0.89
6 A-5874 0.1361 KGS $11.13 CND 0.27 $1.51

Posted By: rahulwalawalkar
Date Posted: 19 Aug 2008 at 12:53am
Hi
 
Create a command object
 
and write the code below
 
SELECT
CURR_ID,
CURR_DATE,
BUYRATE
FROM
EXCHANGE
WHERE CURR_DATE =
(SELECT MAX(CURR_DATE)
FROM EXCHANGE)
GROUP BY CURR_DATE,CURR_ID,BUYRATE
 
this will pick up the  last record from the exchange table if you have multiple exchange records.
 
Cheers
Rahul
 

Posted By: mavrik
Date Posted: 19 Aug 2008 at 9:50am
Thank you very much Rahul, but I still have the same problem, now I have 2 entries in my exchange table, and my report posts each item twice.  Please see example below;
Line Item Key Quantity Unit Price/KG Currency % in formula Cost per Item                          
1 S-6255 0.5800 LTS $1.35 CND 1.00 $0.78                    
1 S-6255 0.5800 LTS $1.35 CND 1.00 $0.78                    
2 R-5325 5.2164 KGS $5.37 CND 10.36 $28.01                      
2 R-5325 5.2164 KGS $5.37 CND 10.36 $28.01                      
4 A-1391 0.1361 KGS $6.18 US 0.27 $0.89                        
4 A-1391 0.1361 KGS $6.18 US 0.27 $0.89                        

Posted By: mavrik
Date Posted: 19 Aug 2008 at 12:21pm
Sorry Rahul, my mistake, its working great, thanks for your help, I appreciate it


Print Page | Close Window