Deleting duplicate records in a table


What I am trying to do is delete duplicate records from a table. I have created a Select statement, below that gives me the dup. records but I now need to delete those form the table.

SELECT DISTINCTROW First([68sdi COPY].DWGNO) AS [DWGNO Field], First([68sdi COPY].ASBUILT) AS [ASBUILT Field], Count([68sdi COPY].DWGNO) AS NumberOfDups
FROM [68sdi COPY]
GROUP BY [68sdi COPY].DWGNO, [68sdi COPY].ASBUILT
HAVING (((Count([68sdi COPY].DWGNO))>1) AND ((Count([68sdi COPY].ASBUILT))>1));

Any and all suggestions would be greatly appreciated. Thanks in advance

Bill


Sponsored Links:



Dear all,

How to create a button delete duplicate records from a table in Access 2007

Many thanks




HOW TO DELETE DUPLICATE RECORDS
create a query with all the fields and an additional exp field combining all fields like=[field1] &[fild2] &[field3] . Run this qry as a make table query. Copy this table to another table with structure only.Fix the expression field as unique key and append to this table all records from the table which was created through make table query.




I want to place code in a click event to delete all records in a table but keep the table for new records. DELETE * FROM Employees WHERE Title = 'Trainee'; seems to have trouble with the asterisk.




HI experts,

I want to delete the duplicate records from a table, so is there any query available in sqlserver?




Is there a macro that will delete all the records in a table? Ive tried a few different ones but they're not what im looking for.


Thanks,
Matt




Can somebody point me in the right direction
for some VBA code to delete all records
in a table. I know I can create a macro and call the macro,
but like to do it in VBA

Thanks for any help




I an about to write some VBA code, where following some procedures, I will want to delete all record from a table to start fresh the next time I run the code. How can I accomplish this?
Thank you in advance for your kind attention to this inquiry.




I have a table on which I cannot set a primary key because there are duplicate records in the table. How can I use a query to show the records that are duplicated?

Robert




All,

How do I prevent duplicate entries in a table? Here is my problem. I have a table set up to maintain balances of security positions in a portfolio on a monthly basis. From the table I have created a form to allow users to enter the data. The fields are Month, Year, Asset Type, Country and Balance.

I want to prevent the users from entering the same combination of Month, Year, Asset Type, Countryand Balance. Then have an error message pop us when a duplicate combination is found

Once again, thanks for your help

Joe B




I am looking to put a “Command Button” on a form to delete all records in a table. I found a Macro that deletes the whole table but I just want to delete the records. Any Ideals? Thanks




Hi, I want to delete all the records in a table after sending these records to a report named as "MonthlyBalance" when I click a button. I written the following codes and I knew there is something wrong but I don't know where... can someone plse help me out with this? Here, I assume the field "BookTitle" will not contain "*" so all the records should be deleted...
Or is there a more correct way to delete all the records..?

Set rst = db.OpenRecordset("Select * From BalanceTable", dbOpenDynaset)
stDocName = "MonthlyBalance"
DoCmd.OpenReport stDocName, acPreview

wrk.BeginTrans
Do Until rst.EOF
If (rst!BookTitle <> "*") Then
rst.Delete
rst.Update
End If
rst.MoveNext
Loop
wrk.CommitTrans

Thanks!




I have a table that contains financial data on projects. Each project has one record consisting of numerous fields.

I want users to be able to archive off copies of these records at certain points in time so I can track the financial data - a bit like storing different versions or issues.

On any one day I want users to be able to access the live record and review the archive.

Question : Is there a simple way of duplicating a record in a table ?

At the moment I am using a form by copying all of the field values from this form, moving to a new record, writing the field values back to the form hence creating a copy. This seems somewhat laborious.

Can't I access the table directly somehow and simply cut and paste ?

Also, is there a simple way to check for a duplicate record before cloning the record i.e. if the data has already been archived once don't do it again.




Hi,
I am using Vb6.0 as a front end and msaccess as the db.
i want 2 delete all the records in a table "Register" by clicking a menu
The connection is made using ADODB
Till now i hv the code

Private Sub mnudel_Click()
Docmd.SetWarnings False
Docmd.RunSql ("DELETE * FROM Register;")
Docmd.SetWarnings True
End Sub

But when i execute this a error msg displays
Run time error "424"
Object required

Whats the problem in me
plz help me to come out from this...




In my database i have a table 'tblClients" who has a one to many relationship with the table 'tblOrders'.
This table 'tblClients' is a 100% copy of a table with the same name that resides in another database. Each moment, the data in my table must be exactly the same as the data in the table in the other database.
Data of several records in the original table can be changed and in the same time new records added,so the updating can mean here updating existing records and in the same time adding of new records. Futhermore , the changing of the data in existing records isn't seldom just changing the value of one column in the table,this is more then often different from record to record. I have no direct connection with the other database. The changed table is sent to me as a attachement of a email in the form of a csv. Making a routine that try to find out for every existing record what is changed in the original table and in the meantime finding out what are the new records, seems to me not so simple and maybe very time consuming.
For these reasons, i believe i'm better of by deleting all records in my table and after that inserting all records of the changed table in my table.
This can be done easily with VBA code. However, because there is a relationship as i mentioned hereabove, the deleting of some records in my table can't be done. What can i do to get round this difficulty. I use MS ACCESS 2007, so making use of replication to solve the whole problem isn't possible here.




Hi All

I try to write some VBA code to delete all records in the existing table and use Transferspreadsheet command to paste new record to it. Can anyone help me out this? It is very appreciated.

Thanks

Rex




Does anyone have some code that can loop through all the tables in an access database and delete every record in said tables?

I am doing a project where I have to download poorly designed XML documents. Each document I download into access creates a ridiculous amount of tables with one record each. luckily each document dowloads the same tables... so I want to append these tables once I get the DL process automated.

Since I am testing a download process atm I need a clearing mechanism that doesn't involve me going into each table and deleting the data, nor writing hundreds of seperate delete queries for each table.

I know there must be a way to loop through every table object in access and delete all the records in it, but I've never had to do something like this, so I don't know where to begin.

I am assuming that it can't be very involved... but like I said... I don't know.

If anyone has ANY code that's even close to this that I can fiddle with that'd be a great start...

Thanks,
Gary




I want to have a command button that when clicked will delete all records in a few different tables. I do not want to delete the table just the records?
jon




Dear,

In the middle of data entry in a MS Access form, If the user press cancel button, the updated record in a table must be undo.
Ho it is possible.
I can delete the newly entered record, but i need to undo the updated record also.




Hi,
I am using Vb6.0 as a front end and msaccess as the db.
i want 2 delete all the records in a table "Register" by clicking a menu
The connection is made using ADODB
Till now i hv the code

Private Sub mnudel_Click()
Docmd.SetWarnings False
Docmd.RunSql ("DELETE * FROM Register;")
Docmd.SetWarnings True
End Sub

But when i execute this a error msg displays
Run time error "424"
Object required

Whats the problem in me
plz help me to come out from this...