Re: Update Query & Expression Help

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: 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: Please Help with Query Calculations
    ... As for the other fields I put SUM but its not summing them up. ... of the query, go up to View and select Totals. ... maybe a few more fields) and an Sum the Amounts Owed and Amounts Paid ... is several dates of when people were billed money and paid money. ...
    (microsoft.public.access.queries)
  • Re: Precision animation of model airplane
    ... How much money are you going to want to spend - and how big a ... learning curve are you willing to master? ... Probably be faster/cheaper to hire someone who has the software ...
    (comp.graphics.animation)
  • Re: BEWARE .darklightrecords.com/
    ... They take your money with no ... ship - just release the funds and they would ship. ... "here's the tracking number" but no tracking number was in the email. ... I have copies of everything so they can eat my master. ...
    (rec.audio.pro)