Modules & VBA :: Code To Consolidate Two Tables And Remove Duplicate Entries
Oct 3, 2013
I have two tables in my Access database. Table 1 has three columns, First Name, Last Name and Phone Number. Table 2 has just 2 columns, First Name and Last Name. What I would like to do is write a VBA macro to consolidate Table 2 into Table 1 and insure any duplicate First Name-Last Name combination is removed.
Table1
Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Table2
Code:
F.Name L.Name
Albert Black
Larry Miller
John Bush
Table1 Post-Macro
Code:
F.Name L.Name Tel.N.
John Smith 12345
Larry Miller 12344
Tony Woods 12333
Albert Black
John Bush
I have a table which has duplicate records so I want to write down the code so that when the user click on a button then it should remove the duplicate records from the table.
I am using information pulled from a separate database to create a new database for mail merger purposes. I have created delete queries that go out and delete specific records from the new database to help us get the proper list before setting up the mail merge. However, now I am stuck. We have customers that have multiple accounts with us that are qualified for this mailing, yet each customer needs to receive only one mailing. The table looks like this essentially. Table 1id statCode acct custNumber* addy-etc. *This is the field in which we can find exact duplicates. What I need to do is remove the duplicate entries and leave at least one of the entries within the table. It does in fact need to remove the whole record, but as I said leave at least one of the records behind. Can someone please help me to find a solution to this. I guess I should add that I am not very good with Access and I use it very sparingly. I assume that the above can be accomplished using a SQL statement. Any help will be greatly appreciated. LR**EDIT** I also needed to add that I do need to exclude 000 00 0000 from being removed because of duplication.
I have imported a table from Excel. The table contains a long list of names and there are quite a lot of duplicates. How do I remove the duplicates so I can import the clean list into a value list in Access ?
I am attempting to filter the data in query design such that it will not return anyone in the database has there name present more than one time.
For example, in the image below under the Owner_Name field there is "Abbot, Mark C Etux Wanda C". This name appears twice in the database. My criteria is the name can only appear once, therefore this record would not be returned after the query. In query design I would like it to return only name records that appear once in the entire database if there is a way to structure that.
I have one Master database and multiple child databases in a folder. Each of these databases (both Parent and Child) have identical table called "source". I need a VBA code which will consolidate tables of child database into the Parent database table.
I have a few duplicate customer queries (different duplicate fields etc). I would like to construct a form to make it easy for an end user to remove the duplicates.
The main problems are...
1. the newer customer is to be deleted
2. any/all orders from the newer customer will be moved to the older customer before deletion.
3. preferably an easy way to switch between the duplicate queries in the same form, though if this is a problem I could just have one small dialog box/form, linking to 3 different duplicate query/forms.
I think other changes between the customers will be easy enough to just do in the subform, i.e update email address/tel/status/salesperson etc. These can just be edited directly?
But the transferring of orders can't be edited directly because there may be several orders and could be errors etc.
So all that needs to be updated is [CustomerNumber] in tblCustomerOrders. All orders for the newer customer to have the CustomerNumber field updated to match the older customer. Then the newer customer is deleted.
I would just have a button with a message box to run the code, update CustomerNumber/s and delete record.
I have made a crosstab query that is sporatically making duplicate counts. I'd like to know how to fix the problem, but more importantly - I'd like to know why and how my query is giving me these results so I can avoid making this same mistake again.
The relationship is set up so that all records from one table are returned, with only matching records from the other - so no problems that way as far as I can see. This is my crosstab query that is giving some counts of the same record as being matched sometimes two, three, or even four times:
TRANSFORM Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]
SELECT [Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID]) AS [Total Of DB_ID]
FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address
WHERE (([Testing DB].[Window Length]) Is Not Null))
GROUP BY [Testing DB].State, [Location].[Size]
ORDER BY [Testing DB].State, [Location].[Size]
PIVOT Format([Date],"mmm-yyyy");
When I do a simple query for a list of the records being counted by this query, I can see the duplicate entries and can eliminate them simply by adding "Distinct" to the select statement - but I haven't had any luck adding any kind of distinction with this crosstab that will do the same.
How can I eliminate duplicate counts of records in my crosstab? If anyone has a suggestion, it would be much appreciated.
I'm trying to create a database that is going to be used to deliver some work to several of our users. Each time they open a specific form they'll be delivered a job.
Now it works like this, the user gets an ID_JOB from queue
In the form they get all the all the work orders with that ID_JOB, the thing is i'm getting users with same duplicate orders cause i can't update the locked efficiently.
Regarding the users, the database is split, multi-user, with >30 simultaneously
I'm been trying to use dao.recordset, with transactions to try and reduce the duplicate orders.
I would like to put a message on the screen for informing the user - mostly during some procedures that might take time or when, after a warning, I want to go on with the program but without action from the user.
Msgbox is obviously not the one I'm looking for as it needs customer acknowledge. Is there a solution for this task?
I have a form based on a query and have written code to display a msgbox if a duplicate entry is inputted in the NHS_Number field in the form. I have also added code "me.undo" to clear the form so that it is not saved. I am looking for a piece of code that will display the duplicate record. Here is my code thus far:
Private Sub NHs_Number_BeforeUpdate(Cancel As Integer) Dim dbs As Database Dim rst As DAO.Recordset Dim x As Integer
How to give a VBA code for duplicate records is without entry in Subform datasheet. I just used in the screenshot code but that is not working properly. how to change a vba code for without entry duplicate names.
I need to consolidate mailing addresses so that many records are grouped to one invoice based on the address they fall under. I am currently using an update query to change addresses that are similar to match exactly. Is there a way to make it so the addresses group together based on similarities rather than exact matches? For example I have two addresses:
1301 W Johnson Street Phoenix, Arizona 85725 1301 West Johnson St Phoenix, AZ 85725-7201
These are the same property however without cleaning them up manually to match, two invoices will be printed. Is there a way to consolidate these?
The above table suits me well for data entry.For analysis reasons, I want it to have another table or query or something (let's call it "Teacher Info") that looks like this:
Is there any way that I could do this? I have been struggling with this for a while.Just so you know how this data is connected - At the moment, I have a form where I put in new Teacher information. I have a subform attached to that, where I put the student names and numbers. This way, the students are linked with their student #s, and each of them are linked to a teacher. Unfortunately, the table ("New Teacher Registration") that this creates looks like this:
id....Teacher Name 62...... Alice (+ tab).........A.........432 ...................B.........674 ...................C.........875 18.......Katie (+ tab).........D.........934 ...................E.........345 4........Dan (+ tab).........F.........134 ...................G.........734
I created the "Student Info" table (above) from this to work with data entry (drop-downs and such). Now I'd like to create a "Teacher Info" table (above) for analysis.
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
hello, Currently i have a database that holds information about computers. Each computer has a Service tag and is associated to a person. I need to be able to prevent duplicate service tags from being entered. I am using indexing and don't allow duplicates from the table design view. But, if a duplicate service tag is indeed entered, i want to inform the user who that service tag currently belongs too without having to search through the DB manually. I need then to give the user the option to delete the duplicated record they just entered or change the service tag that they just entered. Thanks. Jared
I need to remove all duplicate records in a table. These are records that have matching Contract and Order fields. I can't do a DISTINCT query, because that still leaves one record. Thanks.
I've set up a booking system and am looking to stop the system recording double bookings.
I've 3 fields, RoomID, Date and Time that need to be searched, however I can't just simply set the properties to No Duplicates (as this would mean a booking cannot be made on the same day at 9:00 and 12:00 for example).
I am running a query to find out duplicate in two fields in one table. Postcode and Address Line 1. Can anyone tell me the formula for only picking up the first 6 charaters in the Address Line 1 field??? I cant remember how to do it!
Hi all, I am currently using this code in the before Update event of the Surname Textbox on a form. The first Textbox is FirstName. The second one is Surname.
If (Not IsNull(DLookup("[FirstName]", _ "Employee", "[FirstName] ='" _ & Me!FirstName & "'"))) And (Not IsNull(DLookup("[Surname]", _ "Employee", "[Surname] ='" _ & Me!Surname & "'"))) Then MsgBox "Someone already exists with the same name! Please check for duplicates", vbCritical, "IPDMS" Me.Undo End If
However once the user has entered the surname and tries to save the record the MsgBox is coming up. Basically what is happening is the first name is coming up as the duplicate entry only. I need to prevent the whole name (first name and surname together) from being duplicated.
I have a booking form database With a date field. I want users to be warned when they try to enter a date that already exists in the database in order to avoid double booking:confused: .....