Delete And Append Query At The Sametime
Feb 8, 2007
hey guys,
I'm new to this site, I have search a lot in this form regarding Delete and Append query used at the same time and couldn't find answer.
I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.
Thanks in advance for the help
View Replies
ADVERTISEMENT
Jul 26, 2007
I am trying to run a query that appends data into a table.. however, the way im setting it up.....lets say i have a table named Accouts, and under accounts there is #1 to 30. When I try to add a new account through a form, and append it to that, it takes the 31 there nwo are after i added one, and adds it to the 30 alraedy there, creating 61 instead of 31. is there anyway i can take into account for duplicates when im creating a query. im new to databasing, just got stuck with this assignment for the summer, and have no idea what im doing :( and i dont have time restart after learning normalization. i wish i did, but i dont. if it works it works, thats the bottom line. thanks everyone.
View 2 Replies
View Related
Jun 9, 2005
I have 2 databases which are mirror images of each other. one db is named rent and the other is named renthistory. in the db i have 2 tables, tblcustomer and tblradio. i would like a button on my form to append the current record to the renthistory db and then delete the current record. how can i do this?
Thanks in advance!
Tim
View 2 Replies
View Related
Jun 23, 2005
Sorry, I really couldn't think of a solid title for this. I'll try and explain and provide some code as well.
I've got a series of tables, one of which is linked to a Mobile Device. I have a query (herein called Query A) that queries a masterfile, named MasterFileEdit, based on selections the user makes on the front end. Query A then returns the results, sorted by section number. Query B is set up to query based on the same selections, but instead DELETE all of those records from the masterfile. Doing this makes sure that no duplicate work is performed.
Here's the code for Query A.
INSERT INTO [TABLE A1] ( OID, SystemLoc, SystemPN, Location, Part_Number, Qty, Date_n_Time, NL, RCHKL, RCHKP, oddeven, Auditor, upperlower, area, aisle, [section] )
SELECT TOP 100 MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]))
ORDER BY MasterFileEdit.section;
Everything after the 'FROM MasterFileEdit' after is the user selections that come from a form.
I need help writing the DELETE query. Here's what I've got thus far:
DELETE MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]));
I can't get the DELETE to sort. The query runs, but doesn't select the records it should. In fact, it doesn't select any of the correct records.
Thoughts: does the TOP 100 modifier work with Deletes?
Is this query even possible?
Thanks in Advance for your Help.
Matt
View 14 Replies
View Related
Feb 8, 2007
I don't know if this is the right place for my post. Since is in regard to queries...
I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.
Thanks in advance for the help
View 7 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
Mar 27, 2013
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
View 2 Replies
View Related
Apr 11, 2007
why isn't my Access giving me warning before runing the delete, append or update query because usually it warns you that you are about to append, update or delete the following number of records. It must be the settings, can someone help!
View 9 Replies
View Related
Jul 1, 2015
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
View 1 Replies
View Related
Dec 27, 2006
Hey everybody,
Admittingly I last did a query like this 4years ago and I know there is a simple way of doing it, but I have completely forgotten!
My problem: I am wanting to create a query which will automatically detect if a customer has NOT made an order in a period of twelve months (a customers details/orders are stored in the database: tables customer and tables order). Those who have not made any orders in 12months can be deleted (the option should be to delete them not automatic deletion.
I want the query to delete (if it is selected) all traces of that customer including their orders.
I tried creating the query and using the date function: <Date()-365, however, I want to make sure it will work.
Thanks and I hope people can help!
View 2 Replies
View Related
Oct 24, 2006
I need to append some rows into a table, before I add these rows i need to delete all of the old rows.
I can't use a Make Table query because I am updating a linked table in another database.
So how can I (automatically) delete all rows in that table before or during the append process.
Thanks
Steve
View 2 Replies
View Related
Sep 23, 2007
I got these two append and delete queries in my database that i want to run on startup. I was wondering if there is a way to not have the msg that pops up to ask if you are sure you want to append/delete 0 record to table if there isn't any record that matches the condition set. I'm sure there is a way because if there isn't any record that matches the condition set in the queries then i don't want to have to click "No" everytime the database starts.
thanks,
Vincent
View 14 Replies
View Related
Sep 4, 2005
I am accessing an oracle database that has several thousand records in it. I am quering for specific requirements, but would like to save my query results in a local access database for faster searching capabilities. Is there a way for me to set up a query that will go out to my oracle table files, select the records that pertain to my search criteria, and add records to locally stored tables without duplicating itself each time that I run the query? I would appreciate any assistance in this matter. Thanks for your help!
View 1 Replies
View Related
Jul 19, 2006
I am basically making a form to streamline the processes and functionality of my database project, so basically it's just a form that runs queries.
However, whenever it runs an action query, it always brings up a yes or no prompt for whatever action it is doing. Is there any way to disable these prompts (i.e. default yes to everything) or is this just an annoyance I will have to deal with?
Thanks.
View 2 Replies
View Related
Aug 19, 2004
I have a macro setup that deletes all the records in 2 different tables, then using about 8 queries, appends several records to the two tables where data was deleted. When these macros run, several Yes/No/Cancel dialog boxes come up for each query, first asking you if you are sure you want to do this, that it is going to change data in my table, and then second telling me how many records it's going to append or delete.
Is there a way to make it automatically run Yes to all of these dialog boxes? That would really help out a whole lot. Of instead of that, but maybe some sort of VB code that could do all the deleting and appending without the need of the Macros, and that doesn't require user intervention. I have my Macro's setup on the "On Click" event in a form, so using VB would be no problem at all.
Any Suggestions?
View 10 Replies
View Related
Oct 25, 2013
I need to reset the autonumber each time I delete/append records in a table. Best way?
View 5 Replies
View Related
Jun 11, 2014
I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
View 9 Replies
View Related
Feb 18, 2007
hi Guys,
I have been looking at different post and checking Microsoft help files as well, but still can't seem to fix this problem.
I am having 2 tables. The first table is connected to a form for viewing and entering data, and in the second table i am just copying 3-4 fields from the first table.
I am trying to use the insert statement to insert records in the second table, and everytime i click on the "Add" button to add the records i get the following error "MS access can't append all the records in the append query ... blah blah blah"
However if i close the form and reopen it, and goto the record (as it is saved in the first database) and now click on the add button to add the fields to the second table/database, it works.
What am i doing wrong???
Any inputs will be greatly appreciated.
View 3 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
Mar 4, 2015
In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.
All is well and fine until source data changes and the pass-through query runs too long and times out.
If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table
Is there a way to open an Append or Make-table query in Design view without invoking the source query?
View 1 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
Mar 20, 2007
Ok, this is what I want to do :
I want to 'append' individual records from 2 tables and place in an archive table or within another database, whichever is the best option.
I then want to be able to 'delete' the relevant records from one table.
The tables are tproperty and trents. This property paid rents but has since been sold. Therefore it no longer belongs in the database, however client wishes to keep details of the property/person/and rents paid in past, for any future ref. These tables are linked in relationships to tlessee and tbilling.
I've read books/notes/looked on here for inspiration and the right direction! If I choose 'append' which seems pretty straight forward then a 'delete' query, how do I choose only one record? Is a make-table onto a different database a better option? It would appear that the whole table is copied over? Can't understand the issue about auto-numbers being copied over? Do I have to use an append/delete query for each individual property that's ever removed?
I'm at a loss! :) Thanks
View 1 Replies
View Related
May 11, 2007
I have a database that contain foreclosure records. I'd like to create a query that will ask for a date and all records that are LESS than the date will be moved to a different table.
I'd also created an icon on my form and I'd like to attach this query to it.
Any help will be appreciated.
Bruce
View 10 Replies
View Related
Oct 6, 2006
Please how can i use a procedure to create a query, then append the content of the query to a table (am using MS Access Project)? i did it in Microsoft acess database but now i need it on Microsoft access Project beacuase am transfering to SQL server.
Thanks
View 3 Replies
View Related
