Query records w both duplicate values and different values

[vfxd]

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:

 [/FONT]
[FONT=Courier New]AcctNum  FoodType  FoodClass
-------  --------  ---------
A123     Apple     Fruit
A123     Apple     Fruit
A123     Grape     Fruit
A456     Potato    Vegetable
A456     Potato    Perishable
A789     Carrot    Vegetable
A001     Banana    Fruit[/FONT]
[FONT=Courier New]

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:

 [/FONT]
[FONT=Courier New]AcctNum  FoodType  FoodClass
-------  --------  ---------
A456     Potato    Vegetable
A456     Potato    Perishable[/FONT]
[FONT=Courier New]

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable'). Any help on the SQL for this would be greatly appreciated. Thanks!

 

[vfxd]

PS -- I'm not trying to delete these semi-duplicates, I'm just trying to identify them & create a recordset with them.
I'm happy to send $5 via PayPal to the (first) person who can help me with the SQL. Thanks.

 

[vfxd]

I think I'm close to figuring this out. There's probably an easier way, but for others who come across this post, the below query will return a list of AcctNum + FoodType pairs which have more than 1 different value for FoodClass. From here I can create another query to give me the full list.

SELECT
  AcctNum, 
  FoodType, 
  Count(FoodClass)
FROM 
  (SELECT DISTINCT AcctNum, FoodType, FoodClass FROM MyTable ORDER BY AcctNum, FoodType, FoodClass)
GROUP BY 
  AcctNum, 
  FoodType
HAVING 
  Count(FoodClass)>1
;

 

[pr2-eugin]

How about..

SELECT AcctNum, FoodType, FoodClass
FROM MyTable GROUP BY AcctNum, FoodType, FoodClass;

This will give what you have shown in Post#1

 

[pr2-eugin]

Just expanding..

Dim rstObj As DAO.Recordset
Dim dbObj As DAO.Database

Set dbObj = CurrentDB()
Set rstObj = dbObj.OpenRecordset("SELECT AcctNum, FoodType, FoodClass FROM MyTable GROUP BY AcctNum, FoodType, FoodClass;")

If rstObj.RecordCount <> 0 Then
    Debug.Print "The Following have the same AcctNum and FoodType but different FoodClass : "
    Do While Not rstObj.EOF
        Debug.Print rstObj.Fields("AcctNum") & " - " & rstObj.Fields("FoodType") & " - " & rstObj.Fields("FoodClass")
        rstObj.MoveNext
    Loop
End If

rstObj.Close
Set rstObj = Nothing
Set dbObj = Nothing

Is this something you were after?

 

[vfxd]

Thanks for the reply pr2-eugin. I think the SQL you posted would include all unique combinations of the three fields. But I only wanted to include fields which have *more than one* possible FoodClass value for a given AcctNum+FoodType pair. The SQL I posted in answering my own question does this, though perhaps not as efficiently as possible. Thanks also for the code, but I don't need VBA code for working with the recordset, I just needed the SQL for creating the recordset I was trying to get to.