473,890 Members | 1,192 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete TOP X number of records

Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks
Nov 12 '05 #1
5 11107
PaulMac wrote:
Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks


Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.

--
But why is the Rum gone?
Nov 12 '05 #2
thanks for responding Trevor.

delete top 3 * from mytable...doesn 't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...

Trevor Best <nospam@localho st> wrote in message news:<40******* *************** @auth.uk.news.e asynet.net>...
PaulMac wrote:
Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks


Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.

Nov 12 '05 #3
pa********@hotm ail.com (PaulMac) wrote in
news:1e******** *************** ***@posting.goo gle.com:
delete top 3 * from mytable...doesn 't work (I'm using an .adp if
that makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i
want to delete is a variable that is populated from within code...


First off, TOP 3 is going to be randomly selected unless you include
a non-ambiguous ORDER BY clause (i.e., one that can produce only one
definitive sort order for any group of records).

If you have a variable N for TOP N, you have to do one of two
things:

1. construct the SQL in code and execute it in code.

OR

2. in code, save a stored querydef with the appropriate N value and
execute that.

I see absolutely no reason to do #2 and would also do #1.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #4
PaulMac wrote:
thanks for responding Trevor.

delete top 3 * from mytable...doesn 't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...


ADP makes a difference, use a stored procedure.

create procedure DeleteMyTopN
@paramHowMany int
as
set rowcount @paramHowmany
delete from MyTable

You can call this using a adodb.command object and set the parameter.
Nov 12 '05 #5
pa********@hotm ail.com (PaulMac) wrote in message news:<1e******* *************** ****@posting.go ogle.com>...
thanks for responding Trevor.

delete top 3 * from mytable...doesn 't work (I'm using an .adp if that
makes any difference)...i definitely tried this approach

the problem with the query approach is the number of records i want to
delete is a variable that is populated from within code...

Trevor Best <nospam@localho st> wrote in message news:<40******* *************** @auth.uk.news.e asynet.net>...
PaulMac wrote:
Hi,

Hopefully this is a simple one.
IF x =3

then i want to delete the first 3 records in tabley

(Similar to a TOP select statement: Select Top 3 * from tabley)

How can i do this?

thanks


Without trying, dunno: delete top 3 * from mytable?

If not then make a query, e.g. qryMyTop3 as select top 3 blah blah then
use another query to delete * from qryMyTop3.


If you want to change the number of records that get deleted each time
(so say X can take any non-negative value), you would need to create a
string to build the SQL statement and then execute it.

Sub DeleteNRecords( byval intNumRecords As Long)

Dim strSQL as string
strSQL = "DELETE TOP " & intNumRecords & " FROM MyTable ORDER BY
MyField;"
CurrentDB.Execu te strSQL, dbFailOnError

End Sub
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5003
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since records to be imported can have duplicate records of the composite key I need to clean all but one of the...
4
6168
by: KT | last post by:
Is there any one click solution that would do the trick? I would like to create a button, so the person who maintains the database can perform clean up work to delete duplicate records which contain same information in the ID field and the account number field once a week. Thanks in advance! KT
3
1445
by: jeff | last post by:
Dear all, i want to delete all records in a table (ms access database) now my method used is not good, i have to loop all record in the datatable and delete it one by one. however, i can't :
16
16198
by: Theodore70 | last post by:
I am trying to delete duplicate records in an access table using VB. Not a programmer here, but this was one of the codes that I found on the web. I made some modifications to it, but it still did not work for me. Public Function DeleteDuplicate() Dim db As DAO.Database Dim qdf As QueryDef Dim strSQL As String Dim strEmail As String dteEmail = DMax("", "tbl_email") strSQL = "Delete * FROM tbl_email" & _
1
8605
by: nico3334 | last post by:
I am trying to delete all records from all tables in Access through VB coding. I would like to do it dynamically in case new tables are added. Here is what I have so far: I have a form with a command button. When it is clicked, here is the code: Private Sub Command0_Click() DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM ;"
5
14427
kcdoell
by: kcdoell | last post by:
Hello: I am trying to write a code that will delete all records found in my DAO recordset Below is the code I have so far: 'Procdure to give the user the ability to delete all records 'for a predefined recordset from the tblStaticAllForecast table LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _ " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
7
3032
by: jedgretzky | last post by:
I am having trouble creating a query that will delete duplicate records. It seems to me that this isn't that hard of a query, but I just can't seem to get it working. What I have is a table that has an ID, which should be unique. Unfortunately there have been entries for the same ID where the Total Value row has been increased. What I want to do is delete the records that have the same ID number but the lower total. Here is one of the sql...
6
5946
by: Dilip1983 | last post by:
Hi All, I want to delete duplicate records from a large table. There is one index(INDEX_U1) on 4 columns(col1,col2,col3,col4) which is in unusable state. First of all when i tried to rebuild index it showed error as unique key violation. So i want to delete duplicate records for col1,col2,col3,col4 combination. How can i delete the duplicate records from this large table?
5
8171
WyvsEyeView
by: WyvsEyeView | last post by:
Upon clicking Delete to delete the current record on frmTopics, I want several things to happen: 1) Display a custom message rather than Access's standard "You are about to delete n records" one. 2) Delete the record. 3) Delete all related attribute records. 4) Go to a new record. Here is my code: Private Sub cmdDelete_Click()
4
3835
by: mark007 | last post by:
I have two tables invoice and so_salesorderdetail1, I want to delete those records from invoice which are not in so_salesorderdetail1. these tables don't have any unique key so I am trying to make a composite key. I wrote the following two different queries but it doesn't seem working. 1. DELETE FROM INVOICE WHERE NOT EXISTS (SELECT SO_SalesOrderDetail1.SalesOrderNo, SO_SalesOrderDetail1.ItemCode FROM SO_SalesOrderDetail1 INNER...
0
9978
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 usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10812
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10462
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9630
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7169
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5848
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6041
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4270
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3276
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.