Delete Query Not Working
May 12, 2005
Hi all,
I have a problem with a delete query i am trying to set up and hopefully one of you experts can help me out.
I have a table called [PMI] and a table called [Referrals]
The tables have a one to many relationship and are linked by the primary key field Patient_No.
I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.
Hope that makes sense, all help is greatly appreciated.
Thanks in advance.
View Replies
ADVERTISEMENT
Mar 19, 2008
Hello All,
when I try and run the following query it says "please specify the table", and for some reason in the clause I cannot change the expression WHERE to FROM. Any ideas?
DELETE DISTINCTROW tbl_Table1.Date, tbl_Table1.Time, tbl_Table1.[SRC Extn], tbl_Table1.CLI2, tbl_Table1.[Dest Extn], tbl_Table1.Digits, tbl_Table1.Duration, tbl_ExcludeCLI.ExcludeCLI
FROM tbl_ExcludeCLI LEFT JOIN tbl_Table1 ON tbl_ExcludeCLI.ExcludeCLI = tbl_Table1.CLI2;
View 1 Replies
View Related
Oct 29, 2004
This is a copy of the SQL statement used to update records from the tblContentImport Table to the tblContent Table.
UPDATE tblContent INNER JOIN tblConentImport ON (tblContent.Code = tblConentImport.Code) AND (tblContent.Type = tblConentImport.Type) AND (tblContent.Title = tblConentImport.Title) AND (tblContent.LoginName = tblConentImport.LoginName) SET tblContent.[Date Assigned] = [tblconentimport].[Date Assigned], tblContent.[Date Started] = [tblconentimport].[Date Started], tblContent.[Last Accessed] = [tblconentimport].[Last Accessed], tblContent.[Date Completed] = [tblconentimport].[Date Completed], tblContent.[Time Spent (min)] = [tblconentimport].[Time Spent (min)], tblContent.Score = [tblconentimport].[Score], tblContent.Result = [tblconentimport].[Result]
WHERE (((tblContent.LoginName)=[tblconentimport].[Loginname]) AND ((tblContent.Title)=[tblconentimport].[Title]) AND ((tblContent.Type)=[tblconentimport].[Type]) AND ((tblContent.Code)=[tblconentimport].[Code]));
Now I want to Delete these same records from the ContentImport Table. I change this statement to start with the word DELETE and Access want me to identify which table to delete the records from. What is wrong with my statement. Please help?
View 2 Replies
View Related
Jan 16, 2013
I've been creating a simple macro which will append data and then delete data from a table.The criteria for the queries is found in a form, and this is used as a paramter.However, when i run this all as a macro...the append and delete queries somehow can't read the criteria in the form. Rather...i have to type in the criteria again...and then it works.I have attached a copy of my DB.
1. Go to frmArchiveStock
2. Select a stock from the combo box (this the criteria for the append and delete queries)
3. Click the button to run macro mcrStockArchive.
How can i get the queries to pick up the value entered in the form to act as a criteria when they run?
View 1 Replies
View Related
Oct 24, 2007
Why does this sql not work?
It brings up the records which satisfy the criteria if one clicks on the view button. Yet does not delete the records! No warning signs appear at all
DELETE pupils.*, pupils.[Pupil ID]
FROM pupils
WHERE (((pupils.[Pupil ID])=[Forms]![Parents]![cmboPupilsDelete]));
Any ideas?
Thanks
View 10 Replies
View Related
Oct 21, 2005
Hi All
I'm back with Access again for a short time (hurray) where I'm picking up a problem for one of Col's dbs. And I'm really stuck! :(
It's a car parking db and you go into the record from a form with a list box on.
There is a delete button, which has the usual wizard code behind it:
On Error GoTo Err_Delete_Click
Dim rsp As Variant
rsp = MsgBox("You will delete the entire record and associated permit and car details. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")
If rsp = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Else
Exit Sub
End If
Exit_Delete_Click:
Exit Sub
Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click
Basically all this did was bring up the msgbox, bleep and not do anything else. When I debugged, it appeared to go through the code no problem.
I looked at an older db of mine and changed a few bits:
added docmd.setwarnings false and true before and after code
changed the Variant to Integer
Added docmd.close and requery of the form that the listbox is on, after the DoCmd. .... ... acVer70.
This closes the form, but doesn't appear to delete or requery, and when you click on the list box, there's now an error message, "No Current Record".
Anyone know what I'm doing wrong please.
Many thanks - Ally
I'm going now, but will be back on Monday.
View 14 Replies
View Related
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
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
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
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
Jan 30, 2007
Hi,
I hope someone is able to help me. I've been working on this for days. I have a report which has a 2 subreports in it. I have the subreport bound and have a query working in the recordsource. Here is the recordsource:
SELECT [Casual Roster Information].ID, [Casual Jobs/Dates].LinkID, Sponsors.SponsorID
I have one report with the Master/Child fields filled in and set but the sponsors subform I cannot fill in the Child field. It says I need to put SponsorID in the recordsource. I thought I did have this(see above.) That's my first problem.
My bigger problem which I think the previous problem is contributing too is my subreport shows up blank. When I run my query it works perfect. But, when I run it with the rest of the program the subreport shows up blank.
Anyone know how I can go about fixing this??
Thanks!!
Nick
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
