How To Make This Delete Query?

Dec 13, 2007

OK,
I know what records I need to delete, just not sure how to do this.

I have 2 tables.
My main table, and a child table that hold some additional info for that record.

What I've been asked to do is, delete all records where in the child table the records meet a certain criteria.

So first I created a select query, added my main table, and then my child table.

Added my criteria to filter out and found the records they want removed.

So I thought I could just convert it to a delete query and be done. Nope because of the relationship between the tables it would not let me do it this way.

So the select query shows me all the ID#s that need to be removed (194k worth of them). How do I use the data from the select query, to create a delete query to remove all the files from the maintable (which should delete the data from the child table because of the relationship?)?

Here is the select query, that found the records that need to be deleted
SELECT tbl_Edit_Type_IRR.Information, tbl_Edit_Type_IRR.Issue, tbl_Edit_Type_IRR.Review, tbl_Edit_Type_IRR.Main_ID, T_tbl_main.Main_ID
FROM T_tbl_main INNER JOIN tbl_Edit_Type_IRR ON T_tbl_main.Main_ID = tbl_Edit_Type_IRR.Main_ID
WHERE (((tbl_Edit_Type_IRR.Information) Is Null) AND ((tbl_Edit_Type_IRR.Issue) Is Null) AND ((tbl_Edit_Type_IRR.Review) Is Null));

View Replies


ADVERTISEMENT

Queries :: How To Make Cascade Update / Delete

Jan 13, 2015

What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.

View 3 Replies View Related

Queries :: Delete Duplicated Records Without Having To Make A New Table?

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

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

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

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

Access 2010 - Delete Form Command Button With A Password To Confirm Delete

May 6, 2014

All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.

View 13 Replies View Related

Delete Query

Apr 4, 2006

Can anyone help?

i have set up the delete query:

DELETE dbo_MP_Audit.*, dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF
FROM dbo_MP_Audit INNER JOIN dbo_CAD_SINV_HEADER ON (dbo_MP_Audit.Usr = dbo_CAD_SINV_HEADER.USR_CODE) AND (dbo_MP_Audit.Sin_DocNum = dbo_CAD_SINV_HEADER.SIN_NUMBER) AND (dbo_MP_Audit.company = dbo_CAD_SINV_HEADER.CMP_CODE)
WHERE (((dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF)=1));

but receiving error message: "could not delete from specified tables".

View 3 Replies View Related

Delete Query

Aug 24, 2007

I have a table that has records listed more than one time. Some of these multiple records have in the score field a zero for the score. I would like to remove all records that are in the table more than once that have a zero for the score in the score field. Any help is greatly appreciated!

View 1 Replies View Related

Delete Query

Feb 21, 2008

Hi,

i have set up a delete query to delete items ticked on a query. the form related to the query only shows the items with a "False" value in the criteria. anything set as "true" ( ticked ) is reserved for deleting. the delete query looks at the query and selects all items set to true. it works ok but i dont know how to get this work automatically without going trough the access prompts of deleting.

can this be done with code behind a button whereas if clicked, it runs the query and deletes all of the true items?


thanks,

NS

View 2 Replies View Related

Delete Query?

May 20, 2005

I have created a query that append all the records to another table3 that are older than two years. But at the same time delete from the original table1.

If i was doing it from a form i would

DoCmd.OpenQuery "MoveQuery"
DoCmd.RunCommand acCmdDeleteRecord

However it will be moving several records at a time any ideas?

Secondly Table1 has a 1toMany Relationship with the table2, how can i move the records from table2 to table 4 at the same time. Will the data keep its relationship?

Please help.
Many THanks

View 1 Replies View Related

Delete Query

Jul 16, 2005

Quick question:

How can I automatically bypass the messages "You are about to run a delete query that will modify data in your table."? I don't want an end user to see that message, I just want the delete query to run. Is there a way to get rid of that message without supressing all the messages in the entire database?

View 2 Replies View Related

Delete Query

Aug 22, 2005

Dim db As DAO.Database
Set db = currentdb
db.execute delete master.* FROM master WHERE workitem=" " and corpid=" "

Please tell me how should i write this code( whcih should execute when a form is loaded)... i want to delete all records where workitem is blank and corpid is blank.

View 2 Replies View Related

Delete Query

Sep 26, 2005

I need help!

I'm trying to delete records from Table1 where the field "Name" is equal to the field "Name" in Table2. Please see below what I tried and is not working, any ideas?

DELETE table1.*
FROM table1 INNER JOIN table2 ON table1.Name = table2.Name;

Thanks! :confused:

View 2 Replies View Related

Delete Query Won't Run

Oct 5, 2005

Hi,

I'm trying to delete some records out of a table. I have a query that acts as a filter and is linked to two fields of the table that I want to delete from. I have put the * field from the table in the query and set the "Unique Values" property to "Yes". Access tells me "You cannot delete from the specified table".

The filter query is not an aggregate one, and if I remove it and simply have the table I want to delete from (i.e. delete all records) it will let me. I have done this loads of times before but this time it won't budge.

Any ideas?

Thanks, Tom

View 1 Replies View Related

Need Help With A Delete Query

Mar 13, 2006

I have two tables..... one table is my "master" table, and the other table is a table of all of the records I want to delete from the "master" table.

I tried setting up a query where I match up the records and then running the query to delete records, but it did not work.

Does anyone out there possibly have a query they could toss my way that uses one table to delete from another table?

Thank you so much!!!!

View 2 Replies View Related

Delete Query

Apr 17, 2006

Hi all.

Below are the info tat i am providing so that u can advice me on how to solve this problem of mine.

Table name = ImageTable
Fields = 1. Code
2. Pic

I want to pop up a MsgBox If the table is empty, i.e. there are no records in the table. How can i achieve that? Thks..

View 2 Replies View Related

Please Help With Delete Query

May 29, 2006

:confused:

Hi all

I've created a delete query and I wanted to run it with the RunSQL command.

Here is the code:

Private Sub btnDelete_Click()

RoundName.SetFocus

Dim strRoundName As String
strRoundName = Me.RoundName.Text
Dim SQL As String

SQL = "DELETE Rounds.RoundName" & _
"FROM Rounds" & _
"WHERE Rounds.RoundName ='strRoundName'"

DoCmd.RunSQL SQL

End Sub

When I click the delete button, it comes up with this error:

Run-time error '3075':

Syntax error (missing operator) in query expression
'Rounds.RoundNameFROM RoundsWHERE Rounds.RoundName='strRoundName".

I'm not sure where the missing syntax is for this query, could anyone help please.

Thanks
Carl

View 3 Replies View Related

Help With Delete Query

Nov 16, 2006

I cannot seem to get a delete query working.

I have two tables.
tbl_Job_Master w/ Job_No, and other fields
tbl_Job_Desc w/Job_No, Status and other fields

The Select query seems to work fine


I have tried.
Right Join, Left Join, Inner Join

All with no luck. If I go to datasheet view I see records returned but if I try to run it 'Could not delete from specified tables'.



DELETE tbl_Job_Master.*,
tbl_Job_Desc.Status
FROM tbl_Job_Master RIGHT JOIN tbl_Job_Desc
ON tbl_Job_Master.Job_No = tbl_Job_Desc.Job_No
WHERE (((tbl_Job_Desc.Status)=0));



I could realy use help on this. If anyone has any idea I would appreciate it.

View 3 Replies View Related

Delete Query

Dec 6, 2006

I have now have another problem doing the same thing. I made a nested query like you said below, then I wanted to delete those records - so I changed it to a delete query. when I tried to run it, it says "Specify the table containing the records you want to delete". I tried playing with it, but I can't seem to get it right.

Basically, I have a table and a sub-table, and I want to delete all records from BOTH tables where data matches a certain criteria in the sub-table. Specifically, I have a table of patients, and a sub-table of their test results. If any of the test results are positive, I want to delete that patient and all of his/her test results.

View 1 Replies View Related

Delete Query

Dec 7, 2006

Hi guys,

The following query


DELETE DD5592.*
FROM DD5592, [module]
WHERE DD5592.Sortcode=module.BASortCode;


Results in a message: Cannot delete from the specified tables.

Any ideas??

View 1 Replies View Related

Delete Query

Mar 21, 2007

is there any way that i can delete records from two different tables using the same delete query as i have them linked in the relationships using Member ID and if i try to delete from one of the tables then and error message appears stating that key violation has occurred.
Thanks

View 2 Replies View Related

Delete Query

May 3, 2007

I have two tables. The first table EES has the list of employees that I need and their information. The second table EPAY has even more employees and each employee may have duplicate rows.

I am wanting to delete any employee on EPAY that does not exist on EES.

I tried creating a delete table with the join from EPAY to EES, where a field on EES is null and then added the table fields from EPAY to be deleted. When I "view", it is showing all the rows that should be deleted, however, when I run, it is giving me the error that it "could not delete from specified tables".

Is there a way to do this? I'm not that familiar with delete queries, even though this seems to 'make sense' to me.:confused:

View 1 Replies View Related

Delete Query

May 10, 2007

I have exact duplicate records in my table. I would like to remove one of the records so there are no more duplicates but the original record remains. What is the best way to get these quickly removed? thanks for any help!

View 1 Replies View Related

Delete Query

Sep 17, 2007

I have created a delete query and it works fine. When the query is run a message box appears asking if you wanna delete the records etc....?

Is there a way to stop this message box from appearing?

View 4 Replies View Related

Delete Query

Nov 16, 2007

Hi All,

i have a Query that references 2 tables, a customers table and a purchase table.

i have a query that looks up customers who have not purchased within the last 7 months.

i would like to make a seperate form / query / macro / script or whatever that will prompt the user to enter the primary key from the customer table (customers unique number) and then it delete that customers record from the customers table and not touch anything from the purchases table.

i have looked at a delete query but can't make it so i can run my criteria and not delete from the purchases table

can anyone help?

View 3 Replies View Related







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