Tables :: Merge Duplicate / Similar Records Into 1 Record In Access 2010
Jun 9, 2014
Is there a way to merge duplicate/similar Access 2010 records into one record?
I have an Access table with 1,000 duplicate records, although they are similar and not exact duplicates. As you can see below, some records contain information that other records do not. Yet, the primary key is the same for all duplicate records. I want to find a way to merge data from filled cells of duplicate records into empty cells for each duplicate record. I do not want to concatenate the data (i.e. combine last and first name, etc.). I only want to fill empty cells if there is a match for it in a duplicate record. I will delete the newly exact duplicate records later. Short of correcting the records by hand.
Example
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. NULL john(at)gmail.com
Doe John 123-45-7891 NULL (123)456-7890 NULL
Desired Result
Code:
LastName FirstName SSN Address Phone Email
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Doe John 123-45-7891 123 Anywhere St. (123)456-7890 john(at)gmail.com
Hi there, I'm quite new to Access.I have two tables which have very similar Data, but laid out differently. The main similarities are things like FirstName, LastName, Company. What I need to be able to do is merge the two tables together into one super table. I've tried linking FirstName with FirstName and LastName with LastName on both tables, and running a query, so I can check for duplicate names. But all that brings up is a list like this:Steve Smith Steve AbrahamsSteve Smith Steve McDonaldSteve Smith Steve PerrySteve Smith Steve Vere(those columns would be Old FirstName, Old LastName, New FirstName, New LastName)There are fields in the old table that are not in the new, and vice versa. But the core fields are labeled the same. What's the best way to proceed here?Cheers,Hob
I have a MS Access 2010 application when the User opens form CONTACTS Form
◦User finds single record to be used CONTACTID is identifier to be used for selection ◦User clicks button to open form frm_MAIL_MERGE ◦Frm_MAIL_MERGE has a drop down combo box that reads the folder location where the application resides and displays all .DOTM files (that is all template files) and one more combo box which contains the CONTACTID. ◦User selects single .dotm file for merge ◦Frm_MAIL_MERGE has either drop down to select CONTACTID or UNBOUND data field for user to type in CONTACTID number ◦User enters CONTACTID to be used for the mail merge ◦User selects SUBMIT ◦Application merges selected .dotm file with the information in table CONTACTS for the selected CONTACTID ◦Merged document is saved on the user Desktop as xxx.docx
I have two tables (Access 2010). One with a list of names (List1) and another with a very similar list of names (List2), but they differ in very small ways. For example, List1 might have John Smith, and List2 would have Smith, John L.; and Smith, John. List2 also has a unique ID associated with these names that I need to append to List1.
I need to design a form that will allow me to look up names in List1, and have it return all names that are similar in List2. I then need to be able to choose with record in List2 matches with the List1 entry (based on a few other columns in List2, such as birth date) and have the form add that unique ID to the List1 record.
I am having an issue with duplicates in my table. I have a table, called "Part Mods", that has about 12 fields in it. the first 2 of them are "Mod" and "Part Number". I have a form where someone can insert data into this table and what i want is for them to NOT be able to insert a record with the same Mod/Part Number combination of an already existing record. I have "indexed" turned on but I am pretty sure that is for every field.
I have been looking around for a while now to learn how to show in a subform similar records from the same table, and a way to link them together.
I work for a mental health organization and we have a call log database that we create a new record every time a person calls us - this allows us to track the outcome of these calls. Over the last 3 years we have about 10% repeat callers. I am trying to find a way, when creating a new entry, to see if this person has called before, and if they have, link their past contact (record) to the new contact (new record).
For example.
Caller: John Smith (555) 555-5555 (this is the new record)
in the subform, a list would populate all the 'john smith' records with an option to link or attach them to the new record.
We have an Access 2000 backend database resides in a network server drive while users connect to the backend tables with a mde file on their computers. All users have Access 2010 and the mde file was converted from the 2000 frontend with Access 2010.
The database have been running for a few weeks but recently users have been complaining about record lost on the backend table.We have also experienced one incident of data corruption where the main table could not be opened. After Compact & Repair, the table could be opened but a few records were showing xxxxx on all the fields and we have to delete and re-enter these records.Would they relate to using 2010 mde converted from 2000 frontend when the backend is still in 2000? I am a little nervous about converting both the frontend and backend to 2010 since I have heard various issues on the new version.
I need some to PLEASE point me in the right direction.
I have a database of magazine articles that are listed like this:
Magazine Date Article Time 2003-01 Iran War Time 2003-01 Pres Campaign Time 2003-01 Polution Time 2003-02 Senate Race Time 2003-02 New Cars Time 2003-02 Cold Wave Hits
I need to merge that down to look like this:
Time 2003-01 Iran War,Pres Campaign,Polution Time 2003-02 Senate Race,New Cars,Cold Wave Hits
I am using Access 2010.I have a table that I am using to pull my data from other tables and a query.My table is called tblMyData.One of the field names is level1. This field points to another table, and gives the user the choices for states (examples California, Texas, Maine).
Another field name is level2. This field points to another table and give the user the choice for type of customer (examples Business, Consumer) The field name level3 points to a query. The query, qryFinalChoice matches up the choices based on level1 and level2.for example, the user can pick California for level1, business in level2 and California Widgets or Los Angeles Clothing store in level3 (plus about 20 other choices).
If the user picks Texas for level1, business in level2, business in level2 and Houston rugs, or Texas style restaurant in level3 (or about 15 other choices).
-I am recording 1 for California on level1, 2 for Texas and 3 for Maine in level1. -I am recording 1 for business and 2 for consumer on level2.
The query qryFinalChoice has all the combinations for state, business or consumer, and lastly business name or consumer name.
-qryFinalChoice has line1 to match up the choices for level1 in my table. -qryFinalChoice has line2 to match up the choices for level2 in my table. -qryFinalChoice has line3 to match up the choices for level3 in my table.
I do not want any of the Texas business names appearing when the user picks California, or vice versa.
My SQL in my tblMyData tab for level3 looks like this: Select line1 from qryFinalChoice where line1=1;
I am able to get all the line items where California is a selection.How do I change my SQL to pull all the line1 choices where I have selected from level1, and all the line2 choices where I have selected from level2 automatically based on my pulldowns?
I have a split database.(Access 2010).Three of the linked tables are Appointments, Appointments_OLD, Appointments_NEW.
I want to use vba to rename Appointments as Appointments_OLD (replacing the current one) and to rename Appointments_NEW as Appointments (replacing the current one)
I have used:
'replace Appointments_OLD by Appointments, replace Appointments by Appointments_NEW DoCmd.Rename "Appointments_OLD", acTable, "Appointments" DoCmd.CopyObject , "Appointments", acTable, , "Appointments_NEW"
Unfortunately this just made Appointments and Appointments_NEW clones of one another - changes in one automatically occur in the other.
What I actually want to do is to swap the names round in the backend database while maintaining the right links to the frontend.
I am pretty new to the using of Microsoft Access. Part of my role at work is to mantain a register of contacts (Aprox 2000 records). When moving into my role there was already an Access Database in place that had been designed and created by another member of staff prior to leaving.
The database contains a wealth of information, however this is spread across MULTIPLE tables (23 to be exact)
I am trying to create a word document that is basically a mail merge in order for me to drag the contact information over to print. However all the information I require is not in one table. Word will only extract the data from one source.
I have 2 tables that have the same structure and same column names - Table A and TAble B. If field1, field2, field3, field4, and field5 match - I want to see that duplicates exist.
How do I do it with 2 tables? I know the query wizard check for duplicates can do it in one table - but I need to compare the two.
i have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.
I have a table with duplicate ids. When I have a forum popup another form, the forms are linked by ids. This works great except for one problem. There are duplicate ids. So if I pop up a form then it will go to the first record that that id exist. This is not good if you are going to add another record. Therefore, I need to create a unique variable for a record. The autonumber would be a great variable but it seems I cannot use it to filter. Unless someone can show me how. Is there another way to create a unique variable automatically for a new record?
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
I have a table with at least 13.000.000 records. There are many duplicates records... For example
ID Name Family mobile car number chassis Register_Year 1 Roy Jalbout 9999 123456/G ASF4546 2005 2 Roy Jalbout 9999 854658/G GRK554JFJD 2009 3 Tony Elishah 1234 854658/G GRK554JFJD 2012 4 Sam Markos 5478 854658/G GRK554JFJD 2014 5 Roy Jalbout 9999 123456/G ASF4546 2005
As you can see ID Number 2,3,4 have the same car but every one bought from another so it's not duplicates
The duplicates here is the ID's number 1,5. So how to remove the duplicates. I remember you i have at least 13.000.000 records. I try to make a query to find duplicates then i make a copy of the original table and than i should make a primary key then append the data from the original table to the copy table, but here i have more than one criteria
When I made a query to find duplicates the result was 680.000 records and every one have a minimum 2 duplicates an maximum 4 duplicates so it's about 2.500.000 duplicates records at least....
I want to enable a few fields when i click on the Add button on my form.I have change the onclick to Event Procedure to be able to add the following code
Code: Category_Desc.Enabled = True
My Category_Desc field get enable as expected but the Add button no long work
Code: Private Sub add_Click() Category_Desc.Enabled = True Me.Refresh End Sub
I have 2 Tables. Table "BOL" and Table "Containers". Both contain "Job_Number" and "Bill_of_Lading_Number" as a field. I would like to get a "Bill_of_Lading_Number" record from "BOL" and add it to "Bill_of_Lading_Number" in "Containers" table based on condition when both "Job_Number" are equal. I have tried two methods. One with DLookup and another with SELECT. Both work but with a small problem.
When I execute them, a dialogue box popup asking me to enter the "Bill_of_Lading_Number" value while the required value shows as dialogue box heading. This box shouldn't come up and I am not sure why this is happening. "varsJobCont" variable I get from another form and is "Job_Number" value that determines the record to be selected.
My Both Codes are:
Dim strBill As String strBill = DLookup("Bill_of_Lading_Number", "BOL", "Job_Number = " & Application.TempVars("varsJobCont").Value) DoCmd.RunSQL "Update Containers SET Bill_of_Lading_Number = " & strBill & " WHERE Job_Number = " & Application.TempVars("varsJobCont").Value Dim strBill As String Dim dbs As DAO.Database Dim rst As DAO.Recordset Set dbs = CurrentDb
[code]....
shows the dialogue box and the heading "bbb1055" is actually the correct value I needed to get. It should have been put in the other table automatically without the need to insert it manually again.
I am working on a SKU generator and I need to prevent duplicate SKUs, but each SKU is made from several different fields. i.e. Brand, Color, Design, etc.
I was wondering if Concatenating would be OK for this situation. I know concatenating usually results in redundant data and I try to avoid that.
I would like to just index (no duplicates) the entire record... is this possible? I can't just set that attribute to each field because there will be many duplicates on a per field basis. i.e. Brand will be duplicated every time I use the same vendor for a different shirt.
I can't just go back and address duplicates once they occur. I need to prevent them from occurring in the first place.
I have a form with a duplicate record button to allow records to be copied. I used this code (I think from Allen Browne), but it only copies the last record in the recordset?
If Me.Dirty Then 'Save any changes Me.Dirty = False End If If Me.NewRecord Then 'Check there is a record. MsgBox "Select a record to duplicate"
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.
I'm currently in the process of doing a an overhaul of the database including a table, some odd 17,500 records long.
What I need to do is to create a unique identifier from the existing part number, almost like an autonumber, but it only increments when there are duplicates. What I mean is that there would be for example, there would be five records of say, part 000135.
What I'm trying to get at is, how I would have to code to grab the existing part numbers (all formatted to be 6 digits), add an extra 2 digits to be its unique identifier, and then save it all to a Unique Identifier column, that I created.
An example of what I want is say, I have part numbers 000135 and 000136. The numbers would go up from 000135-01, 000135-02, 000135-03, 000136-01, 000136-02.... The dash is not as important as implementing the 2 digit unique identifier.
