?
Solved

Update query

Posted on 2011-02-22
8
Medium Priority
?
297 Views
Last Modified: 2012-05-11
I have the folllowing table named TechList
with the following sample data

TotLaborPartsAll    TotLaborPartsTA   TotLaborPartsXTR  PartsLabor
3324.73                   319.92                 1                              0  
400                          500                       2                             0



I am trying to write a query that will update partsLabor with the following

If TotLaborPartsALL > TotLaborPArtsTA +  TotLaborPartsXTR then totLaborPartsall - (totlaborPartsta - totlaborpartsxtr) else 0

Using the sample data above

 partsLabor in the first row should be set to 3003.81
(3324.73 - (319.92 + 1))

partsLabor in the second row should be set to 0 since TotLaborPartsALL is not greater than TotLaborPArtsTA +  TotLaborPartsXTR



0
Comment
Question by:johnnyg123
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34954649
Update table TechList set partsLabor=
     CASE
          WHEN (TotLaborPartsALL > TotLaborPArtsTA +  TotLaborPartsXTR)
                    THEN totLaborPartsall - (totlaborPartsta - totlaborpartsxtr)
          ELSE 0
      END
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34954670
test this

update TechList
set PartsLabor=iif([TotLaborPartsALL] > (nz([TotLaborPArtsTA] +  nz([TotLaborPartsXTR])), [TotLaborPartsALL]-(nz([TotLaborPArtsTA] - nz([TotLaborPartsXTR])),0)
0
 
LVL 4

Expert Comment

by:Alex Matzinger
ID: 34954671
sorry, the first line should be

Update TechList set partsLabor =
0
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
LVL 57

Expert Comment

by:HainKurt
ID: 34954704
Update table TechList
set
partsLabor=IIf(TotLaborPartsALL > TotLaborPArtsTA +  TotLaborPartsXTR, totLaborPartsall - (totlaborPartsta - totlaborpartsxtr),0)

assuming all have numeric values, if some may have null values, use NZ(col, 0) for all columns that may have null values like

Update table TechList
set
partsLabor=IIf(NZ(TotLaborPartsALL,0) > NZ(TotLaborPArtsTA,0) + NZ(TotLaborPartsXTR,0), NZ(totLaborPartsall,0) - (NZ(totlaborPartsta,0) - NZ(totlaborpartsxtr,0)),0)
0
 

Author Comment

by:johnnyg123
ID: 34954883
got the following to work


UPDATE TechList SET PartsLabor = IIf(nz(TotLaborPartsALL,0)>nz(TotLaborPArtsTA,0)+nz(TotLaborPartsXTR,0),nz(totLaborPartsall,0)-(nz(totlaborPartsta,0)+nz(totlaborpartsxtr,0)),0);
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 34955086
the one worked is the one I posted but you did not give me any points :(
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34955151
HainKurt,
sorry but, the query you posted will give you an error.
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 34957512
:) I forgot to remove keyword "table" from the query :)
0

Featured Post

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…
Suggested Courses

741 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