Another Duplicate Delete Question...
Mar 22, 2006
Have searched the forum but cannot find a suitable answer...
I have duplicates in my table that I need to delete but leave one existing record, e.g.:
ID Part Family Colour Cost
1 123456 HHH M98765 12.99
2 123456 HHH M64975 12.99
3 123456 HHH E79461 12.99
4 123456 HHH G13467 12.99
5 123456 HHH M98765 12.99
In the above example i would want to delete records 2-5 (based on Part and Family, even though the colours are different) leaving one there. If I run the find duplicates and then change that to a delete query then it deletes them all.
This needs to be done via a query as it is done in automation, so manually copying the table structure and applying primary keys to the fileds etc. is not an option.
Any bright ideas?
View Replies
ADVERTISEMENT
Apr 30, 2007
Hello,
I have a table that has many duplicate records. I tried importing the table and setting the field to no duplicates but did not work. How can I delete duplicate records. The primary Id id fine I have another field that I can use. it is a BAC number that is repeated many times. Here is the table I need fixed.
Thanks
View 1 Replies
View Related
Mar 22, 2006
Have searched the forum but cannot find a suitable answer...
I have duplicates in my table that I need to delete but leave one existing record, e.g.:
ID Part Family Colour Cost
1 123456 HHH M98765 12.99
2 123456 HHH M64975 12.99
3 123456 HHH E79461 12.99
4 123456 HHH G13467 12.99
5 123456 HHH M98765 12.99
In the above example i would want to delete records 2-5 (based on Part and Family, even though the colours are different) leaving one there. If I run the find duplicates and then change that to a delete query then it deletes them all.
This needs to be done via a query as it is done in automation, so manually copying the table structure and applying primary keys to the fileds etc. is not an option.
Any bright ideas?
View 3 Replies
View Related
Aug 7, 2007
I have a list of projects and am creating a query that tells me their duration based on beginning and ending dates. These beginning and ending dates can be edited by the user, and frequently, there are multiple beginning dates. When I query the projects, instead of getting unique project numbers on the left hand side to create unique rows, I get many of the same projects with different start dates. I would like to use the FIRST start date only for every project and not have multiple rows with the same projects being counted multiple times.
Is there any way to delete the excess rows and keep the original row? I've read a lot about using DISTINCT and UNIQUE INDEX with SQL but it seems as though these are only available for tables and not queries. Bright ideas, anyone?
View 6 Replies
View Related
Jan 14, 2008
On the below data I need to delete records where dep is blank or the same for the same ID. So I need to find and del recNr. 1,4,5. Any help will be appreceated!Thanks
recNr IDDep
1 ABX
2 ABX 1234
3 ABX 1258
4 CDE
5 CDE 7895
6 CDE 7895
View 5 Replies
View Related
Feb 2, 2006
Help please!
I have a form (frmHalfTab) built on a table (tblHalfTab2) that has existing data in it. In the very near future, I will be uploading additional data to this table via excel cut and paste. What I want to do is search for and eliminate duplicate records that have duplicate data in TWO fields, (MemberNum) and (MBRDrug), so that if MemberNum is "10" and MBRDrug is "Zzz" for more than one record, the duplicated records need to be deleted, OR a check box needs to be checked automatically for all the identical records, the original and the duplicate(s). I wanted to put this behind a button, if possible.
Now, I have created a duplicate query that will find the duplicated records, but I'm not sure how to use it to get rid of the duplicated records or check the checkbox appropriately. Should I ditch this query and choose a different route, or am I on the right track? Thanks in advance for your help.:confused:
View 2 Replies
View Related
Dec 14, 2004
Hello,
I have two related table: table_Company and table_contact. Now i have:
Table_Contact Table_Company
James Bolton -----------> MacDonalds
Hellen Kidman ----------> MacDonalds
Now, i wanna have one "MacDonalds" in my Company table, and delete the others "MacDonalds" and still have the relationship between my contacts and their company.
View 4 Replies
View Related
Mar 5, 2006
Good morning all!
I have a problem, I have a contact list table in my database such as name, addr, company, street, country etc. The problem is that I have multiple duplicates of the same data.
How do i delete the duplicates? Is there an SQL query that I can run to delete? How do I do it. Please help. Thanks a bunch!
View 2 Replies
View Related
Sep 1, 2005
Hi,
I have table tblSample (IdNo, SCode, StaffNo) where IdNo is AutoNumber. This is sample of data,
IdNo SCode StaffNo
10 ABC 50424
11 DEF 50424
12 GHI 50424
21 ABC 50424
22 DEF 50424
23 GHI 50424
26 ABC 50424
27 DEF 50424
28 GHI 50424
Is it possible for me to delete records from IdNo 21-23 and IdNo from IdNo 26-28. Any idea? Thanx in advance..;)
View 1 Replies
View Related
Apr 12, 2012
I have two tables that have duplicate order numbers. I would like to compare the first table with the second and then delete the records from the second that match.Can I set this up in a macro and run it every day?
View 2 Replies
View Related
Feb 19, 2013
I have a table that has 1335 records in, and columns names, Title, First Name, Surname, Address, Address2, Town/City, County, Postcode.I want to know how to write a SQL code, which will locate all the duplicates that match the First Name, Surname and Postcode and delete them, but keeping 1 copy of the duplicate.I have created a query to locate the duplicate records, and I have put the SQL code there. I tried changing this, however it would delete all the records, so I am not to sure what to try...
SELECT SurnameSearch.Title, SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Address, SurnameSearch.Address2, SurnameSearch.[Town/City], SurnameSearch.County, SurnameSearch.Postcode
FROM SurnameSearch
WHERE (((SurnameSearch.[First Name]) In (SELECT [First Name] FROM [SurnameSearch] As Tmp GROUP BY [First Name],[Surname],[Postcode] HAVING Count(*)>1 And [Surname] = [SurnameSearch].[Surname] And [Postcode] = [SurnameSearch].[Postcode])))
ORDER BY SurnameSearch.[First Name], SurnameSearch.Surname, SurnameSearch.Postcode;
View 1 Replies
View Related
Jun 12, 2013
I am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.I have a table with 4 columns lets say for simplicity they are A, B, C, D
I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below
Example
A B
John Doe Tires
John Doe Wipers
Allison Doe Tires
Allison Doe Tires
As you can see from the above Tires is a duplicate and need to be deleted.
A B
John Doe Tires
John Doe Wipers
Allison Doe Tires
View 7 Replies
View Related
Nov 18, 2014
The charity that I volunteer at has a web site through which they receive donations.
Most of the COMPANY names that they receive through their website are JUNK.
I would like to have a system to delete duplicate company names:
A = first programmatically delete all company names that are GOOGLE and this will remove about 60% of their spam
B = next group all Duplicate Company names, which will be removed manually
View 6 Replies
View Related
Sep 24, 2013
I know this probably has been asked a 1000 times but I don't seem to be able to edit the uncommented VBA-codes I found online to work on my database.
I already ran a query that shows me the duplicate records of my original table. Now I m looking for a way to
a) delete all duplicate records (not just specific ones by using a criteria) and
b) unite the unique records (result of a)) with the original ones that were not displayed with the "find-duplicates-query" ....
View 1 Replies
View Related
Apr 22, 2014
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
View 8 Replies
View Related
Sep 21, 2015
I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
View 1 Replies
View Related
Mar 19, 2007
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
View 3 Replies
View Related
Dec 30, 2013
We are working on an Access (2007) database that is on a SharePoint Site (2007).
Currently the form is operational, but there is one last thing that would be nice to have.
The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.
In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.
All data is currently bound, so once the user makes a change it is made, no submit button is required.
We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.
TABLE - UPDATED HEADCOUNT
COLUMN in UPDATED HEADCOUNT - EMPID
FORM CELL user will input an EMPID - newEMPID
FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID
So below is what we are trying to do, we are sure there are a few commands missing....
=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")
View 11 Replies
View Related
May 6, 2014
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.
View 13 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
Mar 11, 2007
I have a table with duplicate rows. How do I de-duplicate the table so that there are no duplicates and find out how many duplicates there were in the table.
Thanks:)
View 2 Replies
View Related
Feb 23, 2008
Please i am trying to create a form that will keep records of perspnnel but i want a situation whereby if someone enters a last name and first name that is already in the record, the database should send a message telling the user that such a name exists.
Regards
Henry
View 3 Replies
View Related
Mar 19, 2006
:confused:
I have got to create a car hire db for a course i'm doing.
I have a vehicle table
I have a customer table
I have to be able to book more than one vehicle to a customer.
I tried putting vehicleID into my appointments table twice but that is wrong.
Any solution would be great.
View 3 Replies
View Related
Mar 19, 2008
Hi All,
I have a table that has a device column and a tag # column, I want to pull out a report that shows duplicate tag # for different devices, the tag # can only be assigned to one device, I received an excel sheet that has to be fixed, I have the sheet in an access table. Please help. For example: device 123 and device 345 have tag #abc. I can’t simply create a query to show duplicate for tag# because I can have duplicate # only if it’s the same device.
View 4 Replies
View Related
Feb 22, 2005
I have a form with a bunch of fields. Now their are three im concered with. CFR_EHPID,CFR_SVCDATE,CFR_PROVTAXID. If thier is a vaule in the database when the user tries to enter it again, i want a dup message to show...... SO i dont want any duplicates for those three feilds. is their a way when the user is done keying and tries to move to the next record it displays that thier is a duplicate??????. below is the code i use but its for the before update event
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.CFR_EHPID.Value
stLinkCriteria = "[CFR_EHPID]=" & "'" & SID & "'"
'Check StudentDetails table for duplicate StudentNumber
If DCount("CFR_EHPID", "CFR", stLinkCriteria) > 0 Then
'Undo duplicate entry
'Message box warning of duplication
MsgBox "Warning Student Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Student Number
End If
Set rsc = Nothing
End Sub
View 9 Replies
View Related
