Derived Value

[goslincm]

I have the following query and I want to change it slightly to that instead of all scores being calculated, I can have a parameter where I have the following groups: 0-4, 5, 6-7, 8-10.

Can I do that from within this same query?

SELECT Year3TempDistressFactorsPop.[1Score], Year3TempDistressFactors.[2Score], Year3TempDistressFactors.[3Score], Year3TempDistressFactors.[4ScoreCounty], Year3TempDistressFactors.[5Score], Year3TempDistressFactors.[7Score], Year3TempDistressFactors.[8Score], Year3TempDistressFactors.[9Score], Consolidate.UnitName, ([1Score]+[2Score]+[3Score]+[4ScoreCounty]+[5Score]+[7Score]+[8Score]+[9Score]) AS Total, Consolidate.CountyCd, Consolidate.LocalUnitType
FROM (Consolidate INNER JOIN Year3TempDistressFactors ON Consolidate.ConsolidateID = Year3TempDistressFactors.ConsolidateID) INNER JOIN Year3TempDistressFactorsPop ON Consolidate.ConsolidateID = Year3TempDistressFactorsPop.ConsolidateID
WHERE (((Consolidate.LocalUnitType)="0"));

 

[Golom]

I see nine different things here that are called "Scores" including the "Totals" field. Which of them do you want to group?

Generally, if you create groups, then you can't have all those fields appearing. They will be included in one of the "groups" that you are creating.

 

[goslincm]

The field [total] holds the sum of each of the score fields. Someone could score a total of 2 on all scores, others 10.

So, within my [total] field I want to be able to group my local units by their [total] score.

 

[lespaul]

So if the Total is between 0 and 4, that's group 1 and if it's 5 then group 2, etc.?

Your biggest problem is your unnormalized struture....having Score1, Score2, etc is a flag that you are committing spreadsheet within your database. Read the fundamentals document linked below for more information on normalization.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[goslincm]

My 1score, 2score etc each represent the results of different indicators being computed. 1score stores the results of any population growth or decline over a period of years. 2score represents any growth or decline in taxable value over a different period of years. The individual and overall scores are needed for historical data over time, while cummulative scores are needed each year to compare one unit of government against another.

I realize you should not store calculated fields, however, at my entry level of experience here, I knew of no other way to take my information, compare various years, against various atributes (population growth, taxable value,etc) and come up with individual scores that would eventually end up representing 9 different indicators, and be able to store those scores for historical purposes.