Crystal Reports Duplicate result due to one to many relationship

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Duplicate result due to one to many relationship

Author	Message
phimtau123 Newbie Joined: 10 Mar 2009 Online Status: Offline Posts: 22	Topic: Duplicate result due to one to many relationship Posted: 10 Mar 2009 at 3:54pm
	Hello all, I'm using Crystal Report 11 and is having a trouble with duplication record within report. Table used: Computer (primary table) Hard-drive (join to Computer by compid) Extension-Card (join to Computer by compid) Output CompID cardName PhysicalDrivename 1354 Pentium 4 PhysicalDrive0 1354 Pentium 4 PhysicalDrive1 1354 Dimm 1 PhysicalDrive0 1354 dimm 1 PhysicalDrive1 1354 dimm 2 PhysicalDrive0 1354 dimm 2 PhysicalDrive1 Desire Output CompID cardName PhysicalDrivename 1354 Pentium 4 PhysicalDrive0 Dimm 1 PhysicalDrive1 dimm 2 How can I fix this? I tried using group but it wouldnt work
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 10 Mar 2009 at 4:10pm
	Computer (primary table) Hard-drive (join to Computer by compid) Extension-Card (join to Computer by compid) Looks like you need to join the Computer to the Hrd drive on the comp id and the HArd drive to the Extension card on the Compid and the physicaldriver name. From there your grouping on should make it appear as you want.
	IP Logged

phimtau123 Newbie Joined: 10 Mar 2009 Online Status: Offline Posts: 22	Posted: 10 Mar 2009 at 4:32pm
	Can you clarify? I have update the link from as you have stated but could find the right grouping to get what i want
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 10 Mar 2009 at 5:20pm
	SUre, just keep in mind I am guessing at this since I can't see your data and table set up so you may need to tweak the ideas to make it work exactly for you. My joins may have been off but it is an idea... First if there are instances in where there is data in the computer table that are in the one or either of the other tables you will need to do left joins to include those records if you want them to show up. Also your example data does not quite gel from the first to the second so I am having trouble understanding it... I think if the joins are doing what you need them to you can just group on the computer.compid and palce the rest of your data in the details and is should show as you wanted. if not try: group1 on the computer.computerid Group2 on the CArd Name If there is only one physicaldrive per card name then place the physical drive on the header row otherwise try the detail row. If this does not work can you post the data as it is coming out with the changed joins? Edited by DBlank - 10 Mar 2009 at 5:21pm
	IP Logged

phimtau123 Newbie Joined: 10 Mar 2009 Online Status: Offline Posts: 22	Posted: 10 Mar 2009 at 10:13pm
	Well first off how do you specify a left join in crystal report? I'm using CRXI and it do the join for me automatically from the wizard. hehe Also info above are not data it the report output that I'm getting when placing those three fields on crystal report (I'm a newbie) hehe. CompID are from computer table Cardname are from extensioncard table PhysicalDrivename are from Hard-Drive table The two table are LINK to the computer table through compid with a many to one relationship. I hope that clear up the confusion. I did a group on computer.compid and use the group header and that solve that column duplication but I'm still getting some duplication from the other two column. The output look like this now: CompID cardName PhysicalDrivename 1354 Pentium 4 PhysicalDrive0 Pentium 4 PhysicalDrive1 Dimm 1 PhysicalDrive0 dimm 1 PhysicalDrive1 dimm 2 PhysicalDrive0 dimm 2 PhysicalDrive1 doing an additional group on either one of the other two column result in undesire output. Is there any other way I can fix this? The desire are output for the report are this: Desire Output CompID cardName PhysicalDrivename 1354 Pentium 4 PhysicalDrive0 Dimm 1 PhysicalDrive1 dimm 2 Thank you :)
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 11 Mar 2009 at 7:16am
	To manage Joins go to Database Expert and click on the Links tab. You can delete or add links here by dragging and dropping the field from one table to another. To change the join type double click on a link and change the Join type there. TO get the output you want may be tricky and without seeing your exact data set up not sure how far I can help you. What you need to think about is how the links in the table are going to effect the number of rows you get and is there a better link to use. I assume based on your desired output that the Physical Drive is directly and singularly related to the card name, is there a secondary value in the Hard Drive and Extension Card tables that you can join on other than the compid or is there a double join using both the compID and another field like I suggested before? My guess is there is another fiele like driveid that is in both table. IF that is the case use it to do the join and you may clear it all up from that. Hope this helps.
	IP Logged

phimtau123 Newbie Joined: 10 Mar 2009 Online Status: Offline Posts: 22	Posted: 13 Mar 2009 at 3:37pm
	I tried using the left join and look for other field to link the Physical Drive and the ExtensionCard table but Compid is the only related field these two table have. I've tried grouping on several field but nothing seem to work. Any other idea? Thank again :)
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 13 Mar 2009 at 4:07pm
	Hi Phimtau, Sorry these suggestions are not working out for you Couple of things I'll need to better understand and maybe be able to help (if not me maybe someone else will see this and come up with an idea). Can you post a little sample data from each table with all the available columns in each table as well as explain how these should relate regardless of the report or how you want it to appear int he report. For example you have 3 tables: Computer has the computer id: there is only one record in the computer table per computer... Hard-drive table has the physical drives on it and can have up to 2 physical drives per computer and only one physical drive should be related to one card name only OR is it not related to the card name at all or some other way etc. for last table I think I may be trying to make some relationships that may not be there based on how you wanted your report to look. This will really help. Thanks. Edited by DBlank - 13 Mar 2009 at 4:09pm
	IP Logged

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