[christian Mukeba]

Hi i have a CR that i am creating but i getting duplicate items can anyone help me to reomve them in my report.
the reports values comes from different tables which are linked and i am getting about 47 records while i only have 2 records that am expecting.
I did check the supress if duplicate chkbox but still i can't get it right and my other option was to check the select distinct record chkBox but it is disable for the reason that i have no idea and i was reading an article in a CR forum,they say i must create a formula to supress the duplicate items and what i did was i right click to the values that i want to supress click on the button next to it and put this formula

previous({Sales.SalesPerson})={Sales.SalesPerson}

but still i can't get it right,can anyone help me?

[_HAWK_]

It has a lot to do with the type of join. You can also make a group for the field and suppress the details.

[christian Mukeba]

Thanks for your reply HAWK that's exactly what i was thinking might be the problem but i don't know how to solve it can you please tell me what i am doing wrong?
here's my joining

Attached image(s)

• 

[_HAWK_]

I would say read up joins and see which one fits your needs. When you right click on the links you can change the join there. Right Inner might be the one your after.

[christian Mukeba]

I found something but its still not working as i want.
I selected a field and added a group for that field then i put all my other field into the group header section and then i supress the details section and the duplicate items are gone but now the problem is all the items that are coming from different tables are just showing the same while they not suppose to be like that!!

I really believe the problem reside on the joining type but i spend last night reading about that and tryng but i still can't get it right
Any help please!!!!

Attached File(s)

•  Tender.pdf (89.93K)
  Number of downloads: 9

[_HAWK_]

Did you try diff joins? Can you show what it should look like and what it does look like? Which is the pdf in your link(the right look)?

[christian Mukeba]

No the pdf tender is the wrong one.
see pdf tender1 for the right way its suppose to be and pdf tender for what i am getting now
yes i tried other joins but with no luck
the Sales Person, company name, contact name and likelihood are the items coming from differents tables and are giving me problems. See CR Linking Tables for the way i linked them

Attached image(s)

• 

Attached File(s)

•  Tender.pdf (89.93K)
  Number of downloads: 5
•  Tender1.pdf (55.96K)
  Number of downloads: 7

This post has been edited by christian Mukeba: 27 November 2012 - 11:01 PM

[_HAWK_]

I assume you have checked the database itself to see if it has those correct values. You are showing the description field for those related tables on the report, right? Make sure you have all the right fields on the report as well. Not knowing your DB structure, wouldn't you want the customerId from the Tender table, instead of the Contact table for those related values?

This post has been edited by _HAWK_: 28 November 2012 - 08:53 AM

[christian Mukeba]

YES my DB got all the values that i need and as i need them, and the fields on the report am pretty sure they are right bcoz i took them straight from the database fields in solution explorer and drop them in my report
The reason why there is relationship between customers and contact is bcoz one customer can have many contact.
I tried to put a relationship between customer and tender but no luck nothing is changing.
I am realy confuse right now and what i realy don't understand is why its only showing the same sales person, likelihood,contact name and company while in my DB they ain't the same. is there anything that you might suspect is causing that???

[_HAWK_]

Ok, but the salesPerson appears to be separate/not related to the contact or customer tables.

[christian Mukeba]

yes the sales person and likelikood are not link to the customers or contact tables..or maybe there is something wrong with my sql query to retrieve infos
please tell me is am wrong here:

 Dim cmd As New SqlClient.SqlCommand("Select Contacts.Name,Customers.CompanyName,Likelihood.LikelihoodLevel,sales.SalesPerson, Tender.ETNumber, Tender.Enquirydate,Tender.Projectname,Tender.UnitType,Tender.TenderValue,Tender.ReqDate,Tender.NumberUnits FROM Contacts INNER JOIN Customers ON Contacts.CustomerID=Customers.CustomerID INNER JOIN tender ON Customers.CustomerID=Tender.CustomerID INNER JOIN Likelihood ON Tender.LikelihoodID=Likelihood.LikelihoodID INNER JOIN Sales ON Tender.SalesID=Sales.SalesID")

This post has been edited by christian Mukeba: 28 November 2012 - 11:47 PM