1

I have a database of U.S. Congressional information whereby I have FirstName, MiddleName, LastName, District, State, Party, etc. of each Congressman in the 112th Session of the U.S. House of Representatives and I want to add Committee information for each (i.e. what committee(s) they are on). I have a large table (called StateCommittee) of District | State | Committee as such:

Financial Services              5   NY
Foreign Affairs                 5   NY
Judiciary                       24  FL
Science, Space, and Technology  24  FL
Appropriations                  4   AL

that marks which committee is assigned to which district. I also made a field in my Representatives table called Committees, which is a multiple-value lookup field to another table I have called List of Committees. Ideally, I would like to create an Update query that generally "fills in" [Representatives].Committees.Value (the aforementioned multiple value field) with the appropriate Committees (ideally from the List of Committees table) when the two have identical district and state. So, ideally, it would look like this in pseudocode:

Update [Representatives].Committee.Value
SET [Representatives].Committee.Value = [StateCommittee].Committee
WHERE [Representatives].District = [StateCommittee].District And [Representatives].State = [StateCommittee].State

However, when I put into the Update query exactly the above code, Access produces the following error:

syntax operation error ".

Does anyone have any ideas as to what I am doing wrong or if what I want to do is not actually possible? Thanks so much!

user791411
  • 149
  • 12
  • Don't use multi-value fields. They are there for compatibility with Sharepoint and you're not using Sharepoint. If you have enough chops to post on SO, you know enough how to do it correctly. – David-W-Fenton Jun 12 '11 at 22:13

1 Answers1

-1

It looks to me like you are you using a 1-to-many join between the Representatives table and the State Committee table (as many representatives will be on multiple committees). You can not update the any of the 1-table records on a 1-to-many join. That is pretty standard SQL.

Doug
  • 1