Improve company productivity with a Business Account.Sign Up

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

Update query

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
johnnyg123
Asked:
johnnyg123
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Alex MatzingerDatabase AdministratorCommented:
Update table TechList set partsLabor=
     CASE
          WHEN (TotLaborPartsALL > TotLaborPArtsTA +  TotLaborPartsXTR)
                    THEN totLaborPartsall - (totlaborPartsta - totlaborpartsxtr)
          ELSE 0
      END
0
 
Rey Obrero (Capricorn1)Commented:
test this

update TechList
set PartsLabor=iif([TotLaborPartsALL] > (nz([TotLaborPArtsTA] +  nz([TotLaborPartsXTR])), [TotLaborPartsALL]-(nz([TotLaborPArtsTA] - nz([TotLaborPartsXTR])),0)
0
 
Alex MatzingerDatabase AdministratorCommented:
sorry, the first line should be

Update TechList set partsLabor =
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
HainKurtSr. System AnalystCommented:
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
 
johnnyg123Author Commented:
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
 
HainKurtSr. System AnalystCommented:
the one worked is the one I posted but you did not give me any points :(
0
 
Rey Obrero (Capricorn1)Commented:
HainKurt,
sorry but, the query you posted will give you an error.
0
 
HainKurtSr. System AnalystCommented:
:) I forgot to remove keyword "table" from the query :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now