Over 1 million tech questions and answers.

query to delete duplicate records

Q: query to delete duplicate records

How can I create a query to delete duplicate records?

Preferred Solution: query to delete duplicate records

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: query to delete duplicate records

Read other 9 answers

I am trying to create a delete query to delete all parent records (DGRP) that does not have any child records (CLLI) but I get the error "Could not delete from specific tables".

WHERE (((CLLI.CLLI) Is Null));

Can this be accomplished?

A:Solved: Access Delete Query to Delete Parent with no Child Records

Read other 9 answers

I am using an Excel worksheet with several thousand records listed. On a daily basis, I get an updated list, but I need to keep hold of some of the 'old' records.
At present I'm copying the new list, adding it to the end of my existing list and then filtering it so that only unique records are visible.
Obviously, this means the list is ever-increasing with many duplicated records (hidden or otherwise).
Is there a way to simply delete the duplicate records (so I can just keep one record of each) rather than hide them?


A:Solved. Excel 2000 - delete duplicate records?

Read other 15 answers

I have a spreadsheet that has duplicate records but i cant take out the duplicates based on just one primary field.

For example: 2 lines of data look the same but they have different addresses which i would need both addresses plus there could be multiple people with different names that stay at that address so I could not use address as a primary key.

I honestly would like to use the whole line of data with all fields and if there is anything different about the fields id like to keep it but if every field is the same i would like for one line to be taken out.

Can someone help me please !!! Oh i am definitely a novice user so I would need some good directions lol ..

Field Names:

First N/ Middle N/ Last N/Suffix/Member SSN ( Not always a populated field )/Date of Birth/Address Line 1/Address Line 2/City/State(All the same)/Zip/Home Phone/Email/Marital Status/Gender/Student/Disabled/Relationship Code/Hire Date/Emploment Status/Affiliation
(Access 2003)

A:Duplicate Records

I take it that you actually mean a table (not an Excel Spreadsheet).
I am not sure that you should leave the same person with more than one Record in the database for very long as that is going to lead to serious confusion when you need to enter data for them.
You could use all the fields for comparison purposes. The way to do this is to create Memo, set it to Indexed with no duplicates and add the data from all the fields to it and then use that in Query which looks for Null in that field, you may even be able to use all the fields directly in a Find Duplicates query as well.

Read other 1 answers

I have the following code in a text box on a form:

=IIf(DCount("*","[qryContactsExtended]","[ID]<>" & Nz([ID],0) & " And [ContactName] = '" & Replace(Nz([ContactName]),"'","''") & "'")>0,"Possible Duplicate","")

It just comes up saying #Name?

Any ideas?

Thanks in advance.

A:Access duplicate records

Quite a complex string to troubleshoot visually, #Name often means Access can't resolve a field or control name. Fully qualifying the [ContactName].[Table1] if its in tables and queries could help. Also if the text box itself doesn't have a unique name that can cause the error.

Also the DCount function is very touchy (technical term ) about its syntax. You might pull it out individually and make sure its working correctly.

Read other 1 answers

Slurpee55, I think you gave me the following code...

Sub DelDups()

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim V As Variant
Dim Rng As Range

On Error GoTo EndSub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set Rng = Range("D1" & Range("D65536").End(xlUp).Row)

For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If V = "" Then Rng.Rows(r).EntireRow.Delete
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
End If
Next r

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

This works awesome for 50 records or so (probably more) but i am having issues when trying to run it on all 34K records... it consistently times out excel and wont finish it. Is there a more efficient way to do this? Again it works great if I only do it on 50-100 records, but I have quite a few... I tested it on 1000 records and it took maybe 90 seconds... before i let it sit for 25 minutes for all records..

Thank you for everyones assistance...

A:Duplicate Records - Excel VBA

Read other 6 answers

Good morning,

I have a data extract imported into Access. The fields i have are:

System number (taken fm original system)

The system number is definitely unique, however some of the data entry clerks may have entered on a client more than once in error. Therefore what I want to do is identify records where the Surname, Forename, DOB is identical so that I can investigate if they are genuine duplicates or not. There are 14000+ records on the database so I can't just sort it alphabetically and browse an eye over it.

Any ideas how I can easily accomplish this?

Many thanks


A:Finding Possible Duplicate Records

Read other 6 answers

Hi all,

I need to find duplicate data of company names in one column. The example looks like:

Column A
1. Postal
2. Postal Air
3. PostalAir
4. Energy resources ltd
5. Water us pty. ltd.
6. Plastic company

The duplication in the table is not always because of the same name, but it can be one date use the full company name and the other data is using the shortened name (for example, data in A1, A2 & A3 can be the same company) I have about 7,000 company names, please help me if anyone know any quicker way than doing this one by one, I use Excel 2007.

Thanks heaps!

A:To find duplicate records

its going to be difficult to do this, if the first three are identical as an example - I assume just looking at the fors 8 characters is not going to help as the same company may have a different 1st set of characters

you could use a Mid function to isolate a few characters and compare
Mid(a1, 1,6) - that takes the 1st 6 characters
now you could compare to the previous line
so you could put this is a column - starting at rows 2 copy down
and where the 1st 6 characters match you will get a 1

which in your example should put a 1 against the 1st three

anything else in the data that can be compared ?

the above test the current cell with the previous row cell and also the next row cell if either match it will put a 1 - then you can eyeball the 1;s and decide if they actually match
if you have issues with spaces
so postal (space) service
and postal (nospace) service
you could use
=SUBSTITUTE(A1," ","")
to reove leading and training spaces

Read other 2 answers

I've got two lists merged into one worksheet: 1) Kids who were registered in 2003 and 2) kids who were registered in 2004.

I know how to filter for duplicate names (Data-Filter-Advanced filter), but that process hides only ONE of the duplicate records and leaves the other.

To create a list of kids who were registered in 2003 but who have not yet registered in 2004, I need a way to filter for duplicate names and delete BOTH records when it appears twice, leaving me only those names that appear on one list but not the other.

In other words, if a kid is registered in 2003 AND 2004, delete both names because I don't need to worry about him any more.


Read other answers

Hi there.

I have a file with a unique id in the first 30 characters.

I want to identify duplicate records in a file that may have two of the same unique ids in the file.

Can someone help with this.???

so im thinking something like:

sort -u | ?? > duplicate_ids.log

thanks in advance.
p.s what would be even more handy would be if I can enter a range of field positions in the file that I want checked, so for an example say I want field 10 to 35 checked for dups, I would enter:

script.ksh 10 35

A:Duplicate Records in unix file

Read other 12 answers

the method access reccommends doesn't work for me. I need to delete duplicate records in a Foxpro database that is linked to Access.
Are there any programs I could write that would eliminate every other record in the duplicates query?

A:deleting duplicate records in access

Read other 8 answers

I'm using Microsoft Access 2003.

I have a table with data containing information related to Customer Number, Customer Name & Product. Some customers have more than one product (so there are multiple records in the table)...example Customer: 1243 has a row for Product: A and another row for Product: C.

I want to be able to say if Customer 1243 has multiple products than only show me the record for Product C.

Note: Not all customers have multiple records

Does anyone have any ideas on how to do this?


A:Solved: Duplicate Records in Access Table

Hi am1st1,

Welcome to the forums.

Firstly, you should Normalise your data. http://en.wikipedia.org/wiki/Database_normalization.

Your current data, has a relationship of Many-to-Many, a big no no, in properly normalised data - an important paradigm of database design. So you should have a table with your customers, one with your products and then a table linking them together, with the unique ID from your customer table and the unique ID from your products table.

I'm not sure how to select product C over A without doing it programmatically using VBA, as I do everything that way, however it may be possible, and someone may be able to give you the answer to that one, but you should normalise your data first.

Failing that, I could write some VBA for you, however, I need to know all the potential options, as well as the data fields, once normalised, the best thing would be to remove any sensitive data, replace it with good dummy data, and then upload the database. If you'd prefer, you can email me the database, just send me a private message, and I'll let you know where to send it.

Read other 2 answers

What is the best way to check an Access table for duplicate records?

A:Solved: Access Table Duplicate Records

Read other 9 answers


I am using VAMT 3.0 and when I discover products it appears to be readding all the same computers back into the database.  I use a LDAP query:
LDAP://domain.com??sub?(&(objectClass=computer)(operatingSystem=Windows 7*))
I never appeared to have this problem in previous versions, when I ran the query it would just append to my list the ones that needed to be added, but since going to 3.0 when I run the query it adds ALL the computers it finds.
so for example previously I had 10,000 machines and ran the query and only 14 machines needed to be updated it would only add the 14.  Now the same query is readding 10,014 machines and I have tons of duplicates.
I end up having to clear my database and re-run the query again to start fresh. 

Surely there is a way I can append new machines into the domain without adding all these duplicates.


Read other answers

how can i merge or remove duplicate records from outlook and outlook express?

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: Genuine Intel(R) CPU T2050 @ 1.60GHz, x86 Family 6 Model 14 Stepping 8
Processor Count: 2
RAM: 2550 Mb
Graphics Card: Mobile Intel(R) 945GM Express Chipset Family, 224 Mb
Hard Drives: C: Total - 54988 MB, Free - 19013 MB; D: Total - 55476 MB, Free - 27160 MB;
Motherboard: Acer, Inc., Prespa1
Antivirus: ESET NOD32 antivirus system 2.70, Updated: No, On-Demand Scanner: Enabled

Read other answers


How can I remove duplicate records?

I have are report as such:

Emp No Name Hire Date Status Company/Subsidiary

7791 Anna Jackson 07/01/2002 Active Access
7997 Irene Hagedorn 08/01/2002 Active Access
7798 Alice Smith 08/01/2002 Active Access
7798 Alice Smith 01/01/2003 Active iSmart
Record Number 3: Displays a hire date of employee to 'Access' Company. Record 4: shows employee was employed in iSmart, a subsidiary of the main company.

The requirement of the report needs to display only record 4 and discard record 3 in the list.

What procedures should I do?

Thank you in advance,


A:Crystal Report: How do I remove duplicate records?

Your query isn't filtering unique records because you also have the field Company/Subsidiary in it and it will always bring up both. Try filtering further using the most current date and then you will only get one record for each employee. From the looks of your table structure more than one employee with a unique ID number can work for more than one Company/Subsidiary...is that correct?

Read other 1 answers

I'm running a query off of a linked table and it is duplicating 112 records. I've run the query to find them already now I want to delete them. The "Delete" key under the Records tab is inactive and therefore doesn't allow deletion. I'm assumming it's because it is running off of linked table. Is there a way around this?

A:Deleting Duplicate Records in Access 2007

sylnz, welcome to the Forum.
Have you tried creating a Delete query using the Duplicates query as it's data source?

Read other 1 answers

My question is hard to articulate but I will try. I know what I need is possible because it used to work fine but now it does not!

As part of my job I spend a lot of time looking at property records from the county websites and tallying up sales. Often there are multiple records for the same property and I am only want to track the record once, not multiple times. I used to know when the record was a duplicate because IE would show the link as purple instead of blue. For some reason, IE is no longer doing this now, what setting do I need to go to to make this work again?

Any help appreciated!

Read other answers

I have created several parameter queries in my database. They show the initial records, but if i add new records, the records show up on the form and in the tables but do not appear when i run the queries again. This happens on single table and multiple table queries.

As an example the criteria i use is: Like "*" & [Enter VRN] & "*"

Grateful for any help.

A:access query not all showing records

It is most unusual for Single tables queries not to show the records, but multi table queries rely on getting the Joins correct to show them if there are no "matching" records in one or more of the other tables.

Read other 1 answers

I am trying to use "Count" to count each individual record in a Query. I only have 25 records in the database so far, and each time I run the query record #2 counts as "5". Everything else shows a "1". I have looked at my table setup, and all the records are set up just alike. I can't figure out what's wrong.

Anyone have any ideas?

A:Help counting records in Access Query

What kind of query? A Totals Query I hope?
What field are you "counting"?
Are you SURE it's record #2 and not 5 records for that field?

Read other 3 answers

i have a forest with one domain, HQ and some branches i enabled the debugs for DNS logs and noted that there is abnormal behavior the ldap srv record form is not as i expected where supposed the record will be in this form  _ldap._tcp. SiteName ._sites. DnsDomainName,
 but in the log i discovered it become _ldap._tcp. SiteName ._sites. (ServerName) Netbios
name of the DNS server. this normal record is not exits and all my DNS servers forward the my SRV records to the internet to resolve it and return fail in the resolving every time ....... as mentioned below 
i guess there is an issue in my DNS servers but can not find where is the problem 
11/1/2016 1:19:32 PM 0740 PACKET  000000D2DEED28A0 UDP Snd (IP address of DC02)   30b9 R Q [8281   DR SERVFAIL] SRV    (5)_ldap(4)_tcp(5)SITE1(6)_sites(11)DC02(0)
11/1/2016 1:19:41 PM 0740 PACKET  000000D2DF8DDA40 UDP Snd   a3da R Q [8281   DR SERVFAIL] SRV    (5)_ldap(4)_tcp(5)SITE1(6)_sites(11)DC02(0)
11/1/2016 1:24:26 PM 0BE4 PACKET  000000D2E187D1E0 UDP Rcv (IP address of DC01)   273e   Q [0001   D   NOERROR] SRV    (5)_ldap(4)_tcp(5)SITE1(6)_sites(11)DC01(0)

11/1/2016 1:24:26 PM 0BE4 PACKET  000000D2D960FFD0 UDP Snd    fb03   Q [0001   D   NOERROR] SRV    (5)_ldap(4)_tcp(5)SITE1(6)_sites(11)DC01(... Read more

Read other answers

I want use VBA to count the number of recs in a query. I know how to count # of recs in a table (using openrecordset) but not a query - that code is listed below---

Set db = CurrentDb()
Set rec = db.OpenRecordset("EVALUATION")

intRecords = rec.RecordCount
MsgBox "There are " & intRecords & " records in the EVALUATION table"

A:Solved: VBA to Calc # of Records in a Query

The code is the same, just change the name in the set rec to the name of the Query.
You can also count the records in a query as well.

Read other 2 answers

Hi there i wonder if you can help. I have a report which is based upon a query (Course Table used) that shows information on courses, like shown below. For the one you see below in red 'No of Students enrolled this is where i am hving the issue. i would like for the query to automatically count the number of students that are enrolled on that specific course. To make things a little more complex i have created status to evaluate whether the course is "Completed", "On Going", or "Completed' related to whether the Start and End date are greater less than or equal to todays date. So the count will have to involve this.

Course ID
Course Name
Start Date
End Date
Number of Student Enrolled

The tables are linked together with

Student Table--------Student ID
Enrolled Details Table------------EnrollmentID,StudentID,CourseID
Course Table------------Course ID,CourseName,StartDate,EndDate
Status Field: Course Status: IIf(Now()>[Course Start Date] And Now()<[Course End Date],"Course on going",IIf(Now()<[Course End Date],"Course Not Started",IIf(Now()>[Course End Date],"Course Completed")))
Any help would be greatly appreciated. Thanks for your time and efforts.

Read other answers

At the end of my Query and Report I want to show a total of records returned. Can anyone tell me how to do this please. Thankyou.

A:Access - Query/Report records total

Read other 7 answers


I have a database that was created by an old colleague. I have modified it a little bit, but the Queries seem to be written in SQL, and although I can look at them in the normal Access design view, I need some help.

I am trying to remove any records that come back with "12:00:00 AM" in the "signon" field of this query.

How would you build an expression to remove such a record. If you need more detail, let me know or you can pull down a copy of the database here:

http://www.savefile.com/files/152026 (3.5MB)

If you do pull it down, I am looking at the "signon120" through "signon124" queries.

Let me know if you can help.

THanks, Nuschool

A:Solved: Removing Specific Records in Access Query

Hello again, first of all create a select query with the signon" field in it.
Then add 12:00:00 AM to the "Criteria" row of that column (Access will change it to #00:00:00#).
This should only select the required records.
If it does only select the required records then in design view on the main menu click "Query" and change the query to a "Delete" query.
Save the query and close the query.
Run the query and it should tell that it is about to dlete the same number of rows as it listed when you ran it as a Select query with the criteria in place.

Read other 2 answers

System: Microsoft Windows 2000
Software: Microsoft Access 2002
Subject: MS Access 2002- Pulling Random Number of Records in Query

I am working on pulling a random number or records in an Access query (Top 3 for now) and am trying to figure out how I can include every record from a related table at the same time. I am currently using a randomizer function to pull up 5 random records from one of my tables, the sql for the query reads

SELECT TOP 3 tblOrders.*
FROM tblOrders
WHERE (((randomizer())=0))
ORDER BY Rnd(IsNull(tblOrders.OrderDescription)*0+1);
The function reads:
Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function
I have a customer table (tblCustomers) whose recordset looks like this:
CustomerID FirstName LastName
1 John Atkins
2 Beverly McKinnon
3 Stewart Larsen
4 Debra Svenson
5 Angela Mayor
6 Lewis Hernandez
The related Order table (tblOrders) looks like this:
OrderID CustomerID OrderDescription
1 6 Stationary
2 6 Ballpoint Pens
3 6 Legal Paper
4 6 Lazerjet cartridge-Black
5 1 Notebook pad
6 1 Headset
7 4 3 Ring Binder
8 5 Liquid paper
9 5 Stationary
10 4 Legal Paper
11 2 Lazerjet cartridge-Blue
12 4 Folders
13 4 3 Ring Binder
14 5 Liquid paper
15 5 Ballpoint Pens
16 3 Notebook pad
17 3 Stationary
18 3 Headset
19 2 Lazerjet cartridge-Black

My inquiry is this: How can I create a query that includes all Customers listed in the customer table (tblCustomer... Read more

A:MS Access 2002- Pulling Random Number of Records in Query

Hopefully the attached database should clear any confusion.

What I'm hoping to accomplish is a way to include all employees, but just keep the orders randomized in increments of 3.
Please let me know if you can find anything out.

Read other 3 answers

I wrote an append query to add new data from one table to another table, but when I ran the query, it had duplicated each record ten times.

The table I am appending to is the main table. It has five more fields than the append from table. It has an auto number which is also the primary key. I did not include the auto number field in the append query, as advised by the help file. The tables have no relationship.

Not only did it post 430 lines instead of 43, the autonumber started at #397 instead of row #10 which was the next row. No rows have been deleted from the table as I just created it, so it's not that. This error may correct itself if I can correct the other error, but I thought it was worth mentioning.

Can anyone give any pointers to where I am going wrong?

Many thanks!

A:Solved: Access 2003 append query duping records

Read other 7 answers

I'm working on a database where a user can perform searches by dates and when they click my command button it saves the form's data into a table then runs an SQL sequence which creates a table. I need that table to delete the results and append when the next user performs a search. My code is as follows

'CurrentDb.Execute "Update tblFilter set InspectStart=#"
cboStartDate.Value = Null
cboEndDate.Value = Null
RunCommand acCmdSaveRecord
sql = "Select * into tblInspectionDateFilter from qryInspectionDateFilter"
DoCmd.RunSQL sql
'DoCmd.OpenQuery qryInspectionDateFilter, acNormal, acEdit'
Call MakeTables
DoCmd.OpenReport "MasterReport", acViewPreview
I am not sure how to go about it. Need help please. The qryInspectionDateFilter is the one I need to delete and append to. Thanks

A:Deleting a table and appending its records before a query in Access 2007

ladyvj, can I ask why you do it this way?
Why create a table to create another table?

To answer your actual question you just need to create a "Delete Query" for the table that you want to clear of records and run that at the appropriate time in your current VBA code

Read other 1 answers

Our environmental nonprofit has several projects, and most projects have either a marine or terrestrial conservation area. Some have neither, and have therefore been marked as "no conservation area."

Some projects also have multiple conservation areas, and they might be of the same type. So I have a table like the following:

Project ID: Conservation Category:
1 Marine
1 Marine
1 Terrestrial
2 Neither
3 Terrestrial
3 Terrestrial
4 Marine

Sorry if that's hard to read - the first column is supposed to be numerical project ID's and the second shows which type of conservation category it has.

So I need to make a query that'll give me the number of projects with Marine, Terrestrial, Both, or Neither conservation categories. But I have no idea how to approach this, since I don't know how to get a query to recognize the "Both" condition.

The query should give the following result for the example above:

Conservation Category: Count:
Marine 2
Terrestrial 2
Both 1
Neither 1

Explanation: Project 1 and 4 have marine components. Projects 1 and 3 have terrestrial components. Only project 1 has both a marine and terrestrial component. Only project 2 has has "neither" component.

A:Solved: Access 2007 - Query to count records with multiple requirements

Read other 10 answers

Hi all,

I have been struggling for a few days to build a functioning query. I actually managed to make it work for a smaller set of data but I'm using the following function:

RankOfDup: (SELECT Count(*) FROM [Table] WHERE [field1]=[table].[field1] And [field2] >= [table].[field2])

which I believe slows my query, if I do select query it runs for around 2 minutes but if I try to make table it never executes.

The reason I need make query is that after this I need to create Crosstab query that basically turns the ranks from rows into columns. I tried to create the crosstab using the select query with the RankOfDub but it gives an error that the the database doesn't recognise a field.

Any ideas how I can make this work for large set of data, ie rank the rows and turn them into crosstab?

If anything is unclear please ask questions and I will explain.

Thank you very much, I hope someone can help!

A:Solved: Access 2003: Make-table query with around 2 ml records taking too long

Read other 16 answers

Sounds like a Lord of the Rings reference doesn't it? Ok i have 3 duplicate tables. 3 people will be entering data each into their own table. We have another girl that will need to see all the data from all 3 dispatch tables in one table. Now the fields are identical in each table. Can i write a query that will take for instance. Work_Order field from each of the 3 tables and display it in one field in the query, and do this for each field?

I dont want it to say Mike.Work_Order, Bob.Work_Order, Chris.Work_Order. I just want it to say Work_Order and display them from all 3 tables. Then do the same thing for each other field in the 3 tables. So she only sees the equivelant of one table with all 3 tables data inside it.

A:Solved: 3 duplicate tables, one query to combine them

Read other 9 answers

Hi all,

Right I have a table, which is the following:
ID - Autonumber
ID2 - Number
ID3 - Number
Duplicate? - Yes/No
Name - Text

Sample Data:

ID ID2 ID3 Dup? Name
1 1 1 - Alan Smith
2 2 2 - Bob Smith
3 3 3 - Bob Smith

Data I want:


ID ID2 ID3 Dup? Name
1 1 1 N Alan Smith
2 2 2 Y Bob Smith
3 3 3 Y Bob Smith

You can ignore the ID's - they are not all the same as in the sample data, but they are irrelevant for what I need.

As you will see from the data above, "Alan Smith" is a unique name, and therefore Duplicate? is set to NO; whereas "Bob Smith" has two occurrences, so the Duplicate? is set to YES.

I know I need an append to go through each line to check if it is a duplicate. Any thoughts on how I can go about this???

A:Query to tick a yes/no box if a field contains a duplicate value [Access2010]

Read other 15 answers

I am working with a large data set containing vehicle positions. The data was collected with a very high sample rate so for each vehicle I have positional data every 5 milliseconds. This makes the data set huge, so I want to strip down the set so that it only contains one data record per second.

The table looks like this:

ID Date Time Long Lat
1 2011-06-30 21:26:35 59.529139 -1.628551
2 2011-06-30 21:26:35 59.529138 -1.628541
3 2011-06-30 21:26:35 59.529140 -1.628546
39 2011-06-30 21:26:36 59.529242 -1.628534
40 2011-06-30 21:26:36 59.529247 -1.628533

I want to only save one record with exactly the same Date and Time (in the above data set two records would have been saved, one from 21:26:35 and one from 21:26:36). I have used the "Find Duplicates Query Wizard", selecting the fields "Date" and "Time" and it gives me the duplicates so that I can delete all but one of them manually.

Problem: the date set contain over 2 million records so manual deletion is impossible.

Question: How can I do this with a query that lets me keep only one record for each identical Time and Date?

A:Solved: Access: remove duplicate values with query

I would create a duplicate blank table by copy and pasting the "Structure Only" and then add a field that adds the date & time together and then set it to Indexed - No Duplicates.
Then create an Append Query based on the big table appending from the big table to the new one, you will have to add a Column Heading with the Concatenation of the date & time which is added to the new field in the table.
When you run that query it will add the fist date & time and then the rest will be excluded due to the "No Duplicates".
You can then create a "Delete" Query to delete all the records in the big table.

You may not need to append the ID field if you don't want to, you can use an autonumber in the new table

Read other 2 answers

I'm using hjt.networktechs.com to analyse my HJT log file which is at http://hjt.networktechs.com/parse.php?log=544159

I'm getting an error "Could not execute query correctly. : 1062: Duplicate entry 'TomTomHOME.exe' for key 1Logfile of Trend Micro HijackThis v2.0.2
Old Version of HijackThis
We suggest you upgrade to the latest version of HijackThis (version 1.99.1") at www.merijn.org

followed by the normal stuff:

Scan saved at 21:31:00, on 14/10/2008
Platform: Windows XP SP3 (WinNT 5.01.2600)
MSIE: Internet Explorer v6.00 SP3 (6.00.2900.5512)
Boot mode: Normal

Can anyone explain why I'm getting this error and what it means please?


Read other answers

I'm sorry, I know this is more like a Microsoft Office post but I cant get any answers when i post topics over there, and I was hoping someone here could help

Here is the link to a zip file containing my website, the database is in the database folder

My Web Site

To download it, type in the verification text, wait the 30 second delay and press the grey download button.

The problem I am having is that I cannot write any new records to the database, or edit it or delete them. I am hoping that someone could run the web site on their machine and tell me what is wrong with it. I think it may be a problem with permissions or something like that, but I have no idea how to fix it.

Thanks in advance

A:Can't Add, Delete or Edit Records in my Access Database using ASP or ASP.NET


Read other 4 answers

Hello everyone,

Nice to meet you guys, was wondering whether you guys can help me out...kinda in a mess right now.

I got a few access tables each in different access db, each contain millions of records and I am sure each contain duplicates.

When I try to use my PC to jus find dup, either the status bar goes half way(for 24hrs) or said out of memory.
Can someone tell is the a way to delete these dup's in a fast and less resource consuming way?
Thank you so much!

A:how to do a fast delete of dup in millions ms access records?

Read other 15 answers

I currently have a macro that selects all records in a table and deletes them. The problem is when the macro deletes the records, it prompts the end-user for confirmation... Which I don't want it to do. I just want it to delete the records and continue with the other macro procedures.

Is there a way that I can setup the macro so that it doesn't prompt the end-user for confirmation? I know I can turn off deletion confirmations all together but I don't want to do that either.

FYI - The part of the macro that I am referring to is setup like so:

Open Table (Opens the respective table)
Run Command (Selects all records)
Run Command (Delete Record)
Close (Closes the respective table)

A:Access 2K: Macros - Delete Records Without Confirmation

Read other 8 answers

Afternoon everyone,

I've got a question. I created this code, but I get the error: "Block If without End If". The idea is that the administrator can delete multiple records (rows) based on what he selects, using check boxes.

I put this code:
Private Sub DeleteRecords_Click()
If CONTACTS.Select Is Not Null Then
RunCommand acCmdDeleteRows
End If


A:Solved: Delete multiple records (rows)

Problem fixed,

used the wrong syntax for the object and IF command.

Read other 1 answers

I'm pretty good with databases, but this one has me stumped as I can't find a solution anywhere, nor do I really know what the deal is - but I'm sure it'll be something so simple that I overlooked that I'll feel retarded.

I have an access database that has a table with the following structure:

company, address, state, zip, state, phone (all with data populated)

I then created another table named "new" and added a field called phone.

I put in XXX amount of phone numbers into the new table named "new".

I basically want to compare the records from Table 1 Phone to the Phone in "new" table, and remove them from the original table.

Everytime i do that, I get a "Recordset not updateable" error, I've tried all sorts of queries, etc. - no avail.

/squirm - help!

A:Solved: MS Access 2003: Cannot Compare &amp; Delete Records

Read other 10 answers

I filtered a list of records on a number of fields. I used the filtering to then delete the rows that appeared after filtering. I then removed the filtering to Show All undeleted records. However, I found that some data rows were being deleted that normally remain. Since I've never had a problem before with this technique, I'm wondering if this is a 'known Excel problem'?

I know this is a tricky one for anyone to help me on because you can't check the way I did my filtering.

However, any ideas much appreciated!


A:Excel 2000:Filter then delete only filtered records

Read other 6 answers

We're having difficulty with a large data file of 4 million records in Access 2000. We can't delete records after it has been linked. Also, when opening an unlinked database, it automatically links it.

error: operation not supported by this ISAM.

A:Access 2000: Can't Delete Records in Large Data File

Read other 6 answers

I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.

A:XLS Macro to move data to columns, delete empty rows, delete duplicate columns

Read other 11 answers

Hi All,

I have an update query which deletes data from fields on my table before closing a form. These fields are calculated fields.
Full message: MS Access didn't update 1 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
When I click on yes, execute, it does what I need it to do, but really want to get rid of this message.
The query:
Field(1):Thermal top calculation
Table: Employees
Update to: Replace(NZ("Thermal top calculation",""),"""","")
Criteria: is not null
Field(2):Thermal trousers calculation
Table: Employees
Update to: Replace(NZ("Thermal trousers calculation",""),"""","")
Criteria: is not null
Main criteria is to execute where employee name on table = employee name on form
The fields i want to update are not required fields.

Where did I go wrong?


A:Solved: MS Access can't update all the records in the update query

Eva, it is possible that the query can't put in the value that you are asking it to do, ie you may be trying to put aText of nothing "" in to a number field which requires a 0.
If you are using VBA or a Macro to trigger the query use the Set Warnings function to switch off the warnings and then set them back on again after running the query.

Read other 2 answers

Can someone help me find a Windows(XP)-based program that I can use to delete records from text-delimited flatfile using a file of email addresses?

Actually, it could be a text-delimited, CSV or an XLS spreadsheet - I can put the data in several formats.


I bought a list of several thousand "opt-in" email addresses, but had about 40% of them bounce. I also had a few request "opt-out". The task of deleting the individual records is staggering without a helper!

My background is a mainframe systems analyst. The only PC-based programming language I became good at was compiled (4.5 or 7.1) BASIC. I sure hate to have to try to find my BASIC compiler, re-install it (if the diskettes could even be read!) and write a program from scratch.

I have created a flatfile containing a list of all the "bounces" and "remove me" email addresses (8K+ records!) and need to use it as a "driver" to find the records in the flatfile and eliminate them.

If anyone can help... THANKS!

Sam SoRelle
[email protected]

SSI Texas
SacoTech LLC
Houston Texas USA

A:How to Delete Records From Text-delimited Flatfile Using a File of Email Addresses

I could write a Bash script in a couple of minutes to do it but that would require you to run Linux.

Read other 2 answers

I cannot get a delete query to delete; it keeps saying "Cannot delete specified records", and the 'Help' button doesn't bring up any help. It should be simple. Have two tables with the same fields, except that tbl2 has one additional field called [Delete] that contains an "X" for the records I want to delete from tbl1. Each table has an ID number, but in tbl1, it's a primary field while in tbl2, it's only a number.

I first created a query that selected only those records in tbl2 where there is (a) an "X" in the Delete field and (b) the Contribution field is null. I used that query in the delete query where I inner join it to tbl1 using the ID number. I put the "*" from tbl1 in the first column of the query, and it correctly puts "From" in the Delete row. If I do a View of the result, it displays 234 records, which is the correct number of records to be deleted. But when I try to run the query, all I get is the error message.

What's wrong with the query?

A:Solved: Delete query won't delete anything

Read other 14 answers

Been using Glary's Utilities 3 to clean things up. When I ran its "Duplicate" cleaner, there were many duplicates with iTunes and my pictures. My problem is that I don't know which to delete and which to keep. Here are screen shots to give you an idea of what the duplicate scan showed (BTW, there are many other areas where there are a mess of duplicates,but to keep things simple-I hope, I've only shown those duplicates that may take up a lot of space:

A:Which duplicate(s) to delete?

Flycaster mate cannot see any screenshots? so cannot comment.

Personally I am not that overly keen on add on software like Glary but it's your choice.

Read other 2 answers