Results 1 to 2 of 2
  1. #1
    price12 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    1

    How to copy data within the same table?

    I have a table named Quotation that contains QuoteNo, Heading, Note, Delivery

    Sometimes when a new quote is generated, it is useful to clone some of the details from an existing quote. So, for example, I might start a new quote with a QuoteNo of 1000, but would like to copy over the Heading and Note from QuoteNo 800. So I need the ability to copy data from within the same table.

    I have tried writing an update query with a nested subquery, but keep getting a syntax error. Here is what I have:

    Code:
    UPDATE Quotation
    SET
    Heading = CloneData.Heading,
    Note = CloneData.Note
    FROM
    (
    SELECT
    Heading,
    Note,
    FROM Quotation
    WHERE QuoteNo = variable_CloneQuoteNo
    ) CloneData
    WHERE
    QuoteNo = variable_NewQuoteNo;
    The variables are set when I start a new quote and when I select a quote to clone. From what I can tell, this should be valid in SQL Server, but apparently not in Access. Any ideas of how to accomplish this in Access? I am using 2010.

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    If you are in process of editing an existing record and then try to simultaneously modify with UPDATE action on the table, this is like two people trying to edit same record and likely will error. If you are in process of creating new record the issue is probably the same.

    Suggest you open a recordset of the other record and populate fields of the current record on form.

    If you want to create the new record before opening it for further editing, use INSERT SELECT.

    I've never seen an UPDATE action with that syntax.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-23-2013, 08:14 AM
  2. Replies: 3
    Last Post: 03-05-2013, 05:14 AM
  3. Replies: 7
    Last Post: 10-15-2012, 11:05 PM
  4. copy data from one table to another
    By Sureshbabu in forum Access
    Replies: 1
    Last Post: 01-08-2012, 01:27 PM
  5. Copy and Paste Data Into an Indexed Table
    By CLT49er in forum Import/Export Data
    Replies: 1
    Last Post: 01-05-2012, 03:47 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums