I have created a table1, with 3 fields: A, B and C, with 500 rows.
I want to copy this data over to table2 starting at row 700.
What is the sql for this.
Additionally I would like to in the 4th column to write November in the same copy over of the 500 rows.
I have two tables pertaining to the same database.
Table [Purchase_Order_Details] amongst many fields have Columns - (Purchase Order #) which is the Primary Key and (Work Order #)
My second table [Order Details] also has a column (Work Order #) . Now I have added another column to this table i.e. (PurchaseOrderNo) - this column is blank as of now.
However I would like to run a query to fill the (PurchaseOrderNo) column depending upon the (Work Order #) which is present both the tables.
I'm using Access 2007.
I have a button on a form that when clicked, does the following:
I have a table called [Workorder Parts] that has 128 records in it with Fields named WorkOrderPartID (Autonumber), WorkorderID(Number), PartID(Number), Quantity(Number), UnitPrice(Currency), Notes(Memo), KitID(Number).
I want to copy records into another table called [tKitsWorkorderParts] that has the identical structure based upon a value in the field WorkorderID. For testing purposes let's assume that the field contains the value "12". There are 28 records in the table that have the value set to "12"
If I hardcode the value "12" into the following SQL statement, it finds and copies the 28 records correctly.
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = 12"
so far so good.....
Now, on the form there is a Text Box called WorkorderID that contains the value "12".
If I change the code to the following, I get all 128 records instead of just the 28 I am expecting:
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = WorkorderID"
I have inserted a few MsgBox displays to display the value contained in WorkorderID and it shows "12"...
What am I doing wrong???
I have the following (simplified) normalised data table:
[Account] [SrcUD2] [Amount]
col1 row1 1000
col1 row2 500
col1 row3 500
col1 row4 1000
col1 row5 1000
col1 row6 0
col1 row7 1000
col1 row8 1000
col1 row9 0
col2 row1 100
col2 row2 1000
col2 row3 200
...
I use the query below to pivot the data into the right format for export:
Code:
TRANSFORM First(Amount)
SELECT SrcUD2
FROM source
WHERE LocName="myLoc" AND Entity="LE01" AND PeriodName="QA - 2014" AND ScenarioName="Actual"
AND (Account="col1" Or Account="col2" Or Account="col3" Or Account="col4" Or Account="col5" Or Account="col6" Or Account="col7")
AND (SrcUD2="row1" Or SrcUD2="row2" Or SrcUD2="row3" Or SrcUD2="row4" Or SrcUD2="row5" Or SrcUD2="row6" Or SrcUD2="row7" Or SrcUD2="row8" Or SrcUD2="row9")
GROUP BY SrcUD2
PIVOT Account
which yields the following table as the query result:
SrcUD1 col1 col2 col3 col4 col5 col6 col7
row1
row2
row3
row4
...
which is great except that I want to transfer the results to Excel using the CopyFormRecordset method without the metadata of column 1 (row1, row2 etc.
I'm trying to copy records from another base into existing table in current base by:
Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")
But it doesn't run. Says insert into syntax error.
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.
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] ....
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code:
Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database
' error handle
On Error GoTo F_Error
[Code] .....
I have 4 different sites with their own data source and have created 33 Queries for the first site. Is there a way I can copy the queries to the other sites instead of recreating them manually? All sites have their own data that is in the same format. All the information is in the same format and similar (different amounts, dates, but all in amount and date fields), they are named differently corresponding to their sites.
EX:
Site 1Site 2
Data 1Data 1
Data 2Data 2
33 Queries(needs the same queries as site 1)
My 2nd post, and I am very new to DB and Access. I have a problem that I want to get help on. I want to set up a main form that is used to enter and delete all data for my table. I wish to add either a button or to make it automatically happen when a record is deleted, that it is first copied to a separate table with the same fields, except it also has a closed date that would be the date that the record was copied over. I know zilch about VB, VBA or any other language other than AutoIt, so assume I am what I am, an ignorant beginner.
I did look into the event somethihng like upondeletion or something... while trying to find help on this in the access and VBA parts of Office, but I do not know how to utilize the event with Basic or SQL, which I know none of either.
Any help or examples are very much appreciated.
**EDIT**
I do not require all fields to be recorded to the secondary DB (History), so if someone can just give me an example of how I would move two fields to a separate DB, I can hopefully learn enough from it to do more.
Thanks a Bunch!
EXAMPLE**
Current Loans (Table 1):
CustomerID
Name
Address
City
State
Phone
Customer History (Table 2):
CustomerID
Name
Phone
That gives an example to help understand what I need. I want to store the CustomerID, Name and Phone values of the record being deleted, to the History Table, which I am using as a closed account table for later look up.
I have a table called [Attachments Table], which has the fields:
AttachNbr as autonumber
Task ID as number
FileN as hyperlink
URLText as long text
I want to copy the URL of the hyperlinks stored in the FileN fields to the URLText fields. However, when I run an update query I am getting the full hyperlink code and not just the address.
Example: FileN will appear as a hyperlink like JR1020.pdf, which is obviously formatted as JR1020.pdf#URL#..How can I copy just the plain URL (the stuff between the hash marks) without the hyperlink text names?
Code:
UPDATE [Attachments Table] SET [Attachments Table].URLText = [Attachments Table].[FileN];
SQL does not copy the field (autonumber) of table1 to another field (number longue) of table2. Is this normal and is there a way around it? Here is the code:
Code:
"INSERT INTO TransUPDSHTbl SELECT TranSHTbl.MemID, TransSHTbl.FirstName, " & _
" TransSHTbl.LastName, " & _
" TransSHTbl.Language, TransSHTbl.Email, TransSHTbl.Federation " & _
" FROM TransSHTbl " & _
" INNER JOIN MembersTbl ON ((TransSHTbl.LastName = MembersTbl.NOM) AND " & _
" (TransSHTbl.FirstName = MembersTbl.PRENOM)) ORDER BY MembersTbl.MemID;"
Above table1 = TransSHTbl, table2 =TranshSHUPDTbl, Field1 = MemID, Field2=MemID
I have a user who wants to automate copying a table from our main database. Basically, he is naming the new table with the original table name and the current date. For example, Part_Table_9-3-04, Part_Table_9-4-04, Part_Table_9-5-04 etc for each day of the month.
He wants me to write a macro, module, or vb code that automates the steps.
I have DoCmd.CopyObject,"Table1",AcTable,"Table2" I want to concatenate the date function (now) or (today) with the new table name but can't seem to get this to work.
Any suggestions?
Thanks for helping,
Jeff
I have a table with employees’ information. I want to copy all records in this table to another existing table “WorkTimes”. I do not want to edit my employee table, so therefore I want to copy all records to another table before editing/adding information in other fields. How I can do that with a macro or module?
Thanks in advance.
How to copy a column from one table and insert it into another table in the same database
Hi, All,
I have two tables (old and new) sitting in the same database. The new table is the result of 'data cleansing' done by an external company. In the process (export and import via excel) two memo type colums in the table were truncated in excel.
To make the new table usable, I must therefore now copy/insert the two memo columns from the old table into the new table.
Both tables are already Access tables and sit in the same database. Both tables, of course, have the same number of rows.
I tried to high-light one column in the old table, clicked copy, then high-lighted a blank column in the target table, then clicked Paste, but got error msg: "This text is too long for this field. Try copying a shorter text", as if I had wanted to copy the whole column into one cell rather than one column into another column of equal length.
What is the best way to proceed?
Thanks for your help.
Adrian
Well, heres the situation. I have a complicated query that refuses to work all the time using a linked table for the data (data is gathered from a FoxPro DB). If I copy the data into a local table in my database then the query will run fine.
The data needs to be updated only 1/month but I don't want to have to do it manually every month. I would like to use VBA to copy and paste the data from the linked table into my local table. Does anyone know of an efficient way to do this? I'm trying to avoid running a VBA loop and adding each record one-by-one (very slow).
I have a problem... I have four tables(but my problem is limited to just two)
The tables are Orders, Customers, Items and OrderedItems
The two more detailed below creates the problem when trying to copy an already existing order to a new since the customers usally order the samethings over and over again.
Orders
IDOrders
IDCustomers
strSalesperson
strWhen
strSent
OrderedItems
IDOrderedItems
IDOrders
IDItems
iNumber
bPacked
So I have made a copy button on the order form. The copy button should do this:
1. First save the old orders IDOrders and IDCustomers (which it does)
2. Create a new order with old data (which doesnt do)
3. Copy all post in OrderedItems with a new IDOrdered using INSERT INTO and using columns
My SQL statement is like follows:
sSQL ="INSERT INTO Orders(IDCustomers, strSalesperson, strWhen, strSent, strComment) VALUES (" & itmpIDCustomers & ",'" & strSalesperson & "', '" & strWhen & "','" & strSent & "');"
But the reply I got is "Could not find output table 'Orders'" but I have checked the names over and over again but it doesnt work.
hey guys,
how can i make a query to copy records from one table to the other, and leave out one record. the record has corrupted somewhere and is causing havoc on some forms.
cheers
I have a relatively small data base.
There are a number of what I call reference tables. These are fairly static and in the main will not change.
There are a further half a dozen tables all linked together.
The system basically takes an enquiry which may lead to a repair taking place. The repair will have a number of jobs and each job may have parts replaced.
What I want to do is have an identical database which to begin with will have exact copies of all the reference tables. The remaining half a dozen tables initially will be empty but grow with time.
The user wants to be able to copy the completed repairs he selects over to the 2nd database (which is initially empty). As weeks go by he wants to select further completed jobs that have not already been copied over to the 2nd database. The 2nd database will be updated so I can't just copy over all completed repairs each time.
I had envisage him doing this by using a front end to the 2 databases and firstly selecting which of the completed jobs which haven't been copied over. This is a fairly straight forward query that will update a field on the repair table.
Once he has done this I was thinking that I could run a query that would pull all the rows from the 6 tables from database 1 and update the corresponding tables/rows on the 2nd database.
I don't actually know how to approach that. The process needs to be simple for the user. ie 1. select repairs to copy over 2. run something to copy them over.
I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.
exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)
"exp4" is the clean name of the song.
Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?
What's the best way in an update query, using access 2003, to update data in a record based on the previous record having an identical value in a certain field?
For example my data looks something like this, in this example I want row 2 to fill in the blanks with the data from row 1 as they both have the same ID number at the end highlighted in red:
1 2 Anywhere Street 1 2 1 1 5423
2 4 Anywhere Street . . . . 5423
3 6 Anywhere Street 1 2 1 1 6401
4 8 Anywhere Street 1 2 1 1 6402
I've found various threads here on how to copy the whole record but I only want to copy the data from certain fields. More oftem than not the fields I'll be copying to will be NULL.
how to reference a field from the previous record within an update query I could probably work it out from there?
I am trying to copy the parameter value that is entered to a variable so that it can be used to run other queries without typing it in over and over. Once my form is finished it will run 4 queries, and the current setup is that I would have to retype the parameter each time.
View 9 Replies View RelatedHi all
This gonna be a long one.... I've search the whole forum for answer to this problem, but couldn't find the one suitable enough.
I have a quote table with the following field:
QuoteName
QuoteDescription
QuotePrice
and booking Table:
BookingName
BookingDescription
BookingPrice
The real table is much more complex, but this will do for now.
When a client call up for a quote, their request will be added into the "QuoteTable". One name can occur many times depend on how many they ask for quote. Once they decided to proceed with the booking, all the records need to be transfered into "BookingTable".
Currently, I use either one of the following codes found in this forum:
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset
Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")
Do Until rstQuote.EOF
rstBooking.AddNew
For Each Field In rstQuote.Fields
rstBooking.Fields(Field.Name).Value = _
Nz(rstQuote.Fields(Field.Name).Value, "")
Next Field
rstQuote.MoveNext
rstBooking.Update
Loop
or
Dim lngOuterCounter, lngInnerCounter As Long
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset
Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")
If Not rstQuote.EOF And Not rstQuote.BOF Then
rstBooking.AddNew
For lngInnerCounter = 0 To rstQuote.Fields.Count - 1
rstBooking.Fields(lngInnerCounter) = _
Nz(rstQuote.Fields(lngInnerCounter), "")
Next lngInnerCounter
rstBooking.Update
End If
But none seem to work. Can anyone help?
Cheers
arnodys
In the database I am developing, users have to be able to copy a table, no data, structure only. The source table is always the same one. Users have to be able to specify the name of the new table.
I would like to have them do this on a form, easy of use is important. Any ideas on this? Thanks. Trucktime
Hi Everyone
I wonder if anyone can help me with code that i can use to copy a single record from table1 to table2
that i can use in a subroutine.
Thanks in advance to anyone that may offer some help
Kindest regards
Tony
Once at the beginning of each month I extract all new cases from last month into a table. The table has a generic name as I repeat this step monthly. I would like to save or archive the data each month without having to manually copy and paste the table and rename it, prior to the new extraction, so that I have a record of each month. The archived table will not be used in any queries or reports.
The table I want to copy and rename is named 'tblSampleFrame'
At the beginning of the month before I delete the contents of the 'tblSampleFrame' to pull the new data, I would like to copy the old table and include a date in the new name. How would I set up a button on a form to copy the file and prompt me for the new file name? The new name would be 'tblSampleFrame_mmyyyy' where I can specify which month and year.