Need To Move Data From Table A To Table B And Delete It
Sep 20, 2006
Well I have two tables, one is the workorder register and the other is the closed work orders. This is for maintenance. I have a form for creating and veiwing workorders. I need to link a command button to the form that will delete the record being viewed in the WO REG table and paste it in the Closed WO table. I think I need to use two append queries but have no idea what I am doing. Here is what I have so far.
INSERT INTO [CLOSED WO] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
SELECT [WO REG].[WO], [WO REG].[MMCN], [WO REG].[TECH], [WO REG].[NOMIN], [WO REG].[FUALTS], [WO REG].[TYPE], [WO REG].[SECTION], [WO REG].[CLOSEDATE], [WO REG].[OPENDATE]
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].[MMCN]=[WO REG].[MMCN]
WHERE ((([WO REG].[WO])=[WHAT WO TO CLOSE?]));
Like I said I have no idea what I am doing, this is out of the SQL view for my "move to closed WO" query. the fields I need to move are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, and OPENDATE.
Is this even remotly right? I know nothing of VBA an all these symbols mean nothing to me.
When the value "X - DISCONNECTED" is selected in the form I want the record to be removed from its existing table and sent to a new table which keeps all the "X - DISCONNECTED" records together.
Any ideas would be greatly appreciated. How would this be coded?
Ok here is what I've got. I have a table called (EQUIP REG) It contains all the information to the equipment in the hospitol. next i have a table called (WO REG) It contains all the information for the workorders for the equipment.
In the EQUIP REG table I have field titled (PM) this is the date that the equipment is due for PM's. Now I could just run a query for all the equipment on a given date called "MONTHLYPM" and then "open" the workorders by hand....but would be an all day job becuase there are nearly a thousand PM's a month. What i need is a wuery to run off of the above mention query that will copy the some data from the EQUIP REG table to the WO REG table. I only need the fields called "MMCN" "SERIAL" "MODEL" and "LOCATION"
I know very little about access and even less about VBA, so who ever is brave enough to help me must be able to talk on a very low level. thanks for any help in advance. :)
I am working on a calibration database which keeps track of measurement equipment and their scheduled calibration date. There are several gages that become "In-Active" because they are old, lost, or whatever. I have two tables that have Active and In-Active gage listings containing all of the pertinent information regarding that gage. I would like to put a button in the Active gage form which in the event the gage becomes "In-Active", pressing the button moves all of the information in the "Active" page to a new page in the "In-Active" table. So essentially one can look at Active gages and In-Active gages depending on the switchboard button that is clicked.
I know that is probably a crazy question but any thoughts or insight would be greatly appreciated.
Hello. My question is how do I go about moving data from one field to another within a table? I want to move the data in ReportDateClo to ReportDatCom when both ReportDateClo and SupAct are true by record which are identified by the field LogNum. It is probably very simple but I am having a brain cramp.
ReportDateClo and ReportDatCom are "short date" date fields. SupAct is a Yes/No field. LogNum is an autonumber.
My boss has assigned a task for me to move the details under a field name (eg:[Name]) in table 1 to table2 under another field name (eg [Insured Name]).
Previously, the software I planned to use is Excel, but my boss says Excel cannot handle large data as the computer will be hanging.So,he wants me to use Access to do the programming.I have some programming backgrounds (like C++) but it seems like I hardly understand how Access works! So can any Access expert here teach me how to do so that i won't get fired.
Firstly, We will have the data like this.
Table1
Code: Name | PolicyNo | Age | Benefit --------+----------------+----------+----------- Chris | 123 | 21 | Death Jane | 456 | 34 | TPD Adam | 789 | 55 | DD
Secondly, we let the other staffs to key in which column to be mapped with the other. Manually Key in (both columns).Mapping Table:
Code: Table1 | Table2 --------------+------------------- Name | InsuredName Policy No | Pol_No Benefit | InsuredBenefit Age | InsuredAge
Thirdly, the staff will run the VBA and result would be like this.
Table2
Code: InsureName | Pol_No | InsuredBenefit | InsuredAge --------------+--------------+----------------------+---------------- Chris | 123 | Death | 21 Jane | 456 | TPD | 34 Adam | 789 | DD | 55
As you can see the new table(table2) , field number 3 is the same with the field number 4 in table1. There is something like copy and paste job, but user can decide how the arrangement for the new table is.
I have a Macro Which my team members fill and submit the information of their daily workload. And the person who Submits has a Pivot table to check if the Workload that he has submitted has been QCed or not.
I have made 2 Tables one is temporary database and other is a master Database, I keep clearing the Temp Database because when users refresh the Pivot table it takes less time if there is less data and Vice Versa.
Problem Statement: When a particular Column is updated for example "QC Result" column is updated as "Green" or "Red" the entire column needs to be moved to the Master Table.
I have two listboxes on a form listbox1 and listbox2. listbox1 is populated by table1 which has 40 fields and 1000 records. listbox2 is populated by table2 which has identical structure to table1(same fields) but has no records.
For both listboxes Multi Select is set to simple. Rowsource is Select * statement from tables. Row Source Type is set to Table/Query.
I would like to select multiple records from listbox1 and add them to listbox2 and table2 or just table2. After selecting the records from listbox1 the user will click a button to add the records to table2. Also need to delete the values selected from table1 on same button click.
I've been testing my scripts so my database has a whole bunch of bogus records in it. I'd like to simply delete everything out of my orders table (there aren't any legit ones in there yet) so that I can start over when I go live and not have all that crap in there.
Is there an easy way to do this without re-creating a blank database and copying/pasting table only over?
My goal is to get a huge amount of data paired down so that I can work with it in Excel.
I started with a 14 million record text file provided by a government agency. I used a text editor to break that into 7 2-million record files to fit into Access.
I figure I can filter each of those files down to 500,000 or so records, so that I can manipulate them in Excel. The problem is, when I open a text file in Access, Access insists on linking it, and then I can't delete data from a linked file.
I am currently unable to delete any data from my table. When I highlight a row and right-click, the option to delete the record is grayed-out. I have deleted and recreated the table and relationships, saved the database as a new file, and compacted and repaired, but I am still unable to delete anything.
How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?
On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:
Code: Private Const strNomeTblFonte as string = _ "SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;" Private Sub Class_Initialize() Set mCol = New Collection
[Code] ....
Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.
After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():
But only generates this: Run Time Error 3265 - Item not found in this collection...
I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?
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.
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.
Howdy all!I am new to Access (only using 2 weeks) and have never taken a class. I am trying to update a DB for my company. []=Table {}=Field What needs to be done is as follows:When a new Purchase Order form is filled out each material ordered has a {ProductNumber} and has a {price}. The {ProductNumber} is the PK in the [Inventory], while this {price} is saved in the [OrderDetails] table (in the Price field)The table called [Inventory] stores info about the products (such a manufacturer, serial number, latest price).However, as it comes time to order new materials the prices have changed.That is, when a new PO form control {Price} is filled out, [OrderDetails]{Price}records the price and product number. I need the [Inventory]{Price} to update the LATEST {ProductNumber}'s price when a new purchase order is completed. Is this difficult?
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------ ' Master_tbl_sub_fm ' '------------------------------------------------------------ Function Master_tbl_sub_fm() On Error GoTo Master_tbl_sub_fm_Err With CodeContextObject On Error Resume Next
SELECT * from mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield
But none of these do:
DELETE FROM mastertable INNER JOIN secondarytable ON mastertable.Identicalfield = secondarytable.Identicalfield **Incorrect syntax near the keyword 'INNER'.**
DELETE FROM mastertable JOIN secondarytable ON (mastertable.Identicalfield = secondarytable.Identicalfield) **Incorrect syntax near the keyword 'JOIN'.**
DELETE FROM mastertable WHERE (mastertable.Identicalfield = secondarytable.Identicalfield) **The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**
DELETE FROM mastertable WHERE IN (SELECT * FROM secondarytable) **Incorrect syntax near the keyword 'IN'.**
Can anyone help? This should be easy, shouldn't it?! Thank you.
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE * FROM M0070 WHERE EXISTS (SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
I have an employee and asset database. If an employee gets fired, I need remove them from the general employee records, but I want to save a record of that employee. Is there a way to delete an employee from one table and have it automatically added to another table?
I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:
There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.
The whole technical process is this: 1. A csv file is imported into a temp table 2. A temp table is created and named "_ImportedSKUS". 3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs". 4. Then the temp table, "_ImportedSKUs" is closed and deleted.
I have a delete command at the end of the sub, which goes like this:
DoCmd.DeleteObject acTable, "_ImportedSKUS".
But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.
I have an Equipment Form with a Supplier subform. The Supplier subform also has a subform, Equipment Pricing Records.
I have the ability to change the Supplier via a combo box, but Equipment Pricing Records become orphaned.
What I would like to do is when a Supplier is about to be changed, for the user to have the option to either a) delete the sub records, or b) the sub records to move across to the new Supplier.
I know I will need to use the after update event once the delete or move decision is made, but I can�t think how to identify the records and then either delete or move them to the new Supplier ID.
The EquipmentID will never change.
Fields are;
EQUIPMENT form; EquipmentID
SUPPLIER form; SupplierID EquipmentIDFK
PRICING RECORD form; PricingRecordID SupplierIDFK EquipmentIDFK
ok ive read some responses to people having trouble or need to know how to move a record from one table to another... i have two tables. One with member and another with thier siblings. i have two forms. one with the member details which contain a command box to open the other form for their siblings. the siblings form is a tabular form so when i double clikc the siblings form i can all the children but when i open it from a specific member only their children are shown. each member has a memberID (primary key). but the siblings have the same ID as their father(member)
i have put a commmand box in the siblings form next to every sibling. what i want is that when i click this button the record moves to the members column.
i only know the very basics of VB...so a step by step explination would be helpful...
this is very specific and if more information is required please ask here or ask for my msn address.
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.
