Transfer Data From Table Or Query To Second Table
Apr 25, 2014
See example below:
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines:
Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow).
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to query, or edit it.
View Replies
ADVERTISEMENT
Apr 25, 2014
Let's say that there're 2 workers: Worker A, and Worker B, and they are fixing some engines. There's 2 different engines: Engine A, and Engine B. And these workers are fixing these engines for all day, and in the evening they have to register how many engines they've fixed.
And my job is to design Access database for them. I thought it will be very easy to do; I've created table with columns: Date, Worker, Engine, Quantity - so every day Worker A and Worker B can register, that they have repaired some amount of Engines A and Engines B.
But there's a problem that I also have to register how many Engines have arrived to the factory, and how many of them weren't repaired on time (E.g. 15 Engines A have arrived, Worker A fixed 5, Worker B fixed 5 so there's 5 engines left for tomorrow)
I've figured out that I should somehow create table IncomingEngines with columns Date, Engine, Incoming, Fixed, Undone (field Fixed should be completed automatically every day for every engine - it would be a sum of engines A and engines B fixed by worked A and worker B - so I could fill Incoming field manually)
It's easy to create this kind-of Query, but I can't add column to querry, or edit it.
View 3 Replies
View Related
Jan 29, 2015
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
View 9 Replies
View Related
Jan 21, 2014
I am new in using MS Access. We have this thesis that checks the attendance of the professors and then sends the ID number of the professor to MS Access. I am using smsenabler as the software to encode the ID numbers to the database.
The problem is that, I want to transfer the ID numbers to specific table for the professors name. I don't know how to do it. And I can't make the ID numbers as primary keys because it duplicates every time the professor scans his/her ID.
View 2 Replies
View Related
Oct 5, 2005
I need to do a date calculation where dates that fall three months earlier than now() would be moved from showings to a clone of it to reduce clutter in forms and also keep only recent data viewable and archival data in an archive table.
Also, I'd like another table for old properties. When the status of a property becomes closed I'd like to have the information migrate to an archival table. Is there any way to do that?
View 1 Replies
View Related
Nov 15, 2007
Hey everyone,
I am a complete lamen on this, I am finding. Here is what I want to do.
I have an access database file with two tables inside it: Distributions AND Plan Data
In both tables, I have the following fields:
CRS ID Number:
Plan Name:
Company Name:
Company Address 1:
Company Address 2:
Company City:
Company Zip:
Company Federal Tax ID:
Company State Tax ID:
I want to be able to type in a 3-digit CRS ID Number in my form for DISTRIBUTIONS and for it AUTOMATICALLY to find that CRS ID number in PLAN DATA and populate all these common fields using data from PLAN DATA, inserting it into the DISTRIBUTIONS table.
The reason I need it to pull the data from PLAN DATA and insert it into DISTRIBUTIONS table is because we are using some out-of-access features that require us to have all data in one table. Thanks!
View 1 Replies
View Related
May 6, 2007
PLEASE ANSWER ASAP!!!
I was wondering if there was a way to automatically transfer data from one table into another. Specifically, if I have two tables, one called Students and one Alumni, is there a way I can click a button or something to transfer a student's data file from the Students' table into the Alumni table? Without having to delete the student's file from Student and retyping it all into Alumni?
Also, is there a way to, for instance, type individual monetary amounts into a single entry in a table and have the total automatically summed? Like, if I have a table for Donors, and every time one person makes a donation, add that one donation to their personal information and have another field that gives the total amount of all of their donations?
Thanks!
View 9 Replies
View Related
Nov 2, 2011
I have one table with the following records:
Name Surname School Date1 Date2 Date3
John Smith London 12/12/11 11/11/11 10/11/11
Mary Wayne Harvard 11/10/09 12/10/13 10/11/11
I would like to design a Macro which can create another table like this :
Name Surname School Date
John Smith London 12/12/11
John Smith London 11/11/11
John Smith London 10/11/11
Mary WAyne Harvard 11/10/09
Mary Wayne Harvard 12/10/13
Mary Wayne Harvard 10/11/11
That is duplicate the records, and create another table, with all the records and each record with just 1 date.
View 2 Replies
View Related
Jun 25, 2014
Any way to transfer the data in an unbound textbox into a separate table.
User enters an amount into a textbox2, and then it does some calculations that involves textbox1 and textbox2. Finally, textbox3 has the final answer. Can textbox3 overwrite textbox1's data on the table and on the form?
Also, is there an "auto date" feature in tables? If new data is put into tables, Today's date appears on the date column?
View 5 Replies
View Related
Aug 29, 2006
Hi,
I am extracting data from linked db2 table using access make table query. First I create a select query and can view the linked db2 data, but when I change to a make table query I get an error message, "invalid argument", when I run the make table query. There is no selection critera specified. Has anyone had this happen? and Do you know a solution?
Thanks for your support
GinnyP
View 1 Replies
View Related
Jul 11, 2006
Hi All,
Im trying to make up a vb command to send date from a query based from to a table i have no clue how to even start.
The database is to record when a item is inspected so a user would type the date in to a unbound box and then at a click of a button the date along with the id code will transfer in to a table so that i can compair the difference between the dates of any one item
is this possible and how???
Many thanks in advance
View 3 Replies
View Related
Jan 10, 2015
I have a form with 8 textboxes which users input and transfer all these data to a table when pressed on a button(VBA Code)
Below is the vba code i used, which works perfectly
CurrentDb.Execute "INSERT INTO tblreverse(txtRequestedBy,dtRequestedDate,txtPrevB illTo,txtOrderNo,txtInvoiceNo,txtLicenceNo,txtInst ruction,mmremarks) " & _
"values('" & Me.ccReq & "', #" & Format(Me.ccDate, "yyyy/mm/dd") & "#, '" & _
Me.ccPrev & "', '" & Me.ccOrder & "', '" & Me.ccInvoice & "', '" & Me.ccLic & "', '" & Me.ccInst & "','" & Me.ccRem & "')"
In the table tblreverse, i have also a field name which is "attAttachment".
What i'm trying is to insert a textbox in my form names ccAtt and when click on the button, the attachment goes to the table.
Here;s how i put the code
CurrentDb.Execute "INSERT INTO tblreverse(txtRequestedBy,dtRequestedDate,txtPrevB illTo,txtOrderNo,txtInvoiceNo,txtLicenceNo,txtInst ruction,mmremarks,attAttachment) " & _
"values('" & Me.ccReq & "', #" & Format(Me.ccDate, "yyyy/mm/dd") & "#, '" & _
Me.ccPrev & "', '" & Me.ccOrder & "', '" & Me.ccInvoice & "', '" & Me.ccLic & "', '" & Me.ccInst & "','" & Me.ccRem & "','" & Me.ccAtt & "')"
But it does not function.
View 1 Replies
View Related
May 11, 2015
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast )
SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast
FROM dbo_examInfo
View 2 Replies
View Related
Aug 18, 2015
I'm working with Access 2010 and am trying to use the transferspreadsheet command to output data in a query to an Excel 2010 format file. Here is the line of code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "q_calldetails_tmp", "c: emp estoutput.xlsx"
It works fine and produces the output file but when I try and open it with Excel I get an error saying the format is incorrect. If I change the extension to .xls it opens with no problem but I need it to be an Excel 2010 format with correct extension.
View 3 Replies
View Related
Dec 30, 2014
I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
View 5 Replies
View Related
Dec 17, 2012
I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.
View 2 Replies
View Related
Dec 4, 2014
There are two tabs named Table1 and Table2. In actual there are two tables in Access database named Table1 and Table2. How the data is stored in ACcess tables, I have made two tabs in excel workbook. Now I want Access VBA code that will check if data in Reference field of Table1 matches with any of the data in Reference field of Table2.
If it matches then change the status of the corresponding record of Table2 with either "Withdrawn","Obsolete" or "Updated". SO it depends upon which field out of "WIthdrawn","Obsolete" and "Updated" in Table1 stores "Y". At a time only one of them will have "Y" and rest of two fields will have "N" as shown in the sheets.
As in the example, now Reference "R566" of Table1 matches with Table2 Reference so the status field in Table2 for that record will be "WithDrawn".
View 14 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
Jan 13, 2014
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
View 1 Replies
View Related
Sep 12, 2014
How to make a form open with data from a table based on a date and time in the/a table?
View 7 Replies
View Related
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
Aug 17, 2012
I have one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
View 1 Replies
View Related
Jan 22, 2015
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
View 2 Replies
View Related
Dec 16, 2013
How to update data from one table to another table using form.
I have data coming from design team in Database 1 and using form i want search data and assign the job to a person and store it in the database with his name. I have to do this because database from design team is read only.
View 1 Replies
View Related
Jun 14, 2007
quick question. If i run a query and change some data in the query will in also change the data in the table? I tried it and it in fact did. If this really does work I don't think it should be allowed to functionally do that since people can easily mess up the data in the table.
View 4 Replies
View Related
May 25, 2005
I create a query from different tables. With an website based insert statement I put the data from this query to another table.
Is it possible to fill the table without the insert statement. But automatic done by the database itself. So insert/update all the query data automatic into the table
ThanXs
View 1 Replies
View Related