Doing a lookup of a subset of values

[hcisteve]

I am relatively new to Access and I am unclear on how to solve this problem. I have one table that contains the entire number of people in our company. People want to be able to send me a list of names that are a sub-set of the people in the company. I can create a second table containing those names. I want to be able to create a query that will let me lookup that subset of names that people have given me and update a field in the main table. Everything I have tried in Design view seems to give me an error message or doesn't work.

 

[jhall01]

Don't know what you mean by "relatively new" so I apologize if this is too basic!!

1.Create a query with table1 (main) and table2 (subset)

2.click and drag common field from table1 to the common field in table2

3.drag the main field you want to update down

4. Set Query Type to Update Query

5. type the [table2].[column] in the "Update To:" field for the column you want to update in table1

 

[hcisteve]

Thanks for your reply. I don't think I was clear enough. The field that gets updated is different from the field that is a subset and the one that is a superset. But in all cases where the subset field exists in the superset field I want to update the other field. I followed your instructions but it seems that I can't get the database to do the initial lookup to determine if the subset fields in the first table are in the second table.

 

[hcisteve]

I think part of my problem has to do with the formating of my data. It is listed in the form of

Lastname, Firstname MI

I was reading that Access treats commas as "And"

 

[hcisteve]

I solved it. I eliminated all commas from both columns. I also eliminates all spaces -- for some reason there were extra spaces before and after some of the names. That seems to have fixed it. There probably was a more elegant method of doing this other than removing all commas and spaces but this did work. Thanks again for your help with the vairable syntax!!

Steve