473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Vba delete duplicated value in table

Dear All,
I need your help to solve my problem to remove the duplicated value in my table by using VBA if you check my image here is some times I may add again record but once I click on the button will remove duplicate data.
thanks for help

Attached Images
File Type: jpg dup.jpg (73.9 KB, 1308 views)
Apr 23 '19 #1

✓ answered by twinnyfo

Again, VBA won't delete "duplicate" data because it doesn't know which is a duplicate and which is the original. You risk deleting both. However, based upon what you explain, you would have to prevent your upload from adding duplicate data in the first place.

You could only approach this by importing the data line-by-line and comparing. But, again, that discussion would be more suitable for a different thread.

10 2964
twinnyfo
3,653 Expert Mod 2GB
You can create a "Find Duplicates" query--which is built into the Access system. Go to Create > Query Wizard > Find Duplicates Query Wizard.

You will have to manually delete any duplicates, as Access cannot "delete a duplicate" record, because it does not know which one is a duplicate and which one is the original.

However, as good advice, you may want to address how you are adding records to your table, so that you can programmatically prevent duplicates from being created. That is a question for another thread, though.
Apr 23 '19 #2
thanks for your advice, but I have one problem is: I upload excel file into my DB some times I forget to upload again, so the best way is when I upload excel file then after append data into my table vb will delete duplicated data, hope someone can help me to solve my problem
Apr 23 '19 #3
twinnyfo
3,653 Expert Mod 2GB
Again, VBA won't delete "duplicate" data because it doesn't know which is a duplicate and which is the original. You risk deleting both. However, based upon what you explain, you would have to prevent your upload from adding duplicate data in the first place.

You could only approach this by importing the data line-by-line and comparing. But, again, that discussion would be more suitable for a different thread.
Apr 23 '19 #4
thanks again for your advice, I'll try to do your way
Apr 23 '19 #5
zmbd
5,501 Expert Mod 4TB
adamdaban,
Does your imported data have a primary key?
I import a lot of data from various sources and these are usually excel files or text files where the record has a unique key which makes it quite easy to work with as one only need to search the record set for this value.

Would you mind posting the VBA-Script you are using for the import? Please remember to format the script using the [CODE/] formatting tool.


I have a few other ideas such as using a temp table (I usually avoid these due to bloat) or using a few other post import methods; however, a tad more information would be helpful...
-Z
Apr 29 '19 #6
Thanks so much for carrying about my issue, actually I have no VBA script!
May 11 '19 #7
  • Remove Duplicates Using Row_Number.
  • Remove Duplicates using self Join.
  • Remove Duplicates using group By
    Expand|Select|Wrap|Line Numbers
    1. SELECT FirstName, LastName, MobileNo, COUNT(*) as CNT FROM CUSTOMER GROUP BY FirstName, LastName, MobileNo; HAVING COUNT(*) = 1
    .
May 16 '19 #8
NeoPa
32,556 Expert Mod 16PB
Not sure what this is for. It's not a DELETE query at all, and it's not even an updatable SELECT query that might be converted to a DELETE one.

Confused :-(
May 16 '19 #9
twinnyfo
3,653 Expert Mod 2GB
Yeah, I almost deleted this post myself as it did not seem relevant--not to mention a couple other spurious posts by the same user....
May 16 '19 #10
zmbd
5,501 Expert Mod 4TB
I read this at 5:30am local and thought maybe I wasn't awake yet... so that's a third person that didn't follow the post... I almost deleted it then.

Post also basically restates the "find duplicate wizard" suggested buy TwinnyFo.

I do have a kludge work around for OP as for the import that is causing the heart burn...

Ideally, OP would create a unique key in the excel table, not easy, but best practice...

However, I suspect that adamdaban is using
Ribbon>External Data>Import & Link>Excel

Before import prepare is dataset in Excel
Open the Excel workbook of interest
Select the range of data to import
Name the range in the excel workbook, save, close



Open Access
Ribbon>External Data>Import & Link>Excel
then select the range name to import



If adamdaban names each range by date then OP will have a good idea what data has already been imported.

Another option is to simply link to the Excel workbook instead of importing the data. Can be a bit of a kludge for the SQL; however, one can write SQL against the linked table. I do this for a database where I'm using Excel as the data entry form (yes, I know...) because they want a non-normalized format that I cannot easily create with a form.
Attached Images
File Type: jpg Excel972533.jpg (39.3 KB, 1003 views)
File Type: jpg access972533.JPG (34.7 KB, 1021 views)
May 16 '19 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: James A | last post by:
I've been searching around for a while now trying to find how I can delete a table through vb. I'm using access2000. I use this to make a table: CurrentDb.Execute "CREATE TABLE " &...
4
by: Naeem Bari | last post by:
Hi, I am using postgres 7.4.5 on Redhat Enterprise Linux 3. My background is really on Oracle, and I am porting a largish database over to postgres. Here is my problem: On oracle, I...
2
by: jkw | last post by:
Hi - I created a linked table via ODBC to a SQL Server database. The table in SQL Server contained over 12,000,000 records. Later, I started a "Delete" query. When I realized that I was deleting...
8
by: starman7 | last post by:
i have a table with objects in categories and their positions. there will be several rows with category 400, and they will have various positions, i want to delete only the row with the lowest...
6
by: Niyazi | last post by:
Hi all, What is fastest way removing duplicated value from string array using vb.net? Here is what currently I am doing but the the array contains over 16000 items. And it just do it in 10 or...
1
by: R. Vince | last post by:
I have a table (tableA), with a data field (saved as a VARCHAR in YYYY-MM-DD). I have a second table, tableB, which has a field with an int field which refers to the ID of tableA (or is null). I...
3
Fary4u
by: Fary4u | last post by:
Hi is any body find out where is the problem is ? i've got product value in 2 different tables it's remove from 1st table but how can i remove it from 2nd table PROBLEM IN LINE 23 code is...
12
ahmedtharwat19
by: ahmedtharwat19 | last post by:
hi, every one for delete duplicated rows can any one up to us an example to see that because i`m beginning to ms access and i have a problem about that thank you for all abo mroan
0
by: Duke Slater | last post by:
I have a user who has created an Access 2007 database with one linked table to SQL Server 2005. He wants to purge the linked table and repopulate it, but gets the 3086 error when running a delete...
4
by: bouldergirl | last post by:
I have 2 tables, Table 1 and Table 2 (see attached images)....Although in the example tables (attached), only a few records are shown, they both have about 1000 GRID values: Table 1 contains...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.