Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    UPDATE query to copy values between fields and rows based on criteria in the table


    All,

    Grateful for any help you can provide with manipulating some data within a table....

    I have an table which contains a list of components according to category, subcategory, subsubcategory and component. The table holds a total, overhead and used value for each component. For components with the same category, subcategory and component ID, I need to copy the total value across if the subsubcategory has a certain fixd value (1 in my example) to all entries with the matching category, subcategory and componentID, but different subsubcategory. The fields to be updated will be null before the update, adn I need to test for that as part of the condition. I also need to sum the overhead values for all matching entries and copy this into the subsubcategory "1" entry. Hard to put this into words, but I've attached a stock table and a "result" table that shows what I need the outcome to look like. Ideally this will update in the same table, but no big deal to use a temporary table or a separate "result" table if necessary, as it will mainly be used for reporting results.

    I've tried various update queries and SQL but without success. Grateful for any help you can provide,

    Many thanks....
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Before you start dealing with SQL, queries etc, make sure you have a database design that represents your "business".

    Please read these articles
    http://forums.aspfree.com/attachment...2&d=1201055452
    http://www.rogersaccesslibrary.com/T...lationship.zip
    http://www.rogersaccesslibrary.com/f..._Databases.zip

    and post back if there are issues.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-30-2012, 10:38 AM
  2. Replies: 2
    Last Post: 05-16-2012, 04:45 PM
  3. Query to find and COMBINE rows based on 2 fields
    By admessing in forum Queries
    Replies: 2
    Last Post: 12-13-2011, 12:59 PM
  4. Replies: 2
    Last Post: 08-28-2011, 06:06 AM
  5. Replies: 10
    Last Post: 07-02-2011, 11:51 AM

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