I have a table of Order Numbers (tblOrderConf) and a table of Order Details (tblOrderDetails). tblOrderDetails has a field [Seller] that can either be Company1 or Company2, so a single order can have items sold by both company names.
I am trying to create a query to find all orders with details sold by both companies (most orders have all details sold by the same company name).
This is what I have now:
Code:
SELECT DISTINCT tblOrderConf.ConfNum, tblOrderDetails.Seller, Count(tblOrderConf.ConfNum) AS CountOfConfNum
FROM tblOrderConf INNER JOIN (tblOCLogo INNER JOIN tblOrderDetails ON tblOCLogo.Seller = tblOrderDetails.Seller) ON tblOrderConf.ConfTableID = tblOrderDetails.ConfTableID
GROUP BY tblOrderConf.ConfNum, tblOrderDetails.Seller
HAVING (((Count(tblOrderConf.ConfNum))>1))
ORDER BY tblOrderConf.ConfNum DESC;
I have it set to only show unique values, but the Count counts each detail record and not the number of records displayed (which would be either 1 or 2).
Is there a way to have it only count the records displayed? That way if Count > 1 it will only show those orders sold by both companies.