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.
View Replies
ADVERTISEMENT
Nov 24, 2006
Ok my problem is this......
I have a form with a field "Property Status" on it. It has 4 possible values -
"C - SHELTERED (with warden charge)";"H - SHELTERED (No warden charge)";"J - WHEELCHAIR SHELTERED (With warden charge)";"M - WHEELCHAIR SHELTERED (No warden charge)";"X - DISCONNECTED"
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?
View 1 Replies
View Related
Sep 26, 2006
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. :)
View 2 Replies
View Related
Aug 26, 2004
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.
View 1 Replies
View Related
Nov 21, 2006
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.
View 2 Replies
View Related
Mar 30, 2015
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.
View 14 Replies
View Related
Jan 29, 2014
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.
View 1 Replies
View Related
Aug 25, 2014
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.
The database is saved with a .mdb file extension.
View 2 Replies
View Related
Jun 14, 2005
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?
View 2 Replies
View Related
May 1, 2015
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.
View 3 Replies
View Related
Jul 25, 2013
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.
View 1 Replies
View Related
Dec 17, 2013
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():
Code:
Recordset.Properties("Unique Table").Value = "tblClientesExamesRequisitados"
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?
View 3 Replies
View Related
Jul 14, 2013
Query4 is the result from left joining of Query3 and Query1 and I would like to delete some of the null data in query 4
while I execute the following code
DELETE DISTINCTROW Query4.*, [working hour] AS Expr1
FROM Query4
WHERE (((query4.[working hour])=0));
I get the warning of "Could not delete from specific table"
View 9 Replies
View Related
Oct 10, 2013
Access 2010. Can vba code be written to delete and insert data from a table in one routine.
Something like Delete * Insert into select from where order by....
I tried it with a query but have to write two.
View 1 Replies
View Related
Sep 18, 2014
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.
View 1 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
Mar 12, 2008
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?
View 5 Replies
View Related
Jul 9, 2014
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
[Code] ....
View 8 Replies
View Related
Dec 25, 2014
i have two data tables, one is depending on the other. now i need to delete the main table row depending on the subtable row if it is null.
View 3 Replies
View Related
Jan 13, 2006
A SELECT works:
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.
View 2 Replies
View Related
Apr 17, 2015
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],
[Code] .....
View 3 Replies
View Related
Oct 28, 2011
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?
View 4 Replies
View Related
Jun 6, 2006
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.
Can anyone help me out here???
View 4 Replies
View Related
Apr 26, 2014
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
View 14 Replies
View Related
Sep 8, 2006
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.
Thank you in advance
View 4 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