Delete Duplicate Records And Unite Results
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 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
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
Nov 16, 2004
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed]
FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName
WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
http://briefcase.yahoo.com/turnerbkgabrobins
Thank you in advance for your assistance.
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
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
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
Dec 3, 2013
Why I am getting duplicate results for some of my records in a query. I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. SQL is below.
SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number],
[tbl_Rewards Activity Report - By Member Number].[Last Four],
[tbl_Rewards Activity Report - By Member Number].[BAL ID],
[tbl_Rewards Activity Report - By Member Number].[Primary Name],
[Code] .....
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
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 1 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
Dec 12, 2007
Hi All,
I have a existing database that I use for pulling data out of our companies data warehouse and I have noticed that when I run the query it is pulling information more than once. So I'll have 13 extra entries which then screws up my reports.
Thanks
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
Jun 25, 2007
I am using a database created by someone else, so I don't know if this is a table issue or a query issue. I create my query pulling information from an established table, and I get duplicate results. The database is intended for use as a timecard where all of our employees can record their time and we can track the projects that they are working on. So, what I mean by duplicate results is that we have tasks and subtasks for the time card and for each entry, when I pull it up in the query, it is duplicated for each task. In other words, if there are nine different tasks in the table and the subtask is "robotic testing", it will be repeated with all of the nine tasks. Like I said, I don't know if it is the table that has a problem, or if it is me. I am a super beginner and I don't know much at all about access. Please simplify your answers. Thanks!
View 1 Replies
View Related
Jan 12, 2006
Hello,
I have an unmatched query that I would like to convert to a delete query. I would like to be able to delete the unmatched records from two tables using one query. I tried something like this (below), but I get an error message stating "could not delete from specified tables". What am I missing?
DELETE PUBLICIST and STORYPERSON STEPS Old table.*
FROM [PUBLICIST and STORYPERSON STEPS Old table] LEFT JOIN [STEP TABLE] ON
[PUBLICIST and STORYPERSON STEPS Old table].Pernr = [STEP TABLE].Pernr
WHERE ((([STEP TABLE].Pernr) Is Null));
Please help!
thanks,
Pablo:confused:
View 3 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
Jun 23, 2005
Sorry, I really couldn't think of a solid title for this. I'll try and explain and provide some code as well.
I've got a series of tables, one of which is linked to a Mobile Device. I have a query (herein called Query A) that queries a masterfile, named MasterFileEdit, based on selections the user makes on the front end. Query A then returns the results, sorted by section number. Query B is set up to query based on the same selections, but instead DELETE all of those records from the masterfile. Doing this makes sure that no duplicate work is performed.
Here's the code for Query A.
INSERT INTO [TABLE A1] ( OID, SystemLoc, SystemPN, Location, Part_Number, Qty, Date_n_Time, NL, RCHKL, RCHKP, oddeven, Auditor, upperlower, area, aisle, [section] )
SELECT TOP 100 MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]))
ORDER BY MasterFileEdit.section;
Everything after the 'FROM MasterFileEdit' after is the user selections that come from a form.
I need help writing the DELETE query. Here's what I've got thus far:
DELETE MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]));
I can't get the DELETE to sort. The query runs, but doesn't select the records it should. In fact, it doesn't select any of the correct records.
Thoughts: does the TOP 100 modifier work with Deletes?
Is this query even possible?
Thanks in Advance for your Help.
Matt
View 14 Replies
View Related
Feb 18, 2013
I have a table with a field 'FName'. I have run a query to count the number of each Name in this field. However, as well as having a blank field with 9129 results (which was expected) but another with 24 results that appears to be completely blank as well. There are another couple of incidences were results in 'FName' are appearing twice.
how to identify what is causing this duplication?
View 5 Replies
View Related
Apr 29, 2014
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
View 4 Replies
View Related
May 29, 2014
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
View 5 Replies
View Related
