Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: pesky duplicate records Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Getch
Groupie
Groupie


Joined: 26 Dec 2008
Online Status: Offline
Posts: 47
Quote Getch Replybullet Topic: pesky duplicate records
     Posted: 18 Mar 2011 at 9:11am

This problem I see alot and still have a hard time getting around.

I have a relatively simple report but it needs date from 10 database tables.
 
I had it working correctly for nearly all scenarios but I am now attempting to make it work for all.
 
There are about 6 clients that have a subset of portfolios to pull into the report instead of the normal 11 portfolios.
 
There is another db that contains portfolio names that I can link only on a single field. I was trying to add a shared variable so I could count the portfiolios then apply a formula to display simple text based on the count of 1, 7 or 11.
 
When I add the portfolio name field to my subreport, the fund data is duplicated by the number of portfolios the client has.
 
My report is set to select distinct records. I'm not sure how to handle these dups.
 
Any help would be appreciated!
 
Newbie reports
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 18 Mar 2011 at 9:35am
Instead of pulling in the whole table, you could try writing a command that would get your count.  A command is just a SQL select statement.  It would look something like this:
 
Select client, count(*) as porfolio_count
from portfolio
group by client
 
You then link that to the appropriate table in the rest of your data based on client.  This will give you a single record  per client that contains the count you're looking for without causing duplicate records.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Getch
Groupie
Groupie


Joined: 26 Dec 2008
Online Status: Offline
Posts: 47
Quote Getch Replybullet Posted: 22 Mar 2011 at 4:41am
Thanks Hilfy,
 
I think I'm clear on your suggestion. I am far from a power user so I'll give that a go and post back if I run into trouble.
Newbie reports
IP IP Logged
Getch
Groupie
Groupie


Joined: 26 Dec 2008
Online Status: Offline
Posts: 47
Quote Getch Replybullet Posted: 22 Mar 2011 at 5:52am
Getting sytax errors at the moment...this is what I started with.
 
FYI...I've never written an SQL command so this is number one.
 
Select
Portfolio.'porf_idi' , count(*) as portfolio_count
From
Portfolio INNER JOIN PLA
pla.'pla_idi' = portfolio.'pla_idi'
GROUP BY
pla.'pla_idi'
 
 
Newbie reports
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 22 Mar 2011 at 7:40am
FROM portfolio
INNER JOIN pla
ON pla.'pla_idi' = portfolio.'pla_idi'



Edited by Keikoku - 22 Mar 2011 at 7:40am
IP IP Logged
Getch
Groupie
Groupie


Joined: 26 Dec 2008
Online Status: Offline
Posts: 47
Quote Getch Replybullet Posted: 22 Mar 2011 at 9:43am
still not there but closer I think. I checked the SQL in the report and then found I had not used the correct full path syntax. This is what I've got now but I'm getting
"The multipart indentifier "BNTRKDB"."dbo"."PORTFOLIO"."pla_idi" could not be bound."
 
Here is the SQL now:
 
Select
"PORTFOLIO"."porf_idi" , count(*) as portfolio_count
From "BNTRKDB"."dbo"."Portfolio" "PORTFOLIO"
INNER JOIN "BNTRKDB"."dbo"."PLA"
ON "BNTRKDB"."dbo"."PLA"."pla_idi" = "BNTRKDB"."dbo"."PORTFOLIO"."pla_idi"
GROUP BY
"BNTRKDB"."dbo"."PLA"."pla_idi"
Newbie reports
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 23 Mar 2011 at 2:48am
Are you able to at the very least run a select * from both tables separately? Then you can try joining them afterwards.
IP IP Logged
Getch
Groupie
Groupie


Joined: 26 Dec 2008
Online Status: Offline
Posts: 47
Quote Getch Replybullet Posted: 23 Mar 2011 at 4:29am
Keikoku,
 
Unsure how I would do that. This is my very first attempt at writing a SQL command. All of my record selection prior to this has been via the selection expert.
 
If you are asking if I can access the data in each db separately then yes I can. I can browse the db's without a problem.
Newbie reports
IP IP Logged
Keikoku
Senior Member
Senior Member


Joined: 01 Dec 2010
Online Status: Offline
Posts: 386
Quote Keikoku Replybullet Posted: 23 Mar 2011 at 4:45am
When I first started SQL and someone asked me to join the tables, I basically did this:

step 1: select * from tableA
browse through the table looking for the columns I want

step 2: select * from tableB
browse through the table looking for the columns I want

step 3: select * from tableA join tableB on .....

The join should be complete at this point and it should work.

Then I just copied the SQL to crystal knowing that it works when I'm browsing the DB.

Group by then comes last since it is trickier...

Edited by Keikoku - 23 Mar 2011 at 4:46am
IP IP Logged
Getch
Groupie
Groupie


Joined: 26 Dec 2008
Online Status: Offline
Posts: 47
Quote Getch Replybullet Posted: 23 Mar 2011 at 5:37am
Ahhhh!
 
Good tip! Thanks. I'll mess around with that and experiment!
Newbie reports
IP IP Logged
Page  of 2 Next >>
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.