Update Query based on a separate Table

[InQuery2004]

Hi all,

This is quite a basic question and I'm sure that it has been posted here some time ago, although I couldn't find it on using the search.

I want to update a field in one table "FieldA" using an update query. I want the update to be based on matching Field B in the updated table, and Field C in a separate table (both tables have the same design structure).

That is, I'd like my query to search each record in the Destination Table to see if its Field B matches a Field C in a separate Table. If there is a match, I'd like to Update Field A to the corresponding field A of the reference table.

I have tried to do this with an Update Query based on both Tables in design view. Thus far, it hasn't yielded any results.

Can someone please post the solution asap. Thanks so much for your help.

Regards,

Mike J.

 

[gromit]

See attached.

tblReference is the table with the source data. tblDestination is the table that is to be modified.

This is slightly different than your question in that I am comparing FieldB in the tblDestination with FieldB (not FieldC) in the tblReference. If both FieldB's are the same then I update tblDestination's FieldA with the corresponding value from tblReference.

hth,

- g

 

[InQuery2004]

Hi Gromit,

Thanks for the posted solution, although it seems pretty straightforward, the same approach doesn't work with my Tables.

Actually, I wasn't very clear in my initial requirements in that my issue is the exact same one that you solved (ie matching column A to column A in a reference table, and then updating the corresponding Column B based on that reference table).

Unfortunately it doesn't quite work on my tables. One key difference that I should note is that my Dest. and Reference tables, although sharing the same Field structure, are of different lengths (Dest. has about 1000 records, the reference has about 300). Also FYI, these tables are not previously related except for the inner join that is defined in the query on the two fields to be matched.

Strange thing is that when I try your solution (and my previous attempt) on my tables two things happen.

1. The query returns 5825 records. My destination table has 1165 records and my reference table has 348 records.

2. The Query does yield some results. The last 1000 records on the query (in Datasheet mode) show some Description fields actually updated. The trouble is, that for some reason, the query selectively matches records based on the contents of the matching field (Field b). I have manually checked the Fields and their contents all semm to be identical.

If anyone has any further insight to my problem, please donate knowledge generously.

Thanks,

Mike Jamal

 

[gromit]

If you are getting more records in the query than either the destination or the reference, then something may be suspect with the join. The join should be equal, you only want to select rows where the fields are equal [As a test, a "select" version of the query should only extract the rows that are to be updated.]

 

[YamiMarik777]

Reply;creating update query

I tried your example on mine, but I got an error can't update all records in the update query. My design is this. I have a VendAlias table (this list all different names that a particular vendor appears on each invoice. Then I have an Invoice table(Similar to a credit card bill i.e. StatDate, PostDate, TransDate....of course--Vendor, VendNum to relate to the VendAlias table. I want Access to be able to compare Vendor in both tables, if they match then put it in the VendNum field in Invoice table. Similar to VLookup in Excel.
Any help would be much appreciated.