Update Query & Expression Help

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

Guest

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
 
-----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-----
 
Your post was somewhat helpful; I can see I probably need to invest in an SQL
book to refresh my memory! ;o)

Actually I am working with 4 columns of numeric values which are statistical
weights of value 1-5, no monetary values. After examining each of the 4
fields, I have to determine what the Max value is, and just populate another
empty field in the MASTER table with that Result. (There is no Where
criteria, not that complex.)

The other problem is just that simple also - multiple values from 2
different fields together, and plug result into an empty field in the MASTER
as well.

The code you gave me for both the problems - looks like it will work for
what I need. Thanks so much for the refresher and help with SQL!
Bobo


MGFoster said:
-----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-----

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
Click to expand...
Click to expand...
 
Back
Top