UPDATE query, multiple results

[nerbonne]

Hi, I'm pretty new to Access. I have an update query that is working, it compares to fields and if both of them match, it updates a third field in table 1 from a third field in table 2. The problem is that sometimes the data matches more than one time and I need both results to be inserted into the third field, possible separated by a comma? Below is the SQL view of my update query that I made in design view. Your assistance is most appreciated.

UPDATE table1 INNER JOIN table2 ON (table1.[t1_number] = table2.[t2_number]) AND (table1.[t2_rev] = table2.[t2_rev]) SET table1.t2_vid = [table2].[t2_vid];

 

[dhookom]

It would help if you provided some sample data. However, I think you might be able to use the generic concatenate function at faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

I need both results to be inserted into the third field"

Not to be cheeky or tell you what you need, but my guess is you really don't _need_ (or even want) to store your data like that. Generally data that looks like "thing, thing2, thing3" is a whole heckofalot harder to work with.

My second guess would be that if you provided some sample data like Duane suggests and some context/big picture about what you are trying to accomplish, maybe we could show you how to get there without compromising your data structure.