0

Is there any kind of VBA or SQL which i could run on this table which delete duplicate ID's within MS Access?

So this:

ID          data1   data2   data3   
a           1       2       3   
a           8       9       10  
a           15      16      17  
a           22      23      24  
b           1       2       3   
b           8       9       10  
b           15      16      17  
b           22      23      24  
b           29      30      31  
c           1       2       3   
c           8       9       10  
c           15      16      17  
c           22      23      24  
c           29      30      31  
c           36      37      38

Would look like this :

ID      data1   data2   data3   
a        1      2       3   
         8      9       10  
         15     16      17  
         22     23      24  
b        1      2       3   
         8      9       10  
         15     16      17  
         22     23      24  
         29     30      31  
c        1      2       3   
         8      9       10  
         15     16      17  
         22     23      24  
         29     30      31  
         36     37      38
Improve this question
12
  • You could SUM UP all the data columns and place the results into a temporary table. Then delete all from the main table and place the temp table back into the main table. Commented Mar 12, 2013 at 11:46
  • I need to display all of the individual records, I just need to remove the duplicates for the first and last name for readability Commented Mar 12, 2013 at 11:50
  • Ah ok thats fairly straight forward if you dont actually need to remove them but just create a view. Ill post a SQL statment in a bit. Commented Mar 12, 2013 at 11:54
  • It sounds a little bit strange though. Why delete duplicates on first + last name? What will you do when there are two persons having the same name? Chances are close to nothing, but it's just a principle that uniqueness is indicated with a unique primary key (eg. ID), unless you choose to use a composite PK (which is generally not advised in this case). The question is how you accidentally enter doubles INTO the table into the first place, this should not be possible IMO. Commented Mar 12, 2013 at 12:02
  • OK, well could we do delete duplicate unique ids? Ill edit the question to have unique ids instead of first and last names. Commented Mar 12, 2013 at 12:12

3 Answers 3

Reset to default
3

Something like the following should clear the ID column of all but the first record. You may want to amend the ORDER BY to also include another column to ensure this code runs in the same order as your export after. 

Dim strKey as String
Dim iCount as Integer
Dim dbs as DAO.Database
Dim rst as DAO.Recordset

strKey = ""
iCount = 0
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM MYTABLE ORDER BY ID")
If rst.RecordCount > 0
    rst.MoveFirst
    Do Until rst.EOF
         If strKey <> rst!ID Then
             strKey = rst!ID
             iCount = 1
         End If

         If iCount > 1 Then
             rst.Edit
                 rst!ID = ""
             rst.Update
         End If

         iCount = iCount + 1
         rst.MoveNext
    Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

See where you state rst!ID, what if the field name contains a space.....ie rst!Last name. How would you format that? I have tried [Last name] 'Last Name' '[Last name]'. Or is it just impossible?
It is still saying that the item is not found in the collection.....i think your right about the rst.Fields("Last name") statement....anything else? My SQL statement is Set rst = dbs.OpenRecordset("SELECT * FROM tblReportFollowUpCallResponses ORDER BY '[Last name]';")
Your code works perfect for fields with no spaces....strange that it wont work with fields that do have spaces....:/ any ideas?
It's the '[Last name]' bit. Change it to [Last name] (no single-quotes). What line gets the error? Another thing you could try is replacing the * in the SQL with [Last name] (again, no single-quotes) in case it's not finding that column, and then the error will generate on that line.
3

you can use view as 

select distinct First Name , Last Name from T1

and save this query as V1 then you have now first name and last name without duplicate

now use code

select * from V1 , T1 where V1.First Name =T1.First Name and V1.Last Name=T1.Last Name

I hope this help you an question you have

Improve this answer

1 Comment

This is outputing duplicate rows still within the first and last name.....I need to hide or delete the duplicates.
0

This will give you your first name, last name and a sum of all data associated with that first and last name on a single row.

SELECT FirstName, LastName, sum(data1), sum(data2), sum(data3)
FROM MyTableName
GROUP BY FirstName, LastName

This would give you something like below:

First Name  Last Name   data1   data2   data3   
a           z           46      50       54   
b           x           75      80       85
Improve this answer

2 Comments

I don't need the sum, I need to just hide the duplicate names while keeping each row
You wont be able to do that via SQL query. Sorry. Someone who knows VBA might be able to help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.