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


Post your answer or comment

comments powered by Disqus
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...

Greeting all,
I have a db where I am wanting to reset or clear the values for specific fields for all records in a table. For example, I have a vendor table that tracks the amount of marketing dollars each vendor has committed. At the end of the year, I want the DB user to be able to hit a button and reset that value to $0.00. in the vedor table. I have several fields like this and would like to automate this task with vb code or a macro. I don't know much vb code but I can copy and paste like a mad man. Can anyone help

I hope everyone's having a good Friday. Thank you kindly to anyone kind enough to offer some advice...

I have a table of attendance data for children attending a club. This table (T_Attendance) records the AttendanceID (autonumber) ChildID, Date and whether they attended or not. I'd like to use this data to create a log of when each child started / stopped and restarted coming to the club. This data should be recorded in a table called T_Actions, which records:
ActionID (autonumber, primary key) ActionChildID (number) ActionDate (date) ActionType (number: 1 for started, 2 for quit, 3 for restarted) I would like to create a query or piece of VBA code (I'm not fussy which, it only needs to run once), which for each Child will go through the attendance records in order of date, and perform the following actions:
If the current record is the final record for this child AND the date is more than 30 days earlier than NOW, Append a record to T_Actions to say child has quit (use the day after the current record's date as ActionDate). If the current record is not the final record for this child AND the difference between the date of the current record and the date of the next record is greater than 40 days Update T_Actions to say child quit (use the day after the current record as the date). Add a further record showing that they restarted on the date of the following record. I only have accurate attendance records in the database since 1st July 2009, so this query should ignore any records with dates prior to that.

I get the feeling this should be quite simple to accomplish, however I'm not very sure how to begin with this so I'd really appreciate any help.

Many Thanks,

Alan

I would like to find a way to limit records in a table by using VBA code. I have a couple of tables that contain data specific to the user (licensed). It is my understanding that these are much safer if entered in code vs. sitting out in a table where clever users may track them down.

For example, let’s say I only wanted to allow 3 records into tblMasterTable. I can set the validation rule (in design view of the table) to have the PrimaryKey (integer): > 0 And

Can anybody tell me how to delete all records in a recordset?

I have a form with text boxes that are updated with their values from other forms or dlookups. I want to take that information on the form and create a record in a table with that data.
Whats the Best / easiest way to do this?


Not finding an answer? Try a Google search.