Delete Query Based On Another Table

Jul 15, 2006

I am trying to run the following Delete Query

DELETE Master.*
FROM Master
WHERE (((Master.glm_series)=506) AND ((Master.glm_account) In (SELECT glm_account from glj )) AND ((Master.glm_prft_ctr) In (SELECT glm_prft_ctr from glj)));


The problem is it is trying to delete all the records with the account numbers = and ALL the records with profit center equal. I am getting 2564 records instead on 147 records.

The two tables are related by glm_account AND glm_prft_ctr - BOTH must be equal in order to delete

If I do an INNER Join or add the other table to the Query it gives me "Cannot Delete Records from Specified Table"

Help

TIA
Nique

View Replies


ADVERTISEMENT

Delete Records From One Table Based On Record From Another

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

Queries :: Delete Records Based On Criteria In Another Table

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

Modules & VBA :: Delete From Table Based On List Box Selection

Nov 21, 2013

Upon the results which populate my list box what I am trying to do is allow the user to select various values from the list box and then hit a button which deletes the values from the table. My code is currently:

Code:
Private Sub Del_TsCorrection_tbox_Click()
Dim lngID As Long
Dim strSQL As String

[code]...

But it doesn't delete, infact it doesn't do anything. The list box values are still in the list box and if I look in the table they are still there also.

View 10 Replies View Related

Forms :: Add / Edit And Delete Records From A Table Based On Combo

Dec 12, 2013

I have a form to add, edit, and delete Records from a table. I am using the following VBA

Private Sub cmdEdit_Click()
If Not (Me.frmlEmpDetailsSub.Form.Recordset.EOF And Me.frmlEmpDetailsSub.Form.Recordset.BOF) Then
With Me.frmlEmpDetailsSub.Form.Recordset
Me.txtAddEditname = .Fields("Name")
Me.cboRoster = .Fields("Roster")
Me.cboPermFctn = .Fields("PermFctn")
End With
End If
End Sub

Instead of referring to the Subform to load the data i would like to refer to a combobox:

cboSearchName

Its not an issue but This Combobox contains 5 columns...

View 2 Replies View Related

Delete File Based On Query.

May 2, 2005

Ok, I have kind of a confusing thing.

Every employee has an ID, and a start date.

Some employees have pictures (stored in a folder).

When someone is terminated, the picture does not get erased (as it shoud).

So, I need to run a query that deletes any pictures older than the start date of the employee.

Any help on this? Not sure where to begin. I suppose I should create a query with employees who have pictures. But how do I compare a files date to an entry in a table?



Example table:


ID Emp Start File
101 John Smith 5/1/2005 \file_serveremppic101.jpg

View 5 Replies View Related

Delete Records Based On Query

Feb 2, 2006

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

View 2 Replies View Related

Modules & VBA :: Delete Records From A Table Based On Records In Another

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

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)

I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.

I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.

View 5 Replies View Related

Delete Query With A Joined Table

Aug 6, 2004

I have created a query that show all the records form Material Estimation table that the job number matches in the Production Table, that the AssDate is between the frm_Export_Data dates.

This the SQL view of the query.

DELETE [Material Estimation].*, Production.[AssDate]
FROM [Material Estimation] INNER JOIN Production ON [Material Estimation].[Job Number] = Production.[Job #]
WHERE (((Production.[AssDate]) Between [Forms]![frm_Export_Data]![FromDate] And [Forms]![frm_Export_Data]![ToDate]));

I want to delete all the records in the Material Estimation table that show up in this query.

Is this possible?

Thank you...

View 4 Replies View Related

Multiple Delete Query With Junction Table??

Dec 14, 2007

Hi there.

I'm trying to archive records by using append and delete queries that span accross 12 tables.

the VBA will execute them all. However, I have a junction table near the end; It will be the Junction table, then a parent then a child table. I can get the junction table keys deleted but after that I can't get the Parent and the child table records deleted.

Does anyone know how to do this. the parent table can't delete any records because I've deleted the junction table keys first but if I delete the parent table keys access obviously wont let me because it's leaving orphan records.

I'm sure there's a way to do this. hope someone can help me.

Thanks.

View 2 Replies View Related

Modules & VBA :: Query To Delete Records From Table

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

Queries :: Pass Table Names To Delete Query

Aug 27, 2014

I use the following code to delete from a table all records except those meeting the WHERE criteria:

Code:
DELETE tblABC.*, tblABC.SubjectID
FROM tblABC
WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));

I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).

Any good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?

View 5 Replies View Related

Queries :: Update Or Delete Query To Remove Data From Table

Sep 18, 2014

I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.

View 1 Replies View Related

Query Lets Me Delete But Doesn't Actually Delete...

Apr 2, 2007

I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.

How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?

View 2 Replies View Related

Delete Records Based On Date Criteria

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

Forms :: Delete Records Based On Inputs

Nov 23, 2013

I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.

I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
Dim stDocName As String
Dim intResponse As Integer
intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
Select Case intResponse
Case vbYes
CurrentDb.Execute _

[Code]...

View 9 Replies View Related

How To Delete Record Using Delete Query

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

Reports :: Delete / Ignore A Page Based On Field Value

Oct 18, 2013

So I have a report with two pages. One page is about product exchange information and the other one payment. This report can either be printed or saved as a pdf (one pdf for both pages) and e-mailed.

This works fine, however if the item is under warranty then the exchange is for free and I do not want to print/e-mail the 2nd page (payment).My trigger field for this is "Warranty" which can be "Yes" or "No".How can I prevent that 2nd page from printing/e-mailing? Can it be simply skipped when the report is being created based on my field?

View 2 Replies View Related

Delete Duplicate Records Based On Multiple Criteria

Sep 21, 2015

I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.

View 1 Replies View Related

Forms :: Delete Records In Subform Based On Check Box In Parent Form

Mar 1, 2015

I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.

What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.

View 5 Replies View Related

Queries :: Query Based On Table Matching Another Query?

Jan 13, 2014

Been working on this for a while now and can't get it to work how I'd like after trying a few things.

I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.

I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:

Quote:

SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration,
DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration,
DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration,

[Code] ....

Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.

View 14 Replies View Related

Can You Update A Table Value Based On Query?

Apr 27, 2006

Hi all,

Simple question...I think. Is it possible to update values in a table, based on the results of a query?

For example, I have tblPRR and qryProcessed.

When I run qryProcessed, I would like the field "Status" in 'tblPRR' to update from 'No' to 'Yes'.

Could anyone advise if this is possible?

Cheers

Rob

View 2 Replies View Related

Sort A Query Based On A Table

Jun 7, 2007

I have a query that I want to sort based on a table. (SS BELOW) I want the OS in qry2FINAL to be sorted based on Trn-OSstops table. So the OS column of the query should be sorted as which is the order shown in the table. I tried linking the 2 tables in my query but I had no clue what to do from there. Any help?MV6HP108PT337HP113 (sometimes there is an extra field, but can I make it show up last?)http://i10.tinypic.com/68hkwhe.gifEdit: I looked at my SS and it might be confusing. Ignore all of the records in the Trn-OSstops table except for the highlighted one.

View 14 Replies View Related

Delete Certain Records Based On Selected Date - Data Type Mismatch In Criteria Expression

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

**update A Table With A Resut Based On Query

Aug 28, 2007

Dear All,

This is my first post in the forum and would like to apologise for any shortcomings.

I want to update a field in my table with the result from a query.

Eg. Table A with two fields, a1 (recorda1) & a2 (recorda2)

Result from Query (recordq) has to be replaced in Table A, field a2.

after the code is run, (recorda2) should be replaced by (recordq)

Can anyone help me how to do this with the help of an update query.

Regards to all

Basheer

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved