[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Update error

What am I doing wrong?  I am getting a missing syntax error on this update that I am running in MS Access.

UPDATE TB_Jul_Aug INNER JOIN Distribution list ON TB_Jul_Aug.NickName = Distribution list.NickName SET TB_Jul_Aug.Dept = Distribution list.DEPT, TB_Jul_Aug.segment = Distribution list.segment;


Thanks
0
Kdankwah
Asked:
Kdankwah
  • 8
  • 6
1 Solution
 
KdankwahAuthor Commented:
The error I am getting is

"Missing operator in query expression......"
0
 
mbizupCommented:
Try this:

UPDATE TB_Jul_Aug INNER JOIN [Distribution list] ON TB_Jul_Aug.NickName = [Distribution list].NickName SET TB_Jul_Aug.Dept = [Distribution list].DEPT, TB_Jul_Aug.segment = [Distribution list].segment;

You need []'s when your field/table names contain spaces.
0
 
mbizupCommented:
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 

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
KdankwahAuthor Commented:
It worked but I am getting its updating 0 rows.  Am I doing anything wrong?
0
 
mbizupCommented:
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 ....
0
 
KdankwahAuthor Commented:
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
0
 
mbizupCommented:
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

Open in new window



What field 'links' the two tables?

Use that field name to replace "LinkingFieldName" in my query....
0
 
mbizupCommented:
In explanation, the SET clause specifies the column that gets updated.
0
 
KdankwahAuthor Commented:
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
0
 
mbizupCommented:
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).
0
 
mbizupCommented:
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;

Open in new window

0
 
KdankwahAuthor Commented:
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
0
 
KdankwahAuthor Commented:
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
0
 
mbizupCommented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now