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.
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.
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?
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.
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.
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
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
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.
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.
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!
All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
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?
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.
Hi. I have two tables, of which i have run a duplicate query, removed the duplicates by doing an "is null" criteria, and keeping that new table. I know though there are still more duplicates but the names may not be exactly the same, say barclays and barclays plc. so i run another query on both tables to show me the first five letters of table 1 and table 2. I can run another "is null" criteria query to find the non duplicates there. these are the unique values.
But even if there are duplicates up to the first five letters the next letter may change, for instance barcleys tractors and barclays bank. So these arent duplicates. So can anyone come up with a way of taking the duplicate ones according to the first five letters, and maybe duplicate ones up to 10 letters, and working out which ones are the unique ones. my brain is frying.
I have imported a 1 column table from an external source for the sole purpose of deleting duplicates. There are over 400,000 rows. What do i need to do to identify the duplicates?
I work at a hospital and deal with several thousand account numbers a day. I am working on an Access database that will handle some of my workload. Here is what I am facing. Lets say on some of the tables that I import there are duplicate account numbers with different totals in the end...
acct info info info amount 123456 xxxxx xxxxx xxxx 5.50 123456 xxxxx xxxxx xxxx 17.50 123456 xxxxx xxxxx xxxx 900.00 753951 xxxxx xxxxx xxxx xxxxxxxx
and so on...
is there a way that i can only show one account number but the total of the amount for the account? please help...this is desperately needed.
Hey All, i'm looking for some help from one of the heavy hitters in the Access Forum. (Keep in mind i'm self-taught in access..never picked up a book on the stuff) So far i have done ok. I'v made many databases and in my opinion i have a firm grasp on the fundamentals.
I have come accross a database that I did not create which i am suppose to work on. I can do the work just find but i'm a little confused on the structure. when i looked at the relationships i noticed that many of the field names in several tables were bold (showing they were set as a primary key) So i looked at the design view of a few of these tables and noted that the primary key was set on 3 different fields. i also noticed that each field allowed duplication. Furthermore i noticed that on at least one of the tables, in the relationship window there was a relationship using the ID field which was a 1 to many. however there was a relationship on the other side of that table still using the ID field that was a many to one. How can a field have a one to many relationship and a many to one?? it can either be duplicated or it can't...right??? Anyways i also tried to duplicate this in a test database and found that when you make a table and then right click at the side of the field you wish to make as Primary key, that works fine, but doing it on another field simply switches the primary key to that field and takes it off the first one.
(as a side bar..is the only way to search this forum to go manually through all the threads to see if you can find what you need? aka...no search tools?? or did i just miss them?):confused:
I have a table that has country_code, Mfr_num, Item_num, catalog_code, mfr_obsolete_code. the key is on country_code, mfr_num, and item_num. I need to find duplicates of mfr_num and catalog_code. Can anyone point me in the right direction on this?
I have all my master data in my table which has a lot of duplicates (700 entries). I ran the find duplicate query offered by access, but exported the data to excel to delete all the duplicates.
My question is how do I import that updated excel list back into the duplicates query so that it updates my master data in my table?
I have got a table that has following fields display on a form:
ClassNo Class Date ClassType ExecutionDate Order Quantity
The Table CLASSES has got many records which are duplicate. Mostly ClassNo is duplicated. I could have stopped duplication by restricting table not to accept duplicate values, but my requirement is such that i dont want to stop the table from accepting duplicate values. When i display the same table data on the form, all the duplicate entries are displayed.
All i need is to find a way to stop the FORM showing duplicate ClassNo Field. The Duplication restriction i wish to set only on ClassNo. If there are more than one ClassNo reptition than it should show that entry only once. For example the form looks like this:
ClassNo Class Date ClassType ExecutionDate Order Quantity 1 12-12-2006 A 12-01-2007 Ok 10 1 1 1 1 1 2 20-10-2006 A 20-11-2006 Ok 100 2 2 2 2
I wish to see the form this way:
ClassNo Class Date ClassType ExecutionDate Order Quantity 1 12-12-2006 A 12-01-2007 Ok 10 2 20-10-2006 A 20-11-2006 Ok 100
I have a form and within that form there is two combo boxes and one text box. When one choses a value from one combo box, and then chooses a value for the other combo box and then enters the value into the textbox I don't want the user to be able to enter the same arrangement. That is I want to prevent duplicates on that combination. Also if they do this I want a Message Box to appear saying that that this combination already exists
I was thinking of using a multiple field index to prevent the duplicates, but I don't know if this is a wise thing to do. Can someone give me some help to see how I can prevent duplicates or offer another solution to prevent duplicates on the combination of the values.
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.
i have a form with a sub-form on it. each has is bound to a table. the main form's table has a one to many relationship with the sub-form table, and the sub-form table has the PK of the main table as a FK.
every time i try to add a second record to the sub form, it gives me an error saying i cant have duplicate foreign keys.
i cant figure out what to do. i am using access 2003 and microsoft sql server version 8. i need to enable duplicate foreign keys so that each record of the main table can have more than one record in the sub table.
