Stumped on an Update Query SQL

[sjr1917]

***SOLVED*** see end of thread

Three tables:
1) People with all the usual demographic fields plus a "Selected" field
2) Groups: ID, GroupName
3) PeopleXGroups: PeopleID, GroupID

I have first set all "Selected" field in table 1 to False

I need to create an update query that sets the "Selected" field in table 1 to TRUE for all rows in the subquery: PeoplerXGroups INNER JOIN People ON PeopleXGroups.PeopleID = People.ID WHERE PeopleXGroups.GroupID=Forms!frmMainMenu!cboGroups.Column(0)

The end goal is to make table 1 available for Word Mail Merge where only the people from the combobox selected group have their "Selected" field marked as true.

 

[Galaxiom]

Queries can only refer to the bound column of a combo.

If other columns need to be referenced then create a hidden textbox on the form with the ControlSource as the combo column and refer to the textbox in the query.

 

[sjr1917]

Yes. Thanks.
The combobox isn't the problem... I'm stuck on: once I've got a subquery which contains the people.id's who are members of the chosen group how do I use that to do the update query that actually marks the Selected field in table 1 = true for each person in the subquery.

 

[namliam]

Why update your source table, why not simply join the chosen group into your table?

 

[sjr1917]

What I need as a final result is a table that will work without Access running. I can take the People table with the proper "selected" fields marked true and call it from within Word to do a mail merge (or forward it to someone else for them to use likewise).

Don't see how I could do that with a union query where part of it depended upon the contents of a combobox or textbox in a closed Access module.

Am I missing something?

 

[namliam]

Dont you have the selected people saved in a table already that you are now using to update your source table?

You can run a merge off a query just as well as a table cant you?

 

[sjr1917]

namliam... That's a point I hadn't considered (running a merge off a query). Haven't had much experience at Word merges other than with existing tables.

Here is the SQL statement that does what I wanted:
UPDATE People SET People.Selected = TRUE
WHERE People.ID IN
(SELECT People.ID
FROM PeopleXGroups INNER JOIN People ON PeopleXGroups.PeopleID = People.ID
WHERE PeopleXGroups.GroupID=[Forms]![frmMainMenu]![cboGroups]);