update query - update yes/no in one table if record in second table

[misscrf]

Sorry, if my title was a bit lame. I have a database shell that I created to use for 5 identical projects. It is a boiler plate, so that I don't have to recreate it. We are now starting the second project. I have received the data as excel spreadsheets. I imported them in to the access database back end, so that I could append them to the main table - tblmembers, and the address table, tbladdress. These are linked based on the tblmembers auto pk id.

Now I have one other spreadsheet that I was given. This is a list of the members that chose not to be members anymore. I have a yes/no field in the main table, tblmembers. I need to update the notmember yes/no with a yes check, if the members member# is listed in the exclusion table.

I have never really gone far with update queries so I am kind of at a loss. Can someone help?

Thanks!

Here is a pic of the two tables, the tbl members which needs to be updated, and the excludedmembersraw with the member records I need to update. The owner# in the excludedmembersraw is = to the vendor field in tblmembers. The number is unique.

I appreciate any help you can give.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[lynchg]

Just a suggestion as far as the name for the yes/no field, I would call it Active, and put 'Yes' there if they are active and 'No' if they are inactive. A Yes in the NotMember field could confuse people.

It is not apparent what the common field(s) is/are between your 2 tables, but do something like this:

Update tblMembers
Set Active = yes
Where VENDR Not In
(Select yourVendorField
From ExtendedMembersRaw)

 

[misscrf]

Thanks for the response.

I guess I didn't explain that well. The notmember field is a data type of yes/no. That means it's a check box. My second question has to do with your suggested query statement. You put "Not In". Don't I want In? I want to set the notmember field to -1 (checked) where the vendr field value is in the excludedmembersraw. Right?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[lynchg]

My suggestion was to switch that Yes/No field to be Yes when someone is active. You don't have to follow it, it just seems clearer than saying "Yes, I am not a member.

If you keep it as NotMember, then you would want IN instead of NOT IN.

 

[misscrf]

Ahhh. I see what you mean. Actually there will be very few that are not members, and the check box is in position to freeze forms and show red as in you just found a person that is not a member. Make sense?

I tried that with In and it said was going to update 0 records.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[lynchg]

You have to match on a common field or fields. I can't tell what that would be.

 

[misscrf]

I figured it out. the exclusion list was not in the main list, so there would be none to update. Instead, I ended up appending the exclusion list in, and marking them during the append. lol. Anyway, thanks for the reponses. It kept me working on it til I got the result I needed.

misscrf

It is never too late to become what you could have been ~ George Eliot