Need Help - Delete Duplicates Query
Jun 5, 2006
I am a rookie! Have a table with names addresses and years. Want to wittle it down to one name and address per. Ran totals query. Did delete duplicates. When I hit view datasheet, it shows the right info to be deleted, but when I try to run the query, it comes back with "Could not delete from specified tables." When I hit the help button, it says Error 3086 and tells me about read only. I have checked permissions, and I have full permissions. Does anyone have any idea what I am doing wrong?
View Replies
ADVERTISEMENT
Sep 23, 2005
I ran the Find Duplicates Query Wizard and it found multiple duplicates. Now how do I go about deleting these duplicates?
View 1 Replies
View Related
May 11, 2015
I have hundreds of duplicates in my database and i've tried using logic to delete them but there are still thousands left over. I know duplicates can be removed by using VBA, how to use VBA IDE.
View 8 Replies
View Related
May 16, 2007
Hi Guys,
I wanted to delete some duplicate records off of a table.
I went through the steps of copying the structure of a table to a new table then setting the Primary Key as what I wanted to be unduplicated. In my case, I have the Primary Key setup as the Account SSN and the Persons ID. This way it would be OK to have more than 1 SSN as long as I dont have more than 1 SSN and Person ID together.
I have 430 records on my table, when I run the find duplicate query, I see 22 lines (11 duplicates). I went through the table and confirmed that this is correct.
When I paste the original table to the PK table, it copies 392 records. Basically, it didn't copy 27 records it should have. I hunted down the 27 and they were not duplicates, they only had 1 SSN + Person ID.
So to make it short, the easy way to kill duplicates for some reason doesnt work for me.
Is there another way to do this? I was thinking of using the Find Duplicates Query and somehow turning it into a Delete Query, but I would only want it to Delete 1 of each it finds, not all of them.
Once again, thanks for the help.
View 2 Replies
View Related
Apr 3, 2006
I think this is an easy one...
I have two tables - one is a Master. I want to delete the records in the Master if they are in the second table. Here's my SQL and I can't get it to work:
DELETE from TestMaster
INNER JOIN on JoinedDupList
ON TestMaster.ID=JoinedDupList.ID
WHERE JoinedDupList.ID = TestMaster.ID
I'm being told I need to define the table to delete from... I thought I defined it.
Thanks!
View 2 Replies
View Related
Feb 10, 2014
I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.
Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
[Code] .....
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
View 4 Replies
View Related
Oct 20, 2014
If there a way i can remove duplicates from an email list? I pull these emails from a recordset. But i email may appear more than once and it doesn't look good e.g
johndoe@mail.com;johndoe@mail.com;jo...hndoe@mail.com.
I'd like to remove the duplicated email, if its possible. Code below.
Code:
Set rs = CurrentDb.OpenRecordset("select * from query")
With objMailItem
If rs.RecordCount > 0 Then
rs.MoveFirst
Do
If Not IsNull(rs![email]) Then
vRecipientList = vRecipientList & rs![email] & ";"
rs.MoveNext
Else
rs.MoveNext
End If
Loop Until rs.EOF
.To = vRecipientList
View 6 Replies
View Related
Aug 13, 2013
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
View 6 Replies
View Related
Apr 2, 2007
I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
View 2 Replies
View Related
May 19, 2011
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
View 13 Replies
View Related
Feb 12, 2008
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
View 6 Replies
View Related
Dec 8, 2005
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
View 14 Replies
View Related
Aug 23, 2005
I have a table in a healthcare patient database that stores Admitting Number, Scan Number, Last Name, and some other fields detailing the scan. Each patient can have multiple scans and I'm trying to design a query that lists how many scans each patient has had by finding all the duplicate admitting numbers. I'm using this SQL statement:
SELECT MultScans.Admitting_Number, Count(MultScans.Admitting_Number) AS Num_Of_Scans
FROM MultScans
GROUP BY MultScans.Admitting_Number
HAVING (((Count(MultScans.Admitting_Number))>1));
which works fine but I can't figure out how to also print out the last name, scan number and the other fields in this query. If I add the field to the select part it says I can't because the expression is not part of the aggregate function and if I add the expression to the 'group by' the query runs but displays all the duplicates. Thanks for any help.
View 3 Replies
View Related
Apr 4, 2006
Please help. I am having difficulty understanding why a subform is showing duplicates despite using SELECT DISTINCT for its query. I have looked all over the forum but am still quite confused.
I think my database is normalised but this may be the problem and can attach it if necessary.
My query is as follows
SELECT DISTINCT StudentAndCourse.ClassID, StudentAndCourse.LevelID, StudentAndCourse.StudentID
FROM [Level] INNER JOIN (Class INNER JOIN StudentAndCourse ON Class.ClassID=StudentAndCourse.ClassID) ON Level.LevelID=StudentAndCourse.LevelID;
Any help would be most appreciated as I cannot understand what is going wrong.
View 1 Replies
View Related
May 1, 2007
Hello,
I am just wondering whether there is any way I could run a simple query that would pull all the data I need and inform me of the duplicates if there is any.
Thanks!
View 2 Replies
View Related
Mar 25, 2008
Hi everyone.
I have a query linking 2 tables. The idea of the query was to show our longest serving customers. The one table contains CustNo, Address details, etc and the second table contains AccNo, CurrBal, AccType, CustNo, etc etc. (note the bold fields are the primary keys). However this is bringing back results on account level rather than customer level, i.e CustNo 3 might Have AccNo xxxxxx, AccNo xxxxx1, AccNo xxxxxx2. How do I get it to show the 'oldest' account for each customer. So it will only show each customer once and that customers oldest account. Heres the SQL for the query. Hope someone can help. Thanks
SELECT DBSINV97.DateOpen, DBSINV97.AccNo, DBSINV97.CustNo, DBSINV97.PostalName, DBSINV97.CurBal, DBSINV97.AccType, DBSINV97.DateClose, DBSCUS01A.Title, DBSCUS01A.Initial, DBSCUS01A.Surname, DBSCUS01A.Street, DBSCUS01A.District, DBSCUS01A.Town, DBSCUS01A.County, DBSCUS01A.Postcode
FROM DBSINV97 INNER JOIN DBSCUS01A ON DBSINV97.CustNo = DBSCUS01A.CustNo
WHERE (((DBSINV97.DateOpen)<"2002-01-01") AND ((DBSINV97.DateClose)="1800-01-01"));
View 1 Replies
View Related
Mar 26, 2008
I've been researching and reading about how to eliminate duplicates in my Query, but all the suggestions don't seem to work. Here is my problem:
I want to create a Query with information from 2 Tables. Ultimately, I want my Query to be a malining list that I will be sending requests to. But, I want only the names, addesses, etc. of the people who fit certain criteria (info. stored in several different fields of the two tables). So, if a person matches two of my specified criteria, they appear twice in my query. Or if they fit all the criteria, they might appear 5 or 6 times. I want each name and address to show up once, even though the person may fit into several criteria. Does this makes sense?
I've tried typing "DISTINCT" after "SELECT" in SQL, but that didn't do anything. I've also tried changing the "Unique Values" to YES, but that didn't work either.
Sorry if it's unclear, but I am very new to Access! :(
View 7 Replies
View Related
Nov 24, 2004
Hello
I've created a query that finds duplicate records in a table. I've also made a form based on that query that shows some fields of the duplicate recoeds found. In that form there's also a textbox that counts the records found (using the count([field]) function). Everything works perfectly when there are duplicate records. However, when there are no duplicates , I'd like the textbox value to be 0 (it shows nothing now obviously because the query returns no records), as long as some other actions to be performed. How can I check (in the vb code or somehow else) if the query returns no records as a result ? Thanks in advance
View 3 Replies
View Related
Jan 9, 2006
Friends,
I have a table with 3 fields:
NAME
AMOUNT1
AMOUNT2
Field AMOUNT1 and AMOUNT 2 can have the same data and I need to run a query which will return records with similar data in both fields. Both fields are text (5 characters) and the match has to be done on the first four characters.
Please note that I have tried with a Find Duplicates query but does not seem to return all the records with showing the same data probably to the fact the the last character is different.
Any help? Thank you.
View 3 Replies
View Related
Aug 14, 2006
I am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field
Any suggestions appreciated
View 1 Replies
View Related
Aug 1, 2007
I am using a combo box to assign gun numbers on an employee form (frmemployees). I also have a query listing only the gun numbers that have been assigned. When I select a gun from my combo box, I what to evaluate if it is already on the gun assignment query. That way I cannot assign the same gun to two different people. I am using the before_update event to do this. The code that I am using is as follows:
Private Sub intgunNumber_BeforeUpdate(Cancel As Integer)
If DLookup([intgunNumber], "qrygunassignmentcurrent", Me.intgunNumber) = Me.intgunNumber Then
MsgBox "Item # is already listed.", vbOK, "DUPLICATE FOUND"
Cancel = True
End If
"me" represents my frmemployees form. The problem is that it's causing an event even for the numbers that aren't listed on the query. I am trying to get the evaluation correct but am having difficulties. All the fields are numeric fields. :( :mad:
View 6 Replies
View Related
May 13, 2005
I have a Duplicates Query that extract the following information from my Table
Name Surname DOB Joined
The query then extracts info such as...
Bill Smith 25:12:70 1999
Bill Smith 25:12:70 2005
Most of the time the 'Joined' field has the same year. But I need to find the ones where the year differs but the rest of the information is exactly the same. Because there is 15000 odd records to check, I thought that there would be a quicker way to check.
Could someone please assist me with this query if it is possible?
Thank you in advance.
View 1 Replies
View Related
Nov 25, 2005
I have the following two querys which are supposed to return only the latest Note for a Prospect.
QRYNotes
SELECT TBLNotes.ProspectID AS Expr1, Max(TBLNotes.NoteDate) AS MaxOfNoteDate
FROM TBLNotes
GROUP BY TBLNotes.ProspectID;
QRYNotes2
SELECT DISTINCT TBLProspects.CompanyName, TBLNotes.NoteID, TBLNotes.Note, TBLNotes.NoteDate
FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.ProspectID=TBLNotes.ProspectID) INNER JOIN QRYNotes ON TBLNotes.NoteDate=QRYNotes.MaxOfNoteDate;
When I run the QRYNotes2, I get more than one record per Prospect instead of just the newest NoteDate.
What is wrong?
View 3 Replies
View Related
Jul 31, 2006
Hi All.
I have a db, with, amongst others, one form that I enter job details, and another form that I enter the materials for the job. These forms are linked by the ID_Number. This all works fine
I have a query that links both these forms together, so when the query is run, it shows the ID_Number of all jobs that have been done completed between dates, whether they have materials assigned to them or not.
This all works fine, but if for example, there are 10 jobs in one month and one of those jobs have more than one material booked against them, then that job number will show twice.
What I would like to do is "Hide Duplicates" on the query.
Can someone help?
Many thanks.
Frank.
View 1 Replies
View Related
Apr 4, 2007
I have a query that includes the following fields
Year
EventNumber
CorrectiveActionNumber
ResponsibleGroup
Committment
Each event will have multiple corrective actions. For each corrective action assigned to a group, the "committment" is also assigned to them, but I also have to assigned a committment to our group (SRG). So this makes the query results show two records for each corrective action, one with the group assigned committment and one for my groups committment. I need a query that will show corrective actions that have the group committment assigned but not a corresponding SRG committment assigned. However since my query results in two separate records, I am unsure how to do this.
Any suggestions?
Thanks,
Jim
View 4 Replies
View Related
Jun 6, 2007
hi all, im trying to make a query so that when someone updates a field in a form it searches a table (Companytable) which is a list of company names. If it exists i want it to then display a msgbox " already exists" sort of thing! trying to do this in the criteria field on the query. anyone have any ideas or useful resources, ive not done this for a while and cant remember much of IF THEN sort of stuff. mainly not sure if im barking up the right tree, i can search dependent on a set value but not sure how to do it dependant on whats been typed into a form..any pointers would be appreciated! thanks! James
View 2 Replies
View Related
