2 tables 1 report issue

[mmars]

I have created a report in CR XI that uses 2 tables from an access database. 2 fields are from one table and the 3rd field is from the other. When I preview the report in CR, it gives me the correct results.

User1

Site1
Site2
Site3

When I call the report in VB.NET 2003 it duplicates the results.

User1

Site1
Site1
Site1
Site2
Site2
Site2
Site3
Site3
Site3

Here is the code that I am calling the report in VB.NET.

strsql = "SELECT tblUser.UserName, tblUser.Administrator, Security_Access.Site_ID FROM tblUser INNER JOIN Security_Access ON tblUser.UserName = Security_Access.UserName"
reportname = apppath & "\rpt_users.rpt"


Dim con As New OleDb.OleDbConnection

'Create a connection to specified database
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & inpdatabase & ";")
'Open connection
con.Open()
da = New OleDb.OleDbDataAdapter(strsql, con)
da.Fill(ds, "User")

con.Close()

rd = New ReportDocument
rd.Load(reportname)
rd.SetDataSource(ds)

crvMain.ReportSource = rd

Thanks
mmars

[ganeshmoorthy]

strsql = "SELECT tblUser.UserName, tblUser.Administrator, Security_Access.Site_ID FROM tblUser INNER JOIN Security_Access ON tblUser.UserName = Security_Access.UserName"

strsql = "SELECT Distinct tblUser.UserName, tblUser.Administrator, Security_Access.Site_ID FROM tblUser INNER JOIN Security_Access ON tblUser.UserName = Security_Access.UserName"

[mmars]

Thanks for your reply but DISTINCT and DISTINCTROW did not work. I'm still getting the duplicate records.

mmars

[Tribo]

I don't know what and how is the information stored in the two tables. Did you try to copy the query into Query Analyzer?... Maybe you must modify the JOIN... (Perhaps a LEFT or RIGHT?)

Regards,
Tribo