Results 1 to 3 of 3

Thread: [RESOLVED] cannot distinct with inner join why?

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Resolved [RESOLVED] cannot distinct with inner join why?

    hey i have a query code that uses a inner join
    some how no matter where i put the Distinct it still shows me the same name of the customer twice or even more.
    is there a way to show the customer name only once?
    this is my code.
    Code:
        SqlStr = "SELECT Distinct TempHist.HistCust, TempHist.HistDate," & _
         " TempHist.HistItem, TempHist.EmployeeName, Customers.ArivedDate, Customers.Cellular" & _
         " FROM Customers INNER JOIN TempHist ON Customers.FullName = TempHist.HistCust" & _
         " WHERE HistCust Not In (Select Distinct HistCust" & _
         " From TempHist" & _
         " WHERE HistDate Between #" & MyDate(DteCheck) & "# And #" & MyDate(Now) & "#)" & _
         " Order by HistDate Desc"
    tnx for the help
    salsa

  2. #2
    gibra
    Guest

    Re: cannot distinct with inner join why?

    You shouldn't join the tables on
    Customers.FullName = TempHist.HistCust
    since there may be more customers with the same name, and this would double the rows of those records.
    You should use the primary key/foreign key that 'should' relate the two tables.
    Example (if the primary key is CustomerID):

    Customers.CustomerID = TempHist.CustomerID

  3. #3

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,204

    Re: cannot distinct with inner join why?

    Quote Originally Posted by gibra View Post
    You shouldn't join the tables on
    Customers.FullName = TempHist.HistCust
    since there may be more customers with the same name, and this would double the rows of those records.
    You should use the primary key/foreign key that 'should' relate the two tables.
    Example (if the primary key is CustomerID):

    Customers.CustomerID = TempHist.CustomerID
    tnk you very much sir

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width