-
Jan 22nd, 2017, 01:09 AM
#1
Thread Starter
Enjoy the moment
[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
-
Jan 22nd, 2017, 02:48 AM
#2
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
-
Jan 22nd, 2017, 05:56 AM
#3
Thread Starter
Enjoy the moment
Re: cannot distinct with inner join why?
Originally Posted by gibra
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|