470,596 Members | 1,182 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

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
Jun 20 '07 #1
2 1287
r035198x
13,262 8TB
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.
Jun 20 '07 #2
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.
Jun 28 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by andreas.maurer1971 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.