Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    46

    Unanswered: copy and update query

    i have table called work_items with two fields

    job_ref
    job_details

    for a each job number there can be x number of jobs.

    so the table looks like
    job_ref job_details
    2 clean house
    2 empty bins

    I would like a button on my form to copy all job_details for x duplicate them with a new value of y
    so it becomes

    job_ref job_details
    2 clean house
    2 empty bins
    3 clean house
    3 empty bins

    i was thinking of using insert into sql statement but wasn't sure of the correct syntax or how to loopit

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    INSERT INTO TableName (job_ref, job_details)
    SELECT 3, job_details
    FROM TableName
    WHERE job_ref = 2
    Paul

  3. #3
    Join Date
    Feb 2005
    Posts
    46
    Thanks for your help that works a treat but I want it to grab values from a form now, so this is the query that I have created

    INSERT INTO table_work ( job_ref, work_detail )
    SELECT [Forms]![Search Form]![oldRefBox] AS Expr1, table_work.work_detail
    FROM table_work
    WHERE (((table_work.job_ref)=[Forms]![Search Form]![jobRefBox]));

    It doesnt work what am I doing wrong??

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Define "doesn't work". Based on the control names, perhaps you have them reversed?
    Paul

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Gotta love those brackets ^^

    But yes... "doesn't work" could mean anything.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •