Query/Table fields based off value of another field.

[MPXJohn]

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.

[ranman256]

Create an update query. Join the 2 like columns, update description.
this is Basic query design.

[MPXJohn]

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.

[Bulzie]

On ItemName criteria:

LIke "*[Assembly]*"

[MPXJohn]

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.

[Bulzie]

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.

[MPXJohn]

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.

[Bulzie]

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.

[MPXJohn]

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?

[Bulzie]

Ok so if you took out criteria and ran Select query, it returns all the records where the IDs are equal right?

[Bulzie]

Try this
Like "*" & [AssemblyName] & "*"

[MPXJohn]

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.

[Bulzie]

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.

[MPXJohn]

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.

[Bulzie]

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?