Queries :: Deleting Data From Selected Records
Sep 23, 2014
My Membership Database includes 3 fields in the main Table which I need to clear prior to the new subscription year, but on a selective basis.The fields are R/N for (Renewal or New), a subscription date and an amount field (Currency).
At the due date I need to clear all 3 fields except where N/R=N AND date >01/01/2014, when data in all 3 fields is to be retained.I have tried several SQL codings to achieve this but have been unsuccessful so far.
View Replies
ADVERTISEMENT
Feb 27, 2014
I have imported a large number of emails into a table tbl_requests.
I had intended to have unique file tbl_requests.date_opened unique, but have ended up with a lot of duplicate records (i.e. tbl_requests.date_opened is not unique !). How to delete any duplicates? I have 15,000 records...
View 3 Replies
View Related
Apr 1, 2013
I am trying to create a Delete query.
I am trying to delete a specific part from multiple BOMs in my database.
I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.
So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.
I am able to select the records with:
Code:
SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));
Now how do I delete these same records.
I am getting error saying I have to select a table to delete from....
View 11 Replies
View Related
Dec 31, 2013
I have a query that selects records based on certain flags in each record. I can view the record in datasheet view, but I need to be able to edit the records selected by the query using another form. Is there any way to automate this process?
View 14 Replies
View Related
Mar 19, 2014
Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).
If I use the following code:
Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem
I get an error stating Object doesn't support this property or method.
View 7 Replies
View Related
Dec 11, 2014
I'm selecting multiple records from a list, now I would like to export these selected records to excel.How do I do this?
View 2 Replies
View Related
Aug 24, 2015
I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code:
DoCmd.RunSQL DelSummarySQL
Here is my full code
Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date
[code]...
View 2 Replies
View Related
Apr 12, 2008
I have two tables.
1. Persons (list of persons)
2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
- Roy
View 1 Replies
View Related
May 17, 2013
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
View 1 Replies
View Related
Aug 4, 2005
I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?
I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.
View 3 Replies
View Related
Nov 11, 2005
A strange request but I hope someone can help with this one
I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number
e.g On a form text box I enter the number or records to be deleted (e.g.6000).
The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:
View 1 Replies
View Related
Feb 1, 2006
How can I go about deleting records fully? So that there autonumber, etc can be used again without access moaning.
Thanks :D
View 1 Replies
View Related
Mar 14, 2006
I have a query (Query1) which returns results In one field recordID.
How do I delete records in a table where the RecordID is in the list Query1.recordID?
Thanks
View 4 Replies
View Related
Jan 25, 2008
Hi,
how would i go about deleting a set of records? i can get a list of records together in a query taken from 4 tables and would need to, if necessary, delete a single line. not all information needs to be deleted from all 4 tables though? the info to be deleted would only be deleted from 1 or 2 tables being the last 2 in the relationship.
i guessed it might be an append query but im not too familiar with them.
any chance of a possible suggestion?
many thanks,
NS
View 2 Replies
View Related
Oct 17, 2007
I am having trouble with my autonumbers when i have to delete a record.
Can anyone help me out with auto numbers?
View 9 Replies
View Related
Dec 14, 2006
Hi there, i have a master reset to delete all the data in the database. Although, as there is a username and password entry to get into the admin module, i wish for one entry in the table participantTable to not get deleted (to save one password/username so its possible to log into the admin module after the reset). The code below will delete everything, how can i change it so it keeps the first record in the table, and then deletes all the other records after.
DoCmd.RunSQL "DELETE * from participantTable"
Thanks in advance
View 1 Replies
View Related
Jan 25, 2006
quick question guys...
if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted..
example
sales order 1001
sales order 1002
if i delete all the records on the table and clean it all out
the next new autonumber will be 1003...
i want it to be 1001 again...
and yes its set to an index of no dups
View 1 Replies
View Related
Mar 19, 2007
Hello all,
I have 3 tables:
1. customer
2. DVD collection
3. Borrow
The aim of this system is to create a home borrowing system.
The customer table has a one to many relationship with the borrow table, while the dvd collection has a one to one with borrow. I can create a new book with no problem, and even view these on a form. Here my proble arises.
I ahve another form to return a DVD. I view the records from the borrow table, and once found what i want there is a button which deletes the record from the borrow table. Sadly, when i press this button, not only does it delete the record in the borrow table, but it will also delete the record from the DVD collection table.
I have tried everything i can think of, and i don't want to change to change the design as i have found this mnethod very easy to generate reports and the like. Any help??
Thanks
EDIT: the button i used was from the wizard that comes up when you create a button.
View 2 Replies
View Related
Nov 24, 2004
Looking for hint how to avoid systems message box ("you are about to delete n records...yes, no") when deleting records from the table.
respect
View 3 Replies
View Related
May 18, 2005
Hello.
I am using a large database, which usually works fine, and is set to compact on close.
Occasionally it has been losing a lot of data in the main table, probably when it compacts, down to a round number of records. This time it left me with 10,000 records exactly. (It has been different round numbers before)
Does anyone have any ideas as to what is causing this?
Thanks.
View 14 Replies
View Related
Jan 25, 2006
quick question guys...
if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted..
example
sales order 1001
sales order 1002
if i delete all the records on the table and clean it all out
the next new autonumber will be 1003...
i want it to be 1001 again...
and yes its set to an index of no dups
View 1 Replies
View Related
Dec 27, 2007
Hi,
I am using Vb6.0 as a front end and msaccess as the db.
i want 2 delete all the records in a table "Register" by clicking a menu
The connection is made using ADODB
Till now i hv the code
Private Sub mnudel_Click()
Docmd.SetWarnings False
Docmd.RunSql ("DELETE * FROM Register;")
Docmd.SetWarnings True
End Sub
But when i execute this a error msg displays
Run time error "424"
Object required
Whats the problem in me
plz help me to come out from this...
View 2 Replies
View Related
Apr 11, 2007
i know this must be a piece of cake but i'm new to Access!
Anyway, am using a delete query and want an expression that i can use in the criteria to allow me to detect (and delete) records that are 5 years old. i used Date() - 1825 but was wondering if there was any other way or function that would allow me to acheive this. any help regarding this would be appreciate! hope someone can help.
View 4 Replies
View Related
Apr 30, 2008
I am having problems deleting various rows from an SQL table, the message coming up is "The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (xxrows). Any Suggestions?
View 1 Replies
View Related
Feb 2, 2005
I have a simple form with a subform on it (see enclosed Access 2000 Database). I'm trying to add/change/delete records from table: Component Name. Adding and changing records is not a problem but deleting is. It only deletes the value of the field "Interative Component Name" and not the entire record. I have my joins defined properly on the tables. What am I missing? I wish this Access stuff was easier to learn. Any help/clues would be greatly appreciated.
Thanks
Sue
View 4 Replies
View Related
Nov 2, 2005
I have a "display only" subform that is updated by a popup form. If I add or amend an entry the details of the changes are displayed immediately in the display window by requerying the form "afterupdate". However, I have a delete button on my popup form which will delete a record but replaces it with the word #deleted in each of its fields. The #delete will only disappear once I have moved to another record which I don't want to do.
Can any one tell me why the amend/insert update works well but the delete behaves in this way.
How can I set up the delete function to function and remove the #deleted message without moving to another record. I have tried adding a save button but this doesn't fix it.
Thankyou
View 14 Replies
View Related
