Update error

The error I am getting is

"Missing operator in query expression......"

And this is an alternate syntax:

UPDATE TB_Jul_Aug, [Distribution list]
SET TB_Jul_Aug.Dept = [Distribution list].DEPT, TB_Jul_Aug.segment = [Distribution list].segment
WHERE TB_Jul_Aug.NickName = [Distribution list].NickName 

Select allOpen in new window

It worked but I am getting its updating 0 rows.  Am I doing anything wrong?

That means that you have no records meeting the criteria:

TB_Jul_Aug.NickName = [Distribution list].NickName

Check for differences in leading/trailing spaces, etc ....

The nick names are in [Distribution list].NickName  and I am updating the NickName column in TB_Jul_Aug.NickName .  Is my syntax right?

Thanks

Not quite...

If you need to update the NickName column, your syntax should be:

UPDATE TB_Jul_Aug, [Distribution list]
SET TB_Jul_Aug.NickName = [Distribution list].NickName
WHERE TB_Jul_Aug.LinkingFieldName = [Distribution list].LinkingFieldName

Select allOpen in new window

What field 'links' the two tables?

Use that field name to replace "LinkingFieldName" in my query....

In explanation, the SET clause specifies the column that gets updated.

I think I know why its doing that, some of the nick names although different are pointing to the same dept and segment.  How do you rectify that.  Its as if the system will have to update two nicknames on the same record.  Bad data I guess.  Two people will get the same report based on the same dept and segment.

Thanks

What exactly needs to be updated and what field(s) define the relationship between the two tables?

If you are trying to update the NickName, you should be using a syntax similar to my last comment (NickName needs to be in the SET clause).

Maybe this?

UPDATE TB_Jul_Aug, [Distribution list]
SET TB_Jul_Aug.NickName = [Distribution list].NickName
WHERE TB_Jul_Aug.Dept = Distribution list.DEPT AND TB_Jul_Aug.segment = Distribution list.segment;

Select allOpen in new window

I did set the SET clause to NickName because NickName defines the relationship and that needs to be updated in the  TB_Jul_Aug.  I am going to eliminate the duplicate dept and segment  on the distribution list and see what happens.  I will keep you posted.

Thanks

It does not work, now back to the drawing board.  All I want is to update the master data in order to run a query and run a report. Since that is not working,do you guys have any other way  and means under your sleeve to do this without updates?  

For example if I want Sams departments I want to create a qurey  with Sams nickname and run all of Sam's report.  Hope I am clear on this.  Please help.

Thanks

What exactly doesnt work?  My first post addressed the syntax issues in your original question, and what you are asking now is different and unclear to me.  You should close this question and post a new one which clearly defines what you are trying to do.   Posting sample data and expected results may help.