Delete Query...Doesn't!!!
Jan 15, 2007
I want to execute a delete query on a table of account records. The query contains two tables linked on account number, one with a list of unique records, linked to the other with "many" records to be deleted. For each unique record from table A, I want the delete query to delete all records with a matching account number in table B. However, I get a message stating that I "could not delete from the specified table." Huh???
I can manually delete from table B, or I can get the query to work if I don't try using table A as the control table and instead specify literal criteria. I have tried all three join types but still no luck. Any ideas?
Many thanks in advance for any help provided-
SLH
View Replies
ADVERTISEMENT
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
Aug 12, 2015
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
View 4 Replies
View Related
Apr 27, 2007
Hello,
I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical.
Does anybody know why this is happening?
Thank you.
The query is:
SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P
FROM P
ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;
View 6 Replies
View Related
Sep 27, 2005
If I copy paste the SQL from a query (View->SQL) to a macro it doesn't work.
Why?
I've tried removing quotes,Caps, adding quotes, parenthesis nothing worked.
Any ideas?
View 3 Replies
View Related
Mar 7, 2007
I created a database that contains 2 tables. I then created a query that simply groups the data and calculates a row total. And it works as expected. However, when I try to create a report, using the query, I get, "The wizard was unable to generate fields from the record source you chose. Perhaps you chose a query that doesn't return any fields."
What am I doing wrong?
Screencaps attached.
Thanks.
View 9 Replies
View Related
Nov 22, 2006
Upon closing my frmInventory the amount stock of stock is checked against a minimal stock value. If the stock amount is below a set minimal value a subsequent form is opened telling you that stock is low and an email message is generated to notify a manager. I have a checkbox on that form which is set to "True" upon close using an update query. The checkbox is there to give users the option to either send or not send a reminder message that stock is low when a message has already been sent earlier.
The problem is that using that update query ALL records are set to "sent=true" and not just the 1 record I intend.
This is my code in the "on close" event:
DoCmd.OpenQuery "qryUpdateEmailMinimal_True
and here's the SQL:
UPDATE tblInventory SET tblInventory.emailSentMinimal = True;
I assume what is missing is a reference to an inventoryID number. How do I do that?
View 3 Replies
View Related
Nov 6, 2006
Hi,
I have a query for a subform which is displayed in datasheet mode (see attachment). I've created a form based on a query and used it as a subform.
The "casenumbers" per clients are displayed from 1 and upwards, I would like to have them sorted Descending. So in the query I sorted them that way. When I run the query they are displayed in descending order. When I open the subform based on that query however it is not. I can sort descending anyway by clicking the column on which I want to sort and do it that way, but that becomes bothersome and it doesn't need to be that way.
Record source is ok:
SELECT KlantNAW.ClientNumber, CaseInfoTable.Casenumber, CaseInfoTable.DateInitialCall, CaseInfoTable.DateCaseCreation, CaseInfoTable.CaseAccepted, CaseInfoTable.[1stAppointmentDate], CaseInfoTable.DateCaseClosed, CaseInfoTable.About1 FROM KlantNAW INNER JOIN CaseInfoTable ON KlantNAW.ClientNumber=CaseInfoTable.ClientNumber;
Order by:
Casenumber DESC
I thought, well, lets create a new form based on the query to see if that changes anything, maybe I've changed something I shouldn't have.
But nope, same result.
How can make the form sort descending in datasheet mode?
View 1 Replies
View Related
Oct 11, 2004
When I open one of my tables or queries and look at the customer Id, the displayed info is a single digit. On my customers table my primary key is the customer Id with auto number with the format like this: "ID"000. This is great however, when you look up the customer id in other tables it only displayes the single digit. I want it to show up like ID001 or ID002. In the customers tables it looks just like that, but if you click in that cell/field it takes away the ID and just shows the 001 or 002. I changed the format in my other tables under customer id to "ID000" but that didn't seem to make a difference. I would like to be able to do a search lets say by customer ID in one of my products table and when I type ID005 or ID012 it takes me to those records. But right now I would have to enter 5 or 12. Datatype in my other tables on field customer ID is set to text. Do I have to do a validation rule? How can I get it to show up the way I want it?
View 7 Replies
View Related
Nov 13, 2004
I've encountered a simple problem. =) I have a report based on a query. Of course due to the relations the query table should affect the report. But it does only with the filter parameters I put in the Query Design View, the quick ones ("filter by selection", etc.) that are in the result table view work there but doesn't affect the report. Still the query asks me to save it each time I apply any of these quick filters but make no changes at the end.
Any ideas how to make them work in the report? Or these filters are temporal and shouldn't work in that way? If so, what could I do to simplify customer's life?
(Access 2003, Access2000 based database)
View 2 Replies
View Related
Nov 4, 2013
I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.
Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."
So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:
tblLeadData.AttyID
refAtty.Atty
tblLeadData.PlglID
refPlgl.Plgl
tblLeadData.AssignmentNote
Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]Criteria: Like "*" & [Who?] & "*"
The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.
When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.
As for my cascading combos, here are the settingsMatterTypeIDRow Source:
SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery
MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
On Change Event:Me.cboAtty.Requery
[Code] ....
I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter
data 3 times:Enter Parameter Value: Atty
Enter Parameter Value: Plgl
Enter Parameter Value: Who?
Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.
I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.
View 4 Replies
View Related
Aug 22, 2013
I already success to run this dynamic query where the parameters taken from the main form.Now the problem is the query result doesn't show in the subform.But the status bar below tell me that it have 2 records in the subform, but there is no data in the subform, it just Blank.I already apllied the Requery or Refresh to the subform (in the Command Button), but it have no result too.This is the code:
Forms!MsDataWarga.QueryDataWarga.Form.RecordSource = "MyQuery"
Forms!MsDataWarga.QueryDataWarga.Requery
Forms!MsDataWarga.QueryDataWarga.Refresh
View 4 Replies
View Related
Aug 4, 2014
I have a database used to track my personal assignments, created about six years ago using Access 2003 on Windows XP. Recently upgraded to Access 2010 on Windows 7. At some point thereafter, I started having the following issue:
When a new record is created, that record gets added to the table, but doesn't show-up in any query, form, or report until after another new record has been added. The most recently added record cannot be located to view or update, except in the table, until after another new record has been added to the table. Queries, forms, & reports now always lag behind by one record.
None of the queries, forms, or reports tested contain filters. I have several multi-user databases that I also support and none of those users have reported having this problem. This is only happening on my personal database.
I've re-created this database once or twice in the past to resolve other issues, but would like to avoid that route this time around, if possible.
View 7 Replies
View Related
Mar 29, 2013
Users are viewing a record on a form that gets it's data from a query. I want to make it so that if they edit that record from that form, a last updated field will timestamp the date/time that the record was edited. I added a lastupdated to the source table which of course adds it to the query and so it's on the form.
But whenever i access the form and change something, the lastupdated only shows the time the record was created (which is from a different form based directly on the table). Whenever I edit any other field data in that query based form it changes in the table. Why not the Lastupdated?
View 2 Replies
View Related
Mar 30, 2013
i'm trying to create a query of all employees doesn't have any transaction for a certain range of date and will also shows the last transaction date they have.i have two databases one is the transaction file and the other is the user file.
View 7 Replies
View Related
Mar 21, 2013
I am receiving this error when i try to run a query:
"You tried to execute a query that doesn't include specified expression as an aggregated function"
There are two images attached which show the SQL view, the error and the design view.
View 11 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
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
