| Author |
Message |
mavrik
Newbie
Joined: 16 Aug 2008
Location: Canada
Online Status: Offline
Posts: 9
|
   Topic: Duplicate recordes
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
|
 IP Logged |
|
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
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
|
|
IP Logged |
|
mavrik
Newbie
Joined: 16 Aug 2008
Location: Canada
Online Status: Offline
Posts: 9
|
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
|
|
IP Logged |
|
mavrik
Newbie
Joined: 16 Aug 2008
Location: Canada
Online Status: Offline
Posts: 9
|
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?
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
Posted: 18 Aug 2008 at 1:43pm |
Hi,
Can you explain with some sample data .... as what you want in the report.
Cheers
Rahul
|
|
IP Logged |
|
mavrik
Newbie
Joined: 16 Aug 2008
Location: Canada
Online Status: Offline
Posts: 9
|
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 |
|
|
|
IP Logged |
|
rahulwalawalkar
Senior Member
Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
|
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
Edited by rahulwalawalkar - 19 Aug 2008 at 12:57am
|
|
IP Logged |
|
mavrik
Newbie
Joined: 16 Aug 2008
Location: Canada
Online Status: Offline
Posts: 9
|
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Edited by mavrik - 19 Aug 2008 at 10:16am
|
|
IP Logged |
|
mavrik
Newbie
Joined: 16 Aug 2008
Location: Canada
Online Status: Offline
Posts: 9
|
Posted: 19 Aug 2008 at 12:21pm |
|
Sorry Rahul, my mistake, its working great, thanks for your help, I appreciate it
|
|
IP Logged |
|
|
|
