I need an Access query that returns customer orders where more than 1 product type was purchased.

Question

I thought this would be simple enough, I tried the solution here, but can't get it to work Count(Distinct) doesn't work in access apparently. So here's my relevant structure:

tblMaster with fields ID (Primary Key), fldOrderNumber, ProductID, and fldPurchaseDate

tblProduct with field ProductID (key) and fldProductName.

Certain order numbers will have multiple productID entries; so if someone bought 3 products in one order, there will be 3 rows in tblMaster with the same fldOrderNumber, but with a different ProductID in each row. I need a query that only returns those fldOrderNumbers.....so basically non-unique fldOrderNumbers.

Answer 1

I can't currently think of a simpler version than this. This could be quite slow for larger tables.

SELECT A.*
FROM 
    tblMaster AS A 
LEFT JOIN 
    (
        SELECT DISTINCT 
            fldOrderNumber, 
            COUNT(*) AS [Count] 
        FROM 
            (
                SELECT DISTINCT 
                    fldOrderNumber, 
                    ProductID
                FROM 
                    tblMaster
            )
        GROUP BY 
            fldOrderNumber
    ) AS B 
ON 
    A.fldOrderNumber = B.fldOrderNumber
WHERE 
    B.Count > 1;