473,573 Members | 2,757 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Deleting a similar record

3 New Member
Hi,

I have a simple Access Table (LstFax) with "REF" (Client NAme) and "ADDR" (Fax numbers) fields that has been generated by SQL from a custom query form. There are duplicate fax numbers for clients from the same office that I want to delete.

I have a sub to try and delete these similar field based on some code I found on the net using From ... From syntax but am having some problems. I can't find syntax to suport this - maybee its just not available for access

Any help much appreciated. The "REF" field is not important. I would like to keep it in SQL without new tables / queries to save space - keep it seemles for users.

Sub RmvDupRec(TabNa m As String, TabFld As String, FldUnq As String)

Dim SQLstr As String

SQLstr = "DELETE FROM " & TabNam
SQLstr = SQLstr & " FROM " & TabNam & " As T1, " & TabNam & " As T2"
SQLstr = SQLstr & " WHERE T1." & TabFld & " = T2." & TabFld
SQLstr = SQLstr & " AND T1." & FldUnq & " > T2." & FldUnq & ";"
MsgBox SQLstr

DoCmd.SetWarnin gs False
DoCmd.RunSQL SQLstr
DoCmd.SetWarnin gs True

End Sub

the SQL looks like

DELETE FROM LstFax FROM LstFax As T1, LstFax As T2 WHERE T1.ADDR = T2.ADDR AND T1.REF > T2.REF;

Thanks

KPoe
Mar 19 '08 #1
3 1400
kavitadatar
2 New Member
Try the command as
DELETE FROM LstFax As T1, LstFax As T2 WHERE T1.ADDR = T2.ADDR AND T1.REF > T2.REF;
Mar 19 '08 #2
KPoe
3 New Member
thanks kavitadatar ,

No such luck. Error 3128 - specify table containing records you want to delete

I am wondering if I have to go about this a different way?

KPoe
Mar 19 '08 #3
KPoe
3 New Member
After a bit more searching, I managed to get the following solution.

First I created another table containing the duplicate fax numbers. The SQL is

SELECT DISTINCTROW LstFax.REF, LstFax.ADDR INTO LstTmp FROM LstFax LEFT JOIN LstFax AS LstFax_1 ON LstFax.ADDR = LstFax_1.ADDR WHERE (((LstFax.ADDR) In (SELECT [ADDR] FROM [LstFax] As Tmp GROUP BY [ADDR] HAVING Count(*)>1 )) AND ((LstFax.REF)>[LstFax_1].[REF]));

I then used this table to delete duplicate records in the origional table.

DELETE DISTINCTROW LstFax.* FROM LstFax RIGHT JOIN LstTmp ON LstFax.REF = LstTmp.REF;

Hope this helps someone

KPoe
Mar 26 '08 #4

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

Similar topics

0
1117
by: Andre Doreid Berro | last post by:
Greetings All I am working on an Access database of contacts. I have plenty of records but plenty of them are almost duplicates. Data in some fields are misspelled and entered as completly different records. I wonder how could I clean the database in a way I have single record of contact information for each contact. Example: Record 1
1
6099
by: Mark | last post by:
This question refers to a main form with a continuous form subform. After an error occurs after entering several records in the subform, how can I delete all the data in the main form and all the records in the subform? I have tried undoing both the main form and the subform and I have tried deleting the record in the main form. Thanks! ...
3
2748
by: Nathan Bloom | last post by:
Hi, I have a data entry form (access 2000) that also allows the user to add, update, and delete records from the form. The Delete action is carried out in an event procedure and has the following code: Private Sub Command28_Click() On Error GoTo Err_Command28_Click
1
2384
by: KC | last post by:
Hello, I am using Access 2002. WinXP, Template from MS called Orders Mgmt DB. I have tweaked this DB to work for our small co. It has worked pretty well up until I made the mistake of deleting about 80 records from the Orders table. 80 out of a 1000 records. Now our data entry form shows our customer addresses, but not customer order...
3
3723
by: vishnu mahendra | last post by:
hello to all, Can any one please tell me how to delete a record in a binary file. thank you in advance, vishnu
46
4397
by: DP | last post by:
hi, i've got a form, with a subform in it. i've got a delete button in the subform. the code i;ve got is; Private Sub cmdDeleteRecord_Click() msg = "Are you sure you want to delete this film rental record?" Style = vbYesNo + vbQuestion + vbDefaultButton2
19
2131
by: MaXX | last post by:
Hi, I hope I'm not OT. I have the following issue: I want to delete a record from my db with a php script. Let's say I'm auth'd and I want to delete the record id 440. With a simple form (get or post), I send the id to my script and delete the record (DELETE FROM table WHERE id=some_validated_input). The problem is if I'm a nasty guy I...
2
1110
by: Armando | last post by:
I created a report to display all the tables/fields in a db, and all the code is in the report's module - for easy one-chunk portability to other apps. In the OnOpen, I create a temp table, fill it from the tabledefs collection, then open the report. Works fine. In the OnClose, I want to delete the table, but the report still has hold of...
4
2384
by: sphinney | last post by:
I'm not exactly sure how to start this post. My question is pretty simple, but it will take a little bit of context before I can state it. (And thanks in advance for taking the time to read this!) Context: What I'm essentially trying to do is create a poor man's document imaging system in Access 2007. I have a database that contains forms and...
1
1714
by: Kyosuke18 | last post by:
Hi everyone, I have a problem in deleting a data that is connected on the database.. I tried this code but it shows me an error: Run-time error '-2147217900(80040e14)': Syntax error in string in query expression 'ID=". Here is the code that i did: Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim ab As String cn.Open...
0
7784
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8033
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8206
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7796
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8077
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5294
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3734
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
1044
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.