Re: Update Query & Expression Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: MGFoster (me_at_privacy.com)
Date: 01/04/05 

Date: Tue, 04 Jan 2005 23:36:10 GMT

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you are trying to do is usually a BAD design in RDMSs 'cuz a
calculated column is generally calculated during a SELECT (report)
query.

Checking 4 column values to find the Max value probably means the design
of that table is incorrect. Since the resultant max value is the final
value, that means those 4 values have the same meaning and should be in
one column. Instead of a table something like this:

create table test (
   AccountID integer ,
   Year integer ,
   Qtr1 money,
   Qtr2 money,
   Qtr3 money,
   Qtr4 money
   Max_Qtrs money,
   constraint pk_test primary key (accountid, [year])
)

You should have something like this:

create table test (
   AccountID integer ,
   [Year] smallinteger ,
   Qtr byte,
   Value money,
   Constraint pk_test primary key (accountid, [year], qtr)
)

To find the max qtr value in 2004 you do something like this:

SELECT AccountID, max(value)
FROM test T
WHERE [year] = 2004
GROUP BY accountid

========== But to answer your question here're 2 updates:

=== Getting the max value of 4 columns:

UPDATE master_table
SET highest_max = IIf(col1 > col2 and col1 > col3 and col1 > col4, col1,
                   IIf(col2 > col1 and col2 > col3 and col2 > col4, col2,
                   IIf(col3 > col1 and col3 > col2 and col3 > col4, col3,
                   IIf(col4 > col1 and col4 > col2 and col4 > col3, col4
                   ))))
WHERE ... criteria ...

=== Setting one column value = result of 2 other columns:

UPDATE master_table
SET col5 = col7 * col8
WHERE ... criteria ... 

-- 
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQdsob4echKqOuFEgEQL0GACgzm+iTpmQxbKNMRfNDt+DGsF0X+MAoNbd
7KWDwtwu9NV3kT0W7lgYMB9L
=R/m9
-----END PGP SIGNATURE-----
Bobo wrote:
> I have a Query I want to run, to update a field in the MASTER table. I have to
> examine 4 fields each record of MASTER to find the Highest Max value; then I 
> want to update MAX SCORE in the MASTER table with the value.
> 
> I also need to take 2 different fields of a record, multiply the values 
> together, and plug the result into another field in the Master table.
> 
> I have tried to create some Update queries but I've been unsuccessful so 
> far, I keep getting an error.
> Please can anyone help

Relevant Pages

  • Re: Update Query & Expression Help
    ... book to refresh my memory! ... and plug result into an empty field in the MASTER ... > AccountID integer, ... > Qtr1 money, ...
    (microsoft.public.access.queries)
  • Re: TURMEL: What Does the Bible say on Interest Free Banking?
    ... JCT: I tried to join the Igbo World Forum group to respond ... Jct: Thomas 95: Jesus said: If you have money, ... his master replied. ... "Then another servant came and said, 'Sir, here is your ...
    (sci.engr)
  • Re: Master Ps Open Letter: Response to 50 Cent
    ... The record company with the biggest marketing ... Three, money don't make me, I make money. ... Ol' Percy - master of jumping on the bandwagon. ... were jocking Tupac shit at that time. ...
    (rec.music.hip-hop)
  • Re: ot: Why liberals and other intelligent people fear Sarah Palin
    ... it is a dangerous servant and a fearful master." ... Think how much money is paid ... clothing and if you get sick, ... We get to vote on them homos, then let's vote on the slavery ...
    (rec.gambling.poker)
  • Re: The Grateful Slave
    ... I am told he has the power to control my life. ... My master must understand the world better than I, ... he says only he can protect my money. ... My master cares about other masters, who don't have good slaves. ...
    (rec.photo.digital)