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
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
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
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:
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 RelatedI 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;
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" ....
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 RelatedI 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 RelatedIn 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.
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.
I have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried:
1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing!
2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
what else can I do?
Thanks
I have recently encountered some issues with my database. When I load data from an .xlsm file to my front end table, I get additional records with no data only the ID. The data I loaded comes in fine, however I get many of these records with no data only the ID. This only happens in one of my tables.
View 2 Replies View Relatedpls can someone point me in the right direction
have searched through the forums.
i have two fields Forename & surename i need to prevent duplicate records if the forename and surename match another record.
Hi
I have a table (which is populated by people filling in a form) which contains two fields:
Section
WeekCom (e.g. week commencing)
I want to make sure that the form will not allow someone to make a double entry. For example, if Sally goes in and says enters "Benefits" as the section and "WeekCom" as 15/10/06, and then Ben tries to do the same - the form will not allow Ben to enter it.
How do you do this please? The table does not have a primary key at the moment....is there any way of doing it without setting one of the fields as a primary key?
Thanks
Maria
I Need to run a query that will not show duplicate records.
For example if joe smith is listed five times i only want the most recent date of membership.
Does anyone have any ideas?
I have a form in Access which has event date and then also an event time slot such as "Morning, Afternoon, Evening" . I need to make it so that you cannot have a duplicate time booking, i assume i will need to code some VBA but if anyone could tell me what it would be much appreciated. Thanks
View 2 Replies View Relatedi m new user to access i just wanna know how can i count duplicate recodes for example how many time
record repeat in one column
waiting for ur quick reply thanks
I have a query of records that are not related to records is the secondary table. I want to delete these records but when I select the records and click delete I get the confirmation to delete answer to delete them and all the records disappear. When I run the query again, all the records are still there. Does anyone know what is happening? This is an access front end that connects to a SQL server database.
View 1 Replies View RelatedThis works perfectly.
SELECT DOCSADM_PROFILE.SYSTEM_ID, DOCSADM_PROFILE.AUTHOR, DOCSADM_SECURITY.THING, DOCSADM_SECURITY.PERSONORGROUP, DOCSADM_SECURITY.ACCESSRIGHTS
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID = DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));
Now how do i tell this query to delete these selected records from security table....?
I have a survey form that has the questions on a subform based on the response to one of the survey demographic questions (via a query based on that response). The answers to the questions are on a sub-sub form. How can I get the program to delete the answers that were entered (they are related to the primary survey form with a common ID number) if the user changes the survey response.
In other words, if the user of SurveyID 5 decides that they want to fill out the list of blue questions rather than red questions, how can I delete all the answer blue records for SurveyID 5? Right now, it will display the red questions if you change to red and enter red answers, but the blue answers remain in the database, creating 2 sets of answers for SurveyID 5.
Any suggestions...if this is not too confusing?
Thanks!
Hello all:
Form created based on a query. When i go to the form, the add new record and delete record buttons are disabled.
I have checked and re-checked the form, tables, queries and all seems well.
Does anyone know how I can fix this problem?
Many thanks,
Dion
possible to delete all records from one table except the first; Is that possible?
View 7 Replies View RelatedWhen I import an excel spreadsheet into my Access database it always creates a couple of new blank records. I import this spreadsheet frequently so I would like to set up a macro that will automatically delete any blank records it creates. Can someone tell me specifically how to do this?
Thanks.:)