Delete Record From One Depending On Criteria From Another Table
May 27, 2005
Hello all,
I am trying to write a query that deletes all records from one table that meet the criteria of another.
The table i need to delete from is called StartingPoint and the other table is called R7e, both tables have a field called "Reference" and R7e has a field called "Change".
I have been stuggleing with various different delete query's to no avail.
So i need the query to delete a record from StartingPoint where [R7e].[Change] = "Suspended". The reference field is a primary key, this field will need to be used to determine which records to delete from StartingPoint.
Sorry if it all sounds confusing.
Any ideas guys?
Many thanks
Tim
View Replies
ADVERTISEMENT
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
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 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
Jan 23, 2012
I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.
What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.
View 5 Replies
View Related
Jul 9, 2014
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
[Code] ....
View 8 Replies
View Related
May 9, 2005
Hi,
Is it possible to delete a record from a table whilst viewing that table through a form. The awkward part though is because I have another table linked to it.
Basically the main table is Orders and the other table is Items (these items make up an order). So when I go to simply click on delete an error message appears saying that I cannot delete the record but there are records attached to it in another table. This is fair enough but is there a smooth way off deleting off the items?
Many thanks,
Paul.
View 3 Replies
View Related
Jun 21, 2007
Hi all, whats the simplest way to delete the last record in a table? based on
DoCmd.RunSQL "DELETE * FROM
Trying to use a button to do the above
Cheers
View 5 Replies
View Related
Mar 17, 2015
How do I DELETE the FIRST RECORD FROM A TABLE ?? I am using MS Access 2003
View 4 Replies
View Related
Jul 7, 2013
This is my code:
my pro is string
prID is autonumber field
Code:
CurrentDb.Execute "delete from protbl where prID='" & mypro & "'"
shows this error:
Data type mismatch in criteria expression
View 6 Replies
View Related
Oct 28, 2011
I have an employee and asset database. If an employee gets fired, I need remove them from the general employee records, but I want to save a record of that employee. Is there a way to delete an employee from one table and have it automatically added to another table?
View 4 Replies
View Related
Jul 23, 2015
I have table1 that I append data to. The unique_ID is the rpt_date.
On form1 (that shows the table1 data) I want to delete the Max rpt_date from the table1.
I have tried to create a query involving MaxOfrpt_date linked to the table1 by the rpt_date and then create a delete query but it doesn't work !!
View 1 Replies
View Related
Oct 23, 2014
I am getting a problem with delete button..I want to delete record from subform and Table..
Table:Payroll
Subform:PayrollSearchQuery
Form:Payrollog
I am giving the code is ....
Private Sub Command58_Click()
'check existing selected record
If Not (Me.PayrollsearchQuery.Form.Recordset.EOF And Me.PayrollsearchQuery.Form.Recordset.BOF) Then
'confirm delete
[code]....
View 3 Replies
View Related
Sep 19, 2014
I have a table that is showing #Deleted in several fields, not all of them. When I select the record and try to delete it I get the message that the record id deleted. How do I remove this record?
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
Jun 27, 2013
I have created a form and need to be able to delete employees from a table. I built a command button using the wizard and this is the current vb code-
Code:
Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Exit_cmdDeleteEmployee_Click:
Exit Sub
Err_cmdDeleteEmployee_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteEmployee_Click
End Sub
But when I click on the button I get a message saying it would create duplicate data in the table.
View 13 Replies
View Related
Dec 4, 2007
Hi All,
I have fought hard to try and figure this out and I have gotten 50% there.
I have a query that generates the following calculated fields through numerous calculations etc....:
[De-Merit Marks]
[No Del with Major Faults]
Now the way it should work is that a supplier gets a rating (A-D) depending on both their De-Merit Marks score and the amount of deliveries with major faults. I used the following function in the query and it does the de-merit mark grading.
Rating: IIf([qry_vend_rating2]![De-Merit Marks]<=10,"A",IIf([qry_vend_rating2]![De-Merit Marks]<=30,"B",IIf([qry_vend_rating2]![De-Merit Marks]<=50,"C",IIf([qry_vend_rating2]![De-Merit Marks]>=51,"D",""))))
However I have no idea how to integrate the Major Faults part.
Basically:
0-10 = A
11-30 = B
31-50 = C
51-100+=D
If the supplier has one major fault, the rating should drop one from that reported using just the de-merit score. Two means it drops two ratings (so if an "A" on just the demerit, they would drop to a "C" and so on. Obviously a supplier cannot drop below a "D".
Can anyone suggest how these maybe acheived? I fear it requring VB code, but I am not that good with VB hence why I have relied on queries for this.
TIA
View 2 Replies
View Related
May 6, 2013
I would like to only display certain fields on a form depending on a criteria in another cell. For example if some enters 4 in 'Schemes' it would display 4 data entry boxes scheme1, scheme2, scheme3 etc - is it possible?
View 7 Replies
View Related
May 19, 2011
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
View 13 Replies
View Related
Oct 10, 2007
Hey,
I have 2 combo boxes. The first one gives a lis of Prefix letters for railcars. The second uses the value of the 1st and finds railcars that start with the same letters.
finally got the 2nd combo box to list the prefix and number of a railcar based on the 1st combo box and also got it to clear as each run-through starts.
Now that the user can select a number (ex. dfcv1234 as the railcar), I need to find the record with the prefix dfcv and the car number 1234 and load the record to the form for view and editing.
So far, I know it needs to be the after update event and have a select statement that finds the [key], [prefix], [car_number] that matches the value selected on the form but how do I use the key to make the record show on the form?
Thanks,
slh
View 5 Replies
View Related
Nov 14, 2013
I have table which store set of number
table: parameter
field: Branch
550
660
770
880
I want to use enter query criteria so that it can filter all record from parameter table, How can I do? or any VBA code can serve same purpose?
View 6 Replies
View Related
May 30, 2014
I have a database where I need to return only the first record of each key (named "process key") accordingly to a time frame I set.
Consider the table below:
Code:
ProcessContact dateSalesmanClientClient
KeyKey
99904/28/2014 11:39:00SALESMAN 1111CLIENT 1
88804/28/2014 10:13:00SALESMAN 1222CLIENT 2
55504/22/2014 14:29:00SALESMAN 2333CLIENT 3
66604/16/2014 12:14:00SALESMAN 3444CLIENT 4
55504/16/2014 11:47:00SALESMAN 2333CLIENT 3
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
View 9 Replies
View Related
Aug 30, 2013
I have a checkbox which when checked then turns textboxes to locked as below. However when I navigate to the next record which may not be checked the text boxes remain locked. I obviously want to lock the boxes depending on each record. I am navigating via the windows next/back record buttons. How do i do it?
Private Sub Check44_Click()
If Check44.Value = -1 Then
serial.Locked = True
gain.Locked = True
swst.Locked = True
Else
serial.Locked = True
gain.Locked = True
swst.Locked = True
End If
End Sub
View 6 Replies
View Related
Aug 16, 2005
I have a table that stores records by "return date". I would like to create a delete query that removes all records that fall outside of the date range entered on a specific form (frmMainMenu).
I can deleted the records that match the form criteria, however that is not what I need. The criteria used to delete the records was:
Between [Forms]![frmMainMenu]![from date] And [Forms]![frmMainMenu]![to date]
Let's say that the frmMainMenu contain the date range of 8/1/05 to 8/10/05, however the table contained data from 6/1/05-8/14/05, I would like for the query to delete all records that have a date 6/1/05-07/31/05 and 08/11/05-08/14/05.
Any assistance would be greatly appreciated. Thanks in advance for your help![/SIZE]
View 1 Replies
View Related
Nov 23, 2014
I have 2tables,table1 id,name, table2 id,name,date.
How can i write a delete query that will delete everything in table2,if the data date is today?
View 2 Replies
View Related
Jun 28, 2013
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
View 11 Replies
View Related
