Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58

    Query/Table fields based off value of another field.


    Hello all. Here is what I am trying to do. I have table columns with names "ItemName" and "Description". In another table I have two columns "Assembly" and "AssemblyDescription". ItemName is populated automatically by QuickBooks. What I would like to do is populate the "Description" field in my query based off the "Assembly" value. So for example. If "Item Name" is "Blue" then the "Description" field will be populated with the "AssemblyDesciption" value if "Assembly" contains "Blue". Otherwise it is left blank. Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    Create an update query. Join the 2 like columns, update description.
    this is Basic query design.

  3. #3
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    Quote Originally Posted by ranman256 View Post
    Create an update query. Join the 2 like columns, update description.
    this is Basic query design.
    OK, So this will work if ItemName=Assembly. In my case, I need it to work if Assembly is any part of ItemName. So say if Assembly=BLUE and ItemName=BLUE2.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    On ItemName criteria:

    LIke "*[Assembly]*"

  5. #5
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    Quote Originally Posted by Bulzie View Post
    On ItemName criteria:

    LIke "*[Assembly]*"
    Ok, I had to change some names for logistical reasons. Here is the info.

    TABLE 1 (ProductionHeaders)
    Field1 - ItemName
    Field2 - ItemDes

    TABLE 2 (ASSEMBLY)
    Field1 - AssemblyName
    Field2 - ItemDes

    I created an update query that is supposed to update [ItemDes] in TABLE1 from the data in [ItemDes] in TABLE2. But it is not working. A snap shot of the update query design is attached. Hope this helps.
    Attached Files Attached Files

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    What is not working, its not considering the right records or the updated field is not geting populated? Are you sure it is linked to the right fields in both tables? Change the query to a Select query and see if it is pulling the correct data you want to update.

  7. #7
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    The updated field is not getting populated. I did change it to a Select Query and it does not pull the correct data. In fact, it pulls nothing. As far as the linking goes. I am not really certain how to check that. I see the relationship there from the attachment, but I do not know how to tell which fields are linked.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    Can you expand the column in the query and do another screen shot? Do you have the * at the end also? Another test is to take out the Criteria altogether and see if it returns all records. If it does, something wrong with the criteria line.

  9. #9
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    Here is the new screen shot. I did take out the criteria and it did ask if I wanted to update all the records. The thing is, it was the amount of records from the [ASSEMBLY] table and not the [ProductionHeaders]. Like it was wanting to update the [ASSEMBLY]. If the [ASSEMBLY] table is linked to another table in another database could this cause an issue?
    Attached Files Attached Files

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    Ok so if you took out criteria and ran Select query, it returns all the records where the IDs are equal right?

  11. #11
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    Try this
    Like "*" & [AssemblyName] & "*"

  12. #12
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    Yes, But seemed to me it was trying to update ASSEMBLY and I need it to update ProductionHeaders. Production headers has many many more records that would be updated. Sorry, but I do not understand what you mean by qualify AssemblyName.......I tried using that in the criteria with no success.

    Question. How does this query with this criteria check to see if AssemblyName is contained in ItemName? I am not sure I understand. It is supposed to compare AssemblyName to ItemName. If AssemblyName is contained in ItemName the it should update ItemDes in ProductionHeaders to ItemDes from the ASSEMBLY table. But there is no mention of ItemName anywhere. Thanks for your patience with me on this.

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    Your update query looked right just use this in the criteria instead of the original:
    Try this
    Like "*" & [AssemblyName] & "*"

    The criteria above is checking ItemDes from ProductionHeaders. If you change it to a select query you can see, it should pull back those records you want to update.

    As always, make sure you have a backup of the table incase it does not update like you want.

  14. #14
    MPXJohn is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    58
    That did not work either. Just a note. From post #10. The records do not return when the criteria is removed and Select query is run. I had run the update query without the criteria and it was trying to update all the records from the ASSEMBLY table.

  15. #15
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    Check the ID field in both tables that is linked, are those the correct fields to link by? Does ID in ProductionHeaders have a corresponding ID in ASSEMBLY?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-31-2016, 03:17 PM
  2. Replies: 5
    Last Post: 01-18-2016, 07:56 AM
  3. Replies: 3
    Last Post: 05-21-2015, 11:26 AM
  4. Query field based upon dates in other fields
    By Demiurgous in forum Queries
    Replies: 8
    Last Post: 11-03-2014, 12:56 PM
  5. Replies: 11
    Last Post: 06-13-2012, 08:14 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