Delete Records Except First
Jun 1, 2014possible to delete all records from one table except the first; Is that possible?
View Repliespossible to delete all records from one table except the first; Is that possible?
View RepliesI have a table InvPrice and Updated Pricing
Need to delete all records from InvPrice that Match UpdatedPricing
InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode
I have tried something like this...
Dim dbs As DAO.Database, sql As String, rCount As Integer
Set dbs = CurrentDb
sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode "
dbs.Execute sql, dbFailOnError
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
How can I do this?
Thanks,
Dave
I have a query of records that are not related to records is the secondary table. I want to delete these records but when I select the records and click delete I get the confirmation to delete answer to delete them and all the records disappear. When I run the query again, all the records are still there. Does anyone know what is happening? This is an access front end that connects to a SQL server database.
View 1 Replies View RelatedThis works perfectly.
SELECT DOCSADM_PROFILE.SYSTEM_ID, DOCSADM_PROFILE.AUTHOR, DOCSADM_SECURITY.THING, DOCSADM_SECURITY.PERSONORGROUP, DOCSADM_SECURITY.ACCESSRIGHTS
FROM DOCSADM_PROFILE INNER JOIN DOCSADM_SECURITY ON DOCSADM_PROFILE.SYSTEM_ID = DOCSADM_SECURITY.THING
WHERE (((DOCSADM_PROFILE.AUTHOR)=21941909) AND ((DOCSADM_SECURITY.PERSONORGROUP)=4038));
Now how do i tell this query to delete these selected records from security table....?
I have a survey form that has the questions on a subform based on the response to one of the survey demographic questions (via a query based on that response). The answers to the questions are on a sub-sub form. How can I get the program to delete the answers that were entered (they are related to the primary survey form with a common ID number) if the user changes the survey response.
In other words, if the user of SurveyID 5 decides that they want to fill out the list of blue questions rather than red questions, how can I delete all the answer blue records for SurveyID 5? Right now, it will display the red questions if you change to red and enter red answers, but the blue answers remain in the database, creating 2 sets of answers for SurveyID 5.
Any suggestions...if this is not too confusing?
Thanks!
Hello all:
Form created based on a query. When i go to the form, the add new record and delete record buttons are disabled.
I have checked and re-checked the form, tables, queries and all seems well.
Does anyone know how I can fix this problem?
Many thanks,
Dion
Good evening,
I am experiencing a problem with the delete buttons I have added into my forms. I have 3 different forms that operators use, I added a delete button using the wizard on each form to delete the data if the user makes a mistake. I have allowed deletes in the form properties. My problem is that the button only works on one of the forms. I have checked the code and it is the same on all 3 forms.
The record is not deleted, all that happens is that the vertical bar down the LHS of the form flashes black, and thats about it
Private Sub Command52_Click()
On Error GoTo Err_Command52_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Exit_Command52_Click:
Exit Sub
Err_Command52_Click:
MsgBox Err.Description
Resume Exit_Command52_Click
End Sub
Any thoughts?
The only difference is that one button is called Command52 and the other one is called Delete
Russ
Hello,
I have a table that has many duplicate records. I tried importing the table and setting the field to no duplicates but did not work. How can I delete duplicate records. The primary Id id fine I have another field that I can use. it is a BAC number that is repeated many times. Here is the table I need fixed.
Thanks
I am creating a web database where users submit entries using an online form. When they submit the entry, I am saving the entry date and time in a column called Timestamp. What I need to figure out is if there is a way to create some sort of expiration date, based on the Timestamp, that will automatically delete a record after it is 7 days old. Is there a way to do this? Thanks so much for any help that you can give.
View 5 Replies View Relatedhow do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.
View 5 Replies View RelatedHi,
I'm trying to delete say 4000 records, out of total 5000 records. I want to keep 1000 records based on the ID.
How can I write the SQL DELETE query, so that only 1000 records remain based on the query?
Thank you in advance for your great help.
regards,
Pranesh
Alberta, Canada
Hi
I'm trying to deleterecords from one table. In the query there are 2 tables both joined. I get an error message come on saying
"Specify The Table Containing The Records you want to Delete"
Below is the query in SQL
DELETE tblStockRequired.StockCode, tblStockRequired.StockQTY, tblStockRequired.OrderNumber
FROM tblOrderCompleted INNER JOIN tblStockRequired ON (tblOrderCompleted.OrderNumber = tblStockRequired.OrderNumber) AND (tblOrderCompleted.StockCode = tblStockRequired.StockCode) AND (tblOrderCompleted.QTY = tblStockRequired.StockQTY);
I want ot delete all records from tblStockRequired which match up with tblOrderCompleted.
Thanks in Advanced
Hi can anyone please help me out. How can I delete records from one table, where matching in a second table?
View 1 Replies View RelatedI have a table of ~165k records and need to delete records that are similar (2 or more records have the same lastname, firstname, dob). These records are not duplicates as other field values in these records are different. I've tried several sql scripts and Access methodologies but can't get valid results.
Thanks,
Phil....
Hi All.
I need to modify my database that give me ability to delete all record form table for last month. How to solve that problem?
Thanks.
On the below data I need to delete records where dep is blank or the same for the same ID. So I need to find and del recNr. 1,4,5. Any help will be appreceated!Thanks
recNr IDDep
1 ABX
2 ABX 1234
3 ABX 1258
4 CDE
5 CDE 7895
6 CDE 7895
I have a form with a listbox on it and I am trying to make a button that will delete the selected records from a table. The List box is filled from a query. Any help would be very appreciated.
Table to delete records from = tbllinkPersonel_Training
listbox = lstTraining_In
_______________________________________________
Private Sub cmdDelete_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim var As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tbllinkPersonel_Training")
If IsNull(Me.cboTraining) Then
MsgBox "No Class selected...", vbExclamation
Exit Sub
End If
For Each var In Me.lstTraining_In.ItemsSelected
With rs
.Delete
End With
Next
MsgBox "Deleted Successfully...", vbInformation
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Me.lstTraining_In.Requery
Me.lstpersonel.Requery
End Sub
____________________________________________
Thanks, Michael
Is there a way upon selecting a record (in a continuous form db) I could right click and delete the record? I am able to cut, copy and paste but unable to delete. Any suggestions?
Thanks.
Help please!
I have a form (frmHalfTab) built on a table (tblHalfTab2) that has existing data in it. In the very near future, I will be uploading additional data to this table via excel cut and paste. What I want to do is search for and eliminate duplicate records that have duplicate data in TWO fields, (MemberNum) and (MBRDrug), so that if MemberNum is "10" and MBRDrug is "Zzz" for more than one record, the duplicated records need to be deleted, OR a check box needs to be checked automatically for all the identical records, the original and the duplicate(s). I wanted to put this behind a button, if possible.
Now, I have created a duplicate query that will find the duplicated records, but I'm not sure how to use it to get rid of the duplicated records or check the checkbox appropriately. Should I ditch this query and choose a different route, or am I on the right track? Thanks in advance for your help.:confused:
Hi,
I have a question.
How can I make undo delete records?
When the user select all the records, and then click "delete" key. Then it will delete all records.
I want to setup that there must be at least 1 record left.
If they select all the records and delete all, then it will undo delete all records. We have to keep at least 1 record.
What command to undo delete all records?
Please let me know, thanks.
I have 2 forms:
one called basic the other called advance
how can i automatically delete records from the basic table, when students are advance.
thanks 4 helping
Hi
I am in the process of designing a sports database and have come across a problem that I can not seem to fix.
I have 3 tables that are linked, they are Customer, Program (this is the customers exercise program) and Health (the customers health records).
I have tried to set up a query that deletes a customer if a command button is clicked. This works find as long as the table I am deleting the data from is not linked to another table.
However when I try to delete a customer record it says that I can't as the data is linked and I have to delete the relationships first.
The only way I can think of doing this is to setup a query that deletes all the data in each table at the same time. I can not get this to work though.
Is this a problem that can be solved or will I have to change the way my relationships are set up?
I would appreciate any help or suggestions.
Thanks
I am having difficulty deleting records in a linked DBF (standalone) table. The table links fine. I can run a delete query and the records appear to be deleted when I view the table from within Access. However, when I view the table outside of Access, the records that I thought were deleted are still there. The only way I can actually delete the records, is to import the table, delete the records and then export the table as a new DBF.
Can someone tell me why deleting from the linked table isn't working?
Thanks,
Sup
Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?
Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."
Then it will let me delete one record out of the selection.
I am the only one in that particular table at the time I am trying to delete the record.
Thanks in advance for your help...
Hi,
I'm getting really mixed up here, Im trying to delete all records in table products based on the following query;
SELECT products.*, tbl_stage_product_update.[Catalogue No]
FROM tbl_stage_product_update INNER JOIN products ON
tbl_stage_product_update.[Product Code] = products.pID
WHERE (((tbl_stage_product_update.[Catalogue No])="0"));
I've tried using the exists keyword with the previous query as a sub query, but it seems to delete all records form table products.
can anyone offer any advice.
TIA.
Mark