Updating a Table's values with a Query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that I need to run a query against, to populate certain fields
because they are blank and can change. I am getting a Syntax error. Please
can someone take a look, what am I doing wrong? Been a while since I've don
SQL.

SELECT MASTER.IPT, MASTER.YRKEY, MASTER.KEY, MASTER.RISK, MASTER.RLSCORE,
MASTER.RISKLVL, MASTER.LKLYHD, MASTER.PERF, MASTER.SAFETY, MASTER.COST,
MASTER.SKED, MASTER.[MAX SCORE], MASTER.[CSQ PRTY SCORE], MASTER.WLIST,
MASTER.CATEGORY, MASTER.TYPE, MASTER.PERFORMER
FROM MASTER

** examine 4 fields and populate MAXSCORE based on highest value found:
SET MASTER.MAXSCORE = IIF(MASTER.PERF > MASTER.SAFETY and MASTER.PERF >
MASTER.COST and MASTER.PERF > MASTER.SKED), MASTER.PERF
IIF(MASTER.SAFETY >
MASTER.PERF and MASTER.SAFETY > MASTER.COST and MASTER.SAFETY > MASTER.SKED),
MASTER.SAFETY
IIF(MASTER.COST >
MASTER.PERF and MASTER.COST > MASTER.SAFETY and MASTER.COST > MASTER.SKED),
MASTER.COST
IIF(MASTER.SKED >
MASTER.PERF and MASTER.SKED > MASTER.SAFETY and MASTER.SKED > MASTER.COST),
MASTER.SKED

** populate RLSCORE based on result:
SET MASTER.RLSCORE=(MASTER.LKLYHD*MASTER.MAXSCORE)

** populate RSKLVEL value based on result:
SET MASTER.RSKLVL=IIF(MASTER.LKLYHD*MASTER.MAXSCORE>12), HIGH
SET MASTER.RSKLVL=IIF(MASTER.LKLYHD*MASTER.MAXSCORE<5), LOW
SET MASTER RSKLVL=IIF(MASTER.LKLYHD=2), LOW
SET MASTER RSKLVL=IIF(MASTER.MAXSCORE=3), LOW

** populate CSQPRTYSCORE base on result:
SET MASTER.CSQ PRTY SCORE= (MASTER.PERF*MASTER.SAFETY*MASTER.COST*MASTER.SKED)

** update ALL records
UPDATE MASTER
** final query extract will contain only WLIST=yes items
WHERE (((MASTER.WLIST)=Yes))
ORDER BY MASTER.IPT, MASTER.KEY;
 
HIGH AND LOW are TEXT Values and therefore should be surrounded by quotes.
If any one of the set Perf, Safety, Cost or Sked values are ever null, the
comparison will fail.

UPDATE MASTER
SET MAXSCORE = SWITCH(MASTER.PERF > MASTER.SAFETY and
MASTER.PERF > MASTER.COST and
MASTER.PERF > MASTER.SKED, MASTER.PERF,
MASTER.SAFETY > MASTER.PERF and
MASTER.SAFETY > MASTER.COST and
MASTER.SAFETY > MASTER.SKED, Master.Safety
MASTER.COST > MASTER.PERF and
MASTER.COST > MASTER.SAFETY and
MASTER.COST > MASTER.SKED, MASTER.COST
MASTER.SKED > MASTER.PERF and
MASTER.SKED > MASTER.SAFETY and
MASTER.SKED > MASTER.COST, MASTER.SKED)
WHERE WList = True

Run that and then you could update the other fields in a second query. But the
main problem is probably due to a design issue with your tables and fields.

UPDATE Master
SET MASTER.RLSCORE=(MASTER.LKLYHD*MASTER.MAXSCORE),
MASTER.CSQ PRTY SCORE=(MASTER.PERF*MASTER.SAFETY*MASTER.COST*MASTER.SKED),
RskLvl = SWITCH (MASTER.LKLYHD*MASTER.MAXSCORE>12,"HIGH",
MASTER.LKLYHD*MASTER.MAXSCORE<5,"LOW",
MASTER.LKLYHD=2,"LOW",
MASTER.MAXSCORE=3,"LOW",
True,"Unknown")
WHERE WList = True

Using the Switch function means that the first condition that tests True will be
the one that applies the value. The remaining tests will be ignored.
I have a table that I need to run a query against, to populate certain fields
because they are blank and can change. I am getting a Syntax error. Please
can someone take a look, what am I doing wrong? Been a while since I've don
SQL.

SELECT MASTER.IPT, MASTER.YRKEY, MASTER.KEY, MASTER.RISK, MASTER.RLSCORE,
MASTER.RISKLVL, MASTER.LKLYHD, MASTER.PERF, MASTER.SAFETY, MASTER.COST,
MASTER.SKED, MASTER.[MAX SCORE], MASTER.[CSQ PRTY SCORE], MASTER.WLIST,
MASTER.CATEGORY, MASTER.TYPE, MASTER.PERFORMER
FROM MASTER

** examine 4 fields and populate MAXSCORE based on highest value found:
SET MASTER.MAXSCORE = IIF(MASTER.PERF > MASTER.SAFETY and MASTER.PERF >
MASTER.COST and MASTER.PERF > MASTER.SKED), MASTER.PERF
IIF(MASTER.SAFETY >
MASTER.PERF and MASTER.SAFETY > MASTER.COST and MASTER.SAFETY > MASTER.SKED),
MASTER.SAFETY
IIF(MASTER.COST >
MASTER.PERF and MASTER.COST > MASTER.SAFETY and MASTER.COST > MASTER.SKED),
MASTER.COST
IIF(MASTER.SKED >
MASTER.PERF and MASTER.SKED > MASTER.SAFETY and MASTER.SKED > MASTER.COST),
MASTER.SKED

** populate RLSCORE based on result:
SET MASTER.RLSCORE=(MASTER.LKLYHD*MASTER.MAXSCORE)

** populate RSKLVEL value based on result:
SET MASTER.RSKLVL=IIF(MASTER.LKLYHD*MASTER.MAXSCORE>12), HIGH
SET MASTER.RSKLVL=IIF(MASTER.LKLYHD*MASTER.MAXSCORE<5), LOW
SET MASTER RSKLVL=IIF(MASTER.LKLYHD=2), LOW
SET MASTER RSKLVL=IIF(MASTER.MAXSCORE=3), LOW

** populate CSQPRTYSCORE base on result:
SET MASTER.CSQ PRTY SCORE= (MASTER.PERF*MASTER.SAFETY*MASTER.COST*MASTER.SKED)

** update ALL records
UPDATE MASTER
** final query extract will contain only WLIST=yes items
WHERE (((MASTER.WLIST)=Yes))
ORDER BY MASTER.IPT, MASTER.KEY;
Click to expand...
 
Back
Top