Queries :: Delete Records From A Table
May 1, 2015
I have two tables as follows;
Master file:Vessel_master
Vessel_code
Vessel_name
main data table:Main
Vessel_code
container_no
size
Voyage
weight
by using above two table have made a query as follows;
Vessel_name:Vessel_master
Vessel_code:Main
container_no:Main
size:Main
Voyage:Main
weight:Main
Have joined "vessel_code" fields of the both tables by join type 2.
by using the query as data source made a form to edit/update/delete data. the problem is, when delete a record by using the form, it will delete the related data in the master file as well, which i do not want.
View Replies
ADVERTISEMENT
Mar 26, 2015
delete Query How do i Specify the table containing the records that I want to delete?
View 1 Replies
View Related
Jun 3, 2013
I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.
Here are the relevant tables:
tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.
It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
tblPeopleRegions is a junction table between tblPeople and tblRegions.
It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.
For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.
For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).
If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).
PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]
View 8 Replies
View Related
Apr 17, 2013
How do I delete duplicated records without having to make a new table? I would like the records deleted from the original table.
View 13 Replies
View Related
Apr 17, 2015
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE *
FROM M0070
WHERE EXISTS
(SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
[Code] .....
View 3 Replies
View Related
Feb 7, 2014
I 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
View 14 Replies
View Related
Apr 20, 2015
I have list box named [lstItems], a table named [tblItems] and the field name is [Model]
I would like to (from a list box) delete a record from a button. I have looked and tried lots of delete code, but none of them seem to work.
View 14 Replies
View Related
Oct 25, 2014
I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.
Code:
DELETE Warranty
FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair]
WHERE (((Serials_Repair.Serials_Repair) Is Null));
I changed "Delete" it was "Select" thinking that would work but it does not.
View 3 Replies
View Related
Jun 19, 2015
I was able to create a query that selects records from one table that have several fields which don't match with another table.
The end-game with this query is to have it delete the records in table 1 that don't match records in table 2. The delete query tells me that it could not delete from specified tables.
I've attached the select query, the delete query and the error, and the relationship table for the db.
View 8 Replies
View Related
Jul 3, 2015
I have a form based on query. Each record has a checkbox.
I would like when activate checkbox to delete the specific record. from a button in the form.
Have not find anything around
View 14 Replies
View Related
Mar 4, 2015
i keep getting an error "could not delete from specified tables"
I have a table with this data
Main table
bub|12345|1
bub|45678|2
bub|91011|3
Child Table
bub|45678|2
result in Main table
bub|12345|1
bub|91011|3
match from child table and delete from main table.. but i keep getting error "Could not delete from specified table main table has unique records. and it is primary key..
View 3 Replies
View Related
Oct 26, 2005
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
View 2 Replies
View Related
Oct 24, 2013
I have a field that is text format. However many of the records in that field contain numbers.I want to delete all the records that are numbers in this text field, leaving only records with textual data.
View 4 Replies
View Related
Nov 3, 2014
I have a database that is used by managers to log activity of staff. We wanted to allow staff to write to the DB, but not allow them full access. We decided to accomplish this by adding a VBA function to an Excel sheet that they already use every day. It allows them to select a description, start time and end time and writes records to a database containing just 1 table.
The database used by the managers periodically runs an append query, and then a delete query to update with details of entries made by staff, and then clear the table to avoid duplicates during the next append.
I'm sure those of you with experience can already guess what's been happening and are shaking your head right now. It seems like entries made while the append query is running are being deleted when the delete query runs. So potentially I append 4 records, but delete 6 as 2 new entries were made before the delete query ran.
Is there a way to delete only the records that have just been appended? Or is there a more acceptable way of achieving this without using a delete query?
View 3 Replies
View Related
Aug 13, 2013
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
View 6 Replies
View Related
Mar 27, 2013
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
View 2 Replies
View Related
Oct 17, 2005
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
View 1 Replies
View Related
Jul 26, 2006
How do you have Access delete records automatically from a temp table?
View 1 Replies
View Related
Mar 15, 2007
OK guys, ive done some searching and saw some stuff on this, but everytime I try something I get a INVALID SQL error message. So Im hoping you guys can help out cause Im lost right now.
I have a 2 tables, Training and Schedule. Training contains all the employees training records. Schedule is meant to be a temp table where the supervisors can enter the training and then once completed, can check the COMPLETED checkbox and hit the LOAD TO TRAINING RECORD command button and it moves ONLY the files for that trainee (a combo box) with a completed checkmark to the training table.
Ive tried this code sooooooooo many ways and its giving me a headache. My original way didnt work at all (had a lot of Do..Loop and With Statements, and then someone posted something about Archiving which is the code Ive gone off of now). Any help you guys can off would be awesome!
Heres what I got:
Private Sub cmdLoad_Click()
On Error GoTo Err_Load_Record_Click
UploadHistory
Exit_Load_Record_Click:
Exit Sub
Err_Load_Record_Click:
MsgBox Err.Description
Resume Exit_Load_Record_Click
End Sub
Sub UploadHistory()
Dim DB As Database
Dim WS As Workspace 'Current workspace (for transaction).
Dim strSql As String 'Sql Code.
Dim strMsg As String 'MsgBox message.
Dim bInTrans As Boolean 'Flag that transaction is active.
Set DB = CurrentDb()
Set WS = DBEngine(0)
WS.BeginTrans
bInTrans = True
Set DB = WS(0)
On Error GoTo Err_UploadHistory
'Execute the add.
strSql = "INSERT INTO [Training] " _
& "(TaskNumber, Date, Hours, TrainerLast, TraineeLast, Qualified) " _
& "SELECT " & "Schedule.Task, Schedule.Date, Schedule.Hours, Schedule.Trainer, " _
& "Schedule.Trainee, Schedule.Qualified FROM [Schedule] " _
& "WHERE (((Schedule.Trainee) = " & Me.TraineeCombo & " AND (Schedule.Completed)= 1));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
'Execute the delete.
strSql = "DELETE FROM [Schedule] WHERE Trainee = " & Me.TraineeCombo & " AND Completed = 1;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
'Get user confirmation to commit the change.
strMsg = "Upload " & DB.RecordsAffected & " record(s) from " & Me.TraineeCombo & "?"
If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then
WS.CommitTrans
bInTrans = False
End If
Exit_UploadHistory:
'Clean up
On Error Resume Next
Set DB = Nothing
If bInTrans Then 'Rollback if the transaction is active.
WS.Rollback
End If
Set WS = Nothing
Exit Sub
Err_UploadHistory:
MsgBox Err.Description, vbExclamation, "Upload failed: Error " & Err.Number
Resume Exit_UploadHistory
End Sub
View 4 Replies
View Related
Apr 12, 2012
I have two tables that have duplicate order numbers. I would like to compare the first table with the second and then delete the records from the second that match.Can I set this up in a macro and run it every day?
View 2 Replies
View Related
Sep 6, 2013
I have a very simple 2010 Access database with only one table which contains a few fields (username, email, phone....)
I have a combobox with unbound controls to search/find users (by the lastname field). The combobox is in the header section which populates the fields (controls?) in subform below.
I have a delete button with some VBA code which allows the user to delete the currently displayed record however when the form refreshes, I end up with two issues:
1: I see #deleted# in the combobox dropdown until the dbase is closed/reopened (it's only typically used by one person at a time.) Compact/Repair doesn't seem necessary, especially since i think it's pretty difficult to do this programatically (?)
2: There are blank rows in the combobox dropdown from where the data used to reside after deleting the record.
I haven't been able to figure out how to remove those blank rows and refresh the combobox to display the remaining records (with the blank rows removed). So I have dozens of blank rows. Me.Requery doesn't seem to work.
My assumption is that the blank rows exist because I'm deleting the content from the combobox's initial creation?
Is there a better way to allow user's to see the list of user's and then be able to select that record and delete it?
If I use a simple search box on a form where people have to type a (last name for example), if it's spelled incorrectly, then nothing would be found.
View 5 Replies
View Related
Jul 22, 2015
I have two tables Table1,Table2. I want to delete records from Table1 whose ID= CoventionalID field value from Table2.
Table1 has two fields (ID,PolicyNumber)
Table2 has 5 fields(ID,PolicyNumber,ConventionalID,Conventional PolNo)
The following query doesn't delete anything.
Code:
DELETE *
FROM Table1 WHERE ID=(Select ConventionalID from Table2);
View 3 Replies
View Related
Oct 10, 2014
I want to Delete all records from the Table Named "CustomerWiseRotaryTillerRates" (Given Below) if the State in the State Filed in found in the "Customer_Master" Table (attached Herewith). What will be the correct procedure ?
I followed the steps referred in page no. 67 & 68 in the Book "Microsoft Access 2007 Data Analysis" by Mr. Michel Alexender.
Customer Name
State
Product_Code
Product_Name
Price_perunit
TSI Engineering Indutries (P) Ltd.
Assam
P_05
1500_Rotary_Tiller_220_RPM
Rs. 63,722.00
Kishan Krishi Engineering Works
Jharkhand
P_05
1500_Rotary_Tiller_220_RPM
Rs. 62,997.00
TSI Engineering Indutries (P) Ltd.
Assam
P_06
1750_Rotary_Tiller_220_RPM
Rs. 66,663.00
Shri_Tata_Enterprises
Bihar
P_06
1750_Rotary_Tiller_220_RPM
Rs. 65,683.00
View 1 Replies
View Related
Dec 25, 2014
i have two data tables, one is depending on the other. now i need to delete the main table row depending on the subtable row if it is null.
View 3 Replies
View Related
Apr 22, 2014
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
View 8 Replies
View Related
Jun 20, 2013
I have a sub form that allows users to add staff to a project team, once added it populates a table which updates the subform showing the selected employee. I am trying to enable a delete function that allows users to remove an employee from the project team in the subform showing selected staff. Here is the code I have so far, but it doesn't work;
Code:
Private Sub Command4_Click()
Dim dbs As Database
Dim rs As Recordset
Dim sqlstr As String
Set dbs = CurrentDb
sqlstr = "DELETE tbl_CapexStaff.* FROM tbl_CapexStaff WHERE CAP_ID = Forms!frm_Switchboard.CAP_Live"
dbs.Execute (sqlstr)
End Sub
View 4 Replies
View Related
