Max Value Question

[delaap]

Hi,
I currently have a table which auto grows every month by creating a new field when a new calender month is upon us.
The new field holds the same type of info as the month before (Values between 0 AND 4).
What I'm trying to do is populate another (master) field with the highest value seen across all the fields.
I.e.

ID Jan2003 Feb2003 March2003 Master_Value
5555 0 3 1 ?

In the above example Master_Value should be populated with 3 as this is the highest value seen to date.
Can someone suggest a loop of sorts to check the highest value in all fields (except Master_Value) and populate Master_value, no matter how many new fields get created over the next few years.
Thanks
Matt

 

[r937]

the "loop of sorts" that i would suggest is a table redesign

that way you won't have to keep adding columns, and the logic to find the master value will not have to be re-programmed either


rudy

 

[SQLSister]

Have to agree with Rudy, this is an extremely poor design. You don;t want to have to change the data structure every month. This also makes it much harder to query data. So to get the current month's data you would have to change the query every month too. Very poor design!!! You really need to go read about relational database design. Incidentally, you want to avoid loop as much as possible in a relational database. THey are much more efficient using set operations.

Your table should be redesigned for the one to many relationship inherent in your data. TAble design should be
REcordID TimePeriod VAlue
555 Mar 2003 3
555 Apr 2003 2
556 Apr 2003 4

Then to get the master value for the RecordIDs, you use the following SQL

SElect Max(Value) from Table1 group by RecordID

Incidentally you need more specific field names as well, ID is a particulary bad choice for a field name. AND I only used Value in my example becasue you don't really specify what the value is. I would never actually use a field named that in a database. Trust me it is better to have longer field names that explain what the data is.