Building Table for Duplicate Records

I'm new to VBA and SQL, and help is limited. The following code basically selects duplicate records based on name (last name only, I think). I sort of understand up until the 'WHERE' statement, then I'm lost -- too many parenths and brackets! Can someone please explain the structure and dynamics of the statements? In addition to this, I have data that consists of a NAME field (where first and last are together, separated by a space) which I need to SPLIT into two fields. I've been told to use the SPLIT Function in VBA, but am not sure how to implement.

'delete previous records
strSQL = "DELETE * FROM tblDuplicates"
db.Execute strSQL

'insert duplicate names from table BEE into tblDuplicates
strSQL = "INSERT INTO tblDuplicates (fldID, fldLName, fldFName, fldDate, fldType, fldHier, fldPhoneNum) " & _
"SELECT BEE.ID, BEE.fldLast, BEE.fldFirst, BEE.fldDate, BEE.fldType, BEE.fldHierarchy, BEE.fldPhone FROM BEE " & _
"WHERE (((BEE.fldLast) In " & _
"(SELECT [fldLast] FROM [BEE] As Tmp GROUP BY [fldLast],[fldFirst] " & _
"HAVING Count(*)>1 And [fldFirst] = [BEE].[fldFirst])))"

db.Execute strSQL

Quote:

Originally Posted by dayharbor

I'm new to VBA and SQL, and help is limited. The following code basically selects duplicate records based on name (last name only, I think). I sort of understand up until the 'WHERE' statement, then I'm lost -- too many parenths and brackets! Can someone please explain the structure and dynamics of the statements? In addition to this, I have data that consists of a NAME field (where first and last are together, separated by a space) which I need to SPLIT into two fields. I've been told to use the SPLIT Function in VBA, but am not sure how to implement.

'delete previous records
strSQL = "DELETE * FROM tblDuplicates"
db.Execute strSQL

'insert duplicate names from table BEE into tblDuplicates
strSQL = "INSERT INTO tblDuplicates (fldID, fldLName, fldFName, fldDate, fldType, fldHier, fldPhoneNum) " & _
"SELECT BEE.ID, BEE.fldLast, BEE.fldFirst, BEE.fldDate, BEE.fldType, BEE.fldHierarchy, BEE.fldPhone FROM BEE " & _
"WHERE (((BEE.fldLast) In " & _
"(SELECT [fldLast] FROM [BEE] As Tmp GROUP BY [fldLast],[fldFirst] " & _
"HAVING Count(*)>1 And [fldFirst] = [BEE].[fldFirst])))"

db.Execute strSQL

Go through an SQL tutorial and look up the following
in clause,
count function
group by clause
and then come back to the statement and see if you still don't understand it. For the VBA split function, you'll need to look up a VBA tutorial or ask in the Access (or is it VB?) forum.|||

Quote:

Originally Posted by r035198x

Go through an SQL tutorial and look up the following
in clause,
count function
group by clause
and then come back to the statement and see if you still don't understand it. For the VBA split function, you'll need to look up a VBA tutorial or ask in the Access (or is it VB?) forum.

Okay.

Thanks.