?
Solved

Update multiple fields with inner select

Posted on 2013-01-20
9
Medium Priority
?
303 Views
Last Modified: 2013-02-18
Hello,

I'm wondering if its possible to extend the following query to include multiple set statements based on different where clauses in one query?

Is there an easier way than repeating this query?

UPDATE ProfileData, SummaryTable SET ProfileData.CarValueO1 = [Total]*[CarValue]
WHERE (((SummaryTable.CarType)="Sedan") AND ((SummaryTable.NumberDrivers)="NumberDrivers1") AND ((SummaryTable.NumberClaims)="NumberClaims1"));

Open in new window


Thanks
0
Comment
Question by:AndyC1000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38798306
I have described the options to do UPDATE WITH JOIN in this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
the ms access syntax is at the end, in the additional comments, so yes, it's possible
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38798310
of course, you may need to combine that with IIF() expressions, but as your requirements is not 100% clear ...
0
 

Author Comment

by:AndyC1000
ID: 38800230
Thanks for your response.

Please provide syntax the to:
SET ProfileData.CarValueO2 = [Total]*[CarValue]
WHERE (((SummaryTable.CarType)="Sedan") And ((SummaryTable.NumberDrivers)="NumberDrivers2") And ((SummaryTable.NumberClaims)="NumberClaims2"));

within the update query below.

UPDATE ProfileData, SummaryTable SET ProfileData.CarValueO1 = [Total]*[CarValue]
WHERE (((SummaryTable.CarType)="Sedan") And ((SummaryTable.NumberDrivers)="NumberDrivers1") And ((SummaryTable.NumberClaims)="NumberClaims1"));

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.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38800241
I would go with something along like this (not tested):
UPDATE ProfileData, SummaryTable 
 SET ProfileData.CarValueO1 = IIF( AND( SummaryTable.NumberDrivers = "NumberDrivers1" ; SummaryTable.NumberClaims ="NumberClaims1") ; [Total]*[CarValue] ; CarValueO1 )
  , ProfileData.CarValueO2 = IIF( AND( SummaryTable.NumberDrivers = "NumberDrivers2" ; SummaryTable.NumberClaims ="NumberClaims2") ; [Total]*[CarValue] ; CarValueO2 )
WHERE ((SummaryTable.CarType)="Sedan") ;

Open in new window

0
 

Author Comment

by:AndyC1000
ID: 38800293
I ran the query its says: syntax error missing operator in query expression 'IIF( AND ( SummaryTable.NumberDrivers = "NumberDrivers1"; SummaryTable.NumberClaims = "NumberClaims1"); [Total]*[CarValue];

I was unable to save the query because of the syntax error.  I've attached an example database.
profiledb.accdb
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38800302
I used ";" you may need to use "," for the arguments...
0
 

Author Comment

by:AndyC1000
ID: 38800379
I replaced the semi-colons with commas no change.
0
 

Author Comment

by:AndyC1000
ID: 38803617
I made the following change to the query although now it says syntax error in UPDATE statement.

UPDATE ProfileData, SummaryTable 
SET ProfileData.CarValueO1 = IIF( SummaryTable.NumberDrivers = "NumberDrivers1" AND SummaryTable.NumberClaims ="NumberClaims1") , [Total]*[CarValue] , CarValueO1 ), 
ProfileData.CarValueO2 = IIF( SummaryTable.NumberDrivers = "NumberDrivers2" AND SummaryTable.NumberClaims ="NumberClaims2") , [Total]*[CarValue] , CarValueO2 )
WHERE ((SummaryTable.CarType)="Sedan");

Open in new window

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38865015
this should work better:
UPDATE ProfileData p
INNER JOIN SummaryTable  s
SET p.CarValueO1 = IIF( s.NumberDrivers = "NumberDrivers1" AND s.NumberClaims ="NumberClaims1") , [Total]*[CarValue] , CarValueO1 ), 
p.CarValueO2 = IIF( s.NumberDrivers = "NumberDrivers2" AND s.NumberClaims ="NumberClaims2") , [Total]*[CarValue] , CarValueO2 )
WHERE ((s.CarType)="Sedan");

Open in new window

references:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html           
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

765 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