Query records w both duplicate values and different values (1 Viewer)

vfxd · Jun 18, 2013

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:

Code:

 [/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:

Code:

 [/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 · Jun 18, 2013

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 · Jun 19, 2013

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.

Code:

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 · Jun 19, 2013

How about..

Code:

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

This will give what you have shown in Post#1

pr2-eugin · Jun 19, 2013

Just expanding..

Code:

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 · Jun 19, 2013

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.

Query records w both duplicate values and different values (1 Viewer)

vfxd

New member

vfxd

New member

vfxd

New member

pr2-eugin

Super Moderator

pr2-eugin

Super Moderator

vfxd

New member

Similar threads

Users who are viewing this thread