Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update query not updating

Posted on 2008-11-07
9
Medium Priority
?
359 Views
Last Modified: 2012-05-07
I'm trying to update a table using an update query.  The query seems to run fine, but none of the data is updated in the table.  I'm not sure if I'm missing something.  The query is below.
UPDATE tblFinSumNetInc INNER JOIN ABCTest2 ON (tblFinSumNetInc.DateOfData = ABCTest2.DateOfData) AND (tblFinSumNetInc.txtClientNumber = ABCTest2.CliNum) SET ABCTest2.CliNum = [tblFinSumNetInc].[txtClientNumber], ABCTest2.DateOfData = [tblFinSumNetInc].[DateOfData], ABCTest2.InterestExpense = [tblFinSumNetInc].[InterestExpense], ABCTest2.SalaryExpense = [tblFinSumNetInc].[SalaryExpense], ABCTest2.OperatingExpense = [tblFinSumNetInc].[OperatingExpense], ABCTest2.Taxes = [tblFinSumNetInc].[Taxes], ABCTest2.Distributions = [tblFinSumNetInc].[Distributions], ABCTest2.Notes = [tblFinSumNetInc].[NetIncomeNotes];

Open in new window

0
Comment
Question by:nfstrong
  • 6
  • 3
9 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22907405

UPDATE tblFinSumNetInc 
SET ABCTest2.CliNum = [tblFinSumNetInc].[txtClientNumber], ABCTest2.DateOfData = [tblFinSumNetInc].[DateOfData], ABCTest2.InterestExpense = [tblFinSumNetInc].[InterestExpense], ABCTest2.SalaryExpense = [tblFinSumNetInc].[SalaryExpense], ABCTest2.OperatingExpense = [tblFinSumNetInc].[OperatingExpense], ABCTest2.Taxes = [tblFinSumNetInc].[Taxes], ABCTest2.Distributions = [tblFinSumNetInc].[Distributions], ABCTest2.Notes = [tblFinSumNetInc].[NetIncomeNotes];
 
INNER JOIN ABCTest2 ON (tblFinSumNetInc.DateOfData = ABCTest2.DateOfData) AND (tblFinSumNetInc.txtClientNumber = ABCTest2.CliNum) 

Open in new window

0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22907424
actually use this
UPDATE tblFinSumNetInc 
SET ABCTest2.CliNum = [tblFinSumNetInc].[txtClientNumber], ABCTest2.DateOfData = [tblFinSumNetInc].[DateOfData], ABCTest2.InterestExpense = [tblFinSumNetInc].[InterestExpense], ABCTest2.SalaryExpense = [tblFinSumNetInc].[SalaryExpense], ABCTest2.OperatingExpense = [tblFinSumNetInc].[OperatingExpense], ABCTest2.Taxes = [tblFinSumNetInc].[Taxes], ABCTest2.Distributions = [tblFinSumNetInc].[Distributions], ABCTest2.Notes = [tblFinSumNetInc].[NetIncomeNotes];
 
FROM tblFinSumNetInc 
inner join ABCTest2 on (tblFinSumNetInc.DateOfData = ABCTest2.DateOfData) AND (tblFinSumNetInc.txtClientNumber = ABCTest2.CliNum) 

Open in new window

0
 

Author Comment

by:nfstrong
ID: 22907461
I get an error that there is syntax after the end of the statement.  I even tried the below code.  It runs the query but doesn't update the data.
UPDATE tblFinSumNetInc inner join ABCTest2 on (tblFinSumNetInc.DateOfData = ABCTest2.DateOfData) AND (tblFinSumNetInc.txtClientNumber = ABCTest2.CliNum)
SET ABCTest2.CliNum = [tblFinSumNetInc].[txtClientNumber], ABCTest2.DateOfData = [tblFinSumNetInc].[DateOfData], ABCTest2.InterestExpense = [tblFinSumNetInc].[InterestExpense], ABCTest2.SalaryExpense = [tblFinSumNetInc].[SalaryExpense], ABCTest2.OperatingExpense = [tblFinSumNetInc].[OperatingExpense], ABCTest2.Taxes = [tblFinSumNetInc].[Taxes], ABCTest2.Distributions = [tblFinSumNetInc].[Distributions], ABCTest2.Notes = [tblFinSumNetInc].[NetIncomeNotes];

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22907491
Oh sorry just saw that you were on access so the syntax may be a little different but don't you need a set for each one?
UPDATE tblFinSumNetInc inner join ABCTest2 on (tblFinSumNetInc.DateOfData = ABCTest2.DateOfData) AND (tblFinSumNetInc.txtClientNumber = ABCTest2.CliNum)
 
SET ABCTest2.CliNum = [tblFinSumNetInc].[txtClientNumber]
SET ABCTest2.DateOfData = [tblFinSumNetInc].[DateOfData]
SET ABCTest2.InterestExpense = [tblFinSumNetInc].[InterestExpense]
SET ABCTest2.SalaryExpense = [tblFinSumNetInc].[SalaryExpense]
SET ABCTest2.OperatingExpense = [tblFinSumNetInc].[OperatingExpense]
SET ABCTest2.Taxes = [tblFinSumNetInc].[Taxes]
SET ABCTest2.Distributions = [tblFinSumNetInc].[Distributions]
SET ABCTest2.Notes = [tblFinSumNetInc].[NetIncomeNotes];

Open in new window

0
 
LVL 12

Assisted Solution

by:Nathan Riley
Nathan Riley earned 200 total points
ID: 22907547
Nevermind you were right with your first query.  It's not the query its your data
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 22907566
Why are you update the rows that already match from your join?
UPDATE tblFinSumNetInc inner join ABCTest2 on (tblFinSumNetInc.DateOfData = ABCTest2.DateOfData) AND (tblFinSumNetInc.txtClientNumber = ABCTest2.CliNum)
SET 
ABCTest2.InterestExpense = [tblFinSumNetInc].[InterestExpense], ABCTest2.SalaryExpense = [tblFinSumNetInc].[SalaryExpense], ABCTest2.OperatingExpense = [tblFinSumNetInc].[OperatingExpense], ABCTest2.Taxes = [tblFinSumNetInc].[Taxes], ABCTest2.Distributions = [tblFinSumNetInc].[Distributions], ABCTest2.Notes = [tblFinSumNetInc].[NetIncomeNotes];

Open in new window

0
 

Author Comment

by:nfstrong
ID: 22907669
Ok, I removed the CliNum and DateOfData from the update.  I reran the query and it runs fine, but it still doesn't update the data.
0
 
LVL 12

Assisted Solution

by:Nathan Riley
Nathan Riley earned 200 total points
ID: 22907904
What are the columns in abctest right now?  Are they NULL?
0
 

Accepted Solution

by:
nfstrong earned 0 total points
ID: 22908057
That was the problem.  I had the SET statements backwards.  I was trying to update ABCTest2 to tblFinSumNetInc, but it was doing the opposite.  I changed the SET statements and now it works fine.  Thanks.
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question