Queries :: Deleting Records Based On Selections From Another Table
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....
In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
Using Access 2007. I have a form (Broker), with a combobox (cboClassifier), that I use to select 1 of 4 phrases, display the selected phrase and color the field with a color that I've assigned to each phrase.
Next, I have a report that contains all of the Broker names, addresses, etc. based on a query (qryBroker). I need the vba or whatever that colors the matching records based on the phrase selected by the combobox on the form, when the report is opened.
1 - Maintenance Order 2 - Breakdown Order 3 - Greasing Order
On my form I have two list boxes: An Order Type List box, and an Area Listbox.What query criteria or VBA code would I use so that I could populate a text box with the relevant order number based on the selections of the list boxes. i.e. MaintenanceOrder & Area Z would display MaintenanceOrderNoZ.
I'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.
What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).
Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.
I have a form with a subform inside of it. On the subform I have created a field for a check box. I wish for the user to be able to click 1 or more check boxes in the subform and then click a delete button that removes all selected entries.
I have this code from a seperate post regarding this problem, but am not sure how to edit it to reflect what I need.
Sub DeleteRecordButton_Click()
'Delete the selected record dim strSQL as String strSQL = "DELETE * FROM mytable WHERE mytblID = " & Me![mySubForm].Form![mytblID]
I want to write an sql or vba code to delete records based on the month...For example I have a field called date which contains a date and I want to delete all Dates that are In April
Ive tried Delete from LinkTable Where month(Date) = 'April' Still Nothing
So I'm trying to populate a table based on selections I make within a form.
The form is based of a query that pulls a new product category that needs to be added to my first reference table for products.
The form shows all the new unique product codes, and there is a drop down box with product categories whose source is also the first reference table, so a new code would be:
'JBL - TRX - FVB - TRZ' And based on that new info, I would select from the drop down to select the corresponding category and click 'Add Record' button.
I'm building the event for the button and the code is as follows so far :
Private Sub Add_Record_Click() End Sub Private Sub cmbAdd_Record_Click() 'add data to table CurrentDb.Execute "INSERT INTO tblPVMTable(PVMJoinField, SummaryPVMCategory) " & _
[Code] ....
I keep getting errors with the main portion of code.
I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.
I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3
My query is: SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header FROM tblSomething WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));
The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.
I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.
I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.
Code: Name EMP ID OP1 OP2 OP3 OP4 OP5 ----------------------------------------------------------------------------- John Doe 526261 C C C Bob Doe 555622 C C C Sheila Doe 066600 C C C
Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.
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]
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.
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...
I am trying to get a series of combo boxes to interact and record the selections made in new records within a destination table. Here is situation with respect to the tables involved:
1 table lists the names/acronyms of various research facilities and the branch to which they belong (acronym is PK) 1 table lists Financial Points of Contact for each facility (acronym is FK to relate to earlier table) 1 table lists Technical Points of Contact for each facility (acronym is FK to relate to Facility table) 1 table lists program participants and the branch they belong to (Branch in FK to relate to Facility table)
I am trying to create a form that allows me to set the participant and, from this selection, restricts the facility choices in the Facility combo box to those that fall within the branch to which the participant is assigned. I think the next step is pretty obvious too. Once the facility is selected I have combo boxes that would only display those Financial and Technical PoCs assigned to that facility.
I have watched the 4 Data Pig tutorials and I can easily make functional cascading combo boxes as long as they only refer to the look up tables. I generated perfectly functioning cascading combo boxes on a test form. How do I generate combo boxes that will then store the info selected in my destination table? Whenever I try to set a Control Source it disrupts the functioning of the queries.
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?
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...
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.
I have created a form to record addresses, phone numbers and other information. How do I go about creating a DELETE button that when pressed, deletes ALL records from the table.
Many thanks in advance and for those of you who celebrate Thanksgiving, have a Happy Thanksgiving!
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.
Code: SELECT Unique_No, Table_Name, List_Order FROM My_Table WHERE Table_Name = 'Titles' AND List_Order IN (3,1,15,4,5,12,7,2) ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)
Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus
I want to automatically delete records from a table in MS Access 2002, I want to keep the existing table structure and therefore I do not want to delete the entire table. I cannot find a way to do this through a macro. Any help on this would be appreciated
Apologies if there is a previous post that answers this - I've looked, but can't find anything that works.
I have two tables with identical structures. tblA contains a subset of the records on tblB, with identical values on all fields except ID. I need to remove from tblB all records appearing on tblA. I thought the following would work:
DELETE tblB.* from tblB INNER JOIN tblA ON tblB.Field1 = tblA.Field1 AND tblB.Field2 = tblA.Field2 AND tblB.Field3 = tblA.Field3...
but I get "Could not delete from specified tables".
I have a form linked to a table and in my form there is a listbox reflecting the records in my table. Evereytime I tick a record in the list box the information on its corresponding columns appears on the text field.
So far I am able to add, modify and save record to my table using this form but I am having a problem on deleting a record which I selected on the listbox. I created a delete command button but it is not working. Please see attached database.
I have a FrmCadastro which updates my master table TblCadastro. Primary key of the TblCadastro is an auto numbered field named CADID.Fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ of the FrmCadastro are mandatory. My rule is that If any of these fields is null then the record cannot be saved (cannot be added into TblCadastro).
FrmCadastro has two distinct buttons, as follows: BtSave (to save either newly included record or changed record) and BtDelete (to delete a chosen record from my master table).To delete a record has not been a problem because under the click event of the BtDelete I use the following command lines to successfully eliminate an existing record (previously saved) from the TblCadastro:Dim numRecord As Integer
numRecord = Me.CADID Dim SQL As String DoCmd.SetWarnings False SQL = "DELETE * FROM tblcadastro WHERE cadid = " & numRecord DoCmd.RunSQL SQL DoCmd.SetWarnings True
To save a complete record (properly filled in) has not been a problem either since the code takes care of that very well.When the user is in the process of adding a new record through the FrmCadastro two different user behaviors can take place:
1. Irregular entering - The user for some reason just give up saving the record (he decides to add the new record later, for instance) by selecting the option Save = No, or 2. Incomplete record - By mistake the user try to save an incomplete record (a record that shows any mandatory field as null).
I coded the following command lines under the click event of the BtSave to take care of deleting those records... BUT it does NOT work. The system reads each line of the code, find the record properly, close the form as expected, but the incomplete record STILL REMAINS in the TblCadastro. I do not understand why, since the code is pretty much the same of the one in the BtDelete.
Code: Private Sub bt_save_Click() If MsgBox("The record was changed. Do you want to save it?", vbQuestion + vbYesNo, Me.Caption) = vbYes Then If MsgBox("WARNING! Incomplete record will be automatically deleted from database. If you did not fill in the fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ, the record will not be saved.", vbOKOnly, Me.Caption) Then If Not IsNull(CPF) And Not IsNull(Name) And Not IsNull(FIN) And Not IsNull(NAT) And Not IsNull(TIP) And Not IsNull(MOT) And Not IsNull(INSTREQ) Then
[code]...
What should I code to exclude both the incomplete record and irregular entering thru the button Save (options Yes and No) ?
