Query Scoring a field

[goslincm]

I'm having a query score several fields for me: 1Score, 2Score, etc. I am running an update query to give me my score. It is set up like:

Field 1Score Yr1Population
Table DistressF TempDistressF
Updateto 1
Criteria >[Yr2Population]

However I need it to also be able to update to zero if the [Yr2Population] is <[Yr1Population] and I need it to update to 999 if either [Yr1Population] or[Yr2Population] is null.

I'm not certain how to do this in an update query so that my results will store into my table.

 

[rjoubert]

UPDATE [DistressF] SET [DistressF].1Score = IIf([TempDistressF]![Yr2Population] > [TempDistressF]![Yr1Population], 1, IIf([TempDistressF]![Yr1Population] > [TempDistressF]![Yr2Population], 0, IIf(IsNull([TempDistressF]![Yr1Population]) OR IsNull([TempDistressF]![Yr1Population]), 999, 999)));

 

[rjoubert]

Allow me to make one correction...change that last 999 at the very end to NULL.

UPDATE [DistressF] SET [DistressF].1Score = IIf([TempDistressF]![Yr2Population] > [TempDistressF]![Yr1Population], 1, IIf([TempDistressF]![Yr1Population] > [TempDistressF]![Yr2Population], 0, IIf(IsNull([TempDistressF]![Yr1Population]) OR IsNull([TempDistressF]![Yr1Population]), 999, Null)));

 

[goslincm]

Alright, that is great, however in doing this I realize that I cannot set the value to 999 as I need to cummulative all my scores later. So how would I set the value to "null" if either field [Yr1Population] or [Yr2Population] is null?

 

[rjoubert]

Change the 999 to Null

 

[rjoubert]

UPDATE [DistressF] SET [DistressF].1Score = IIf([TempDistressF]![Yr2Population] > [TempDistressF]![Yr1Population], 1, IIf([TempDistressF]![Yr1Population] > [TempDistressF]![Yr2Population], 0, IIf(IsNull([TempDistressF]![Yr1Population]) OR IsNull([TempDistressF]![Yr1Population]), Null, Null)));

 

[rjoubert]

By the way, what do you do with the 1Score field if Yr1Population and Yr2Population are equal?

 

[goslincm]

Rjoubert that is an excellent question as I just ran my scores had found the population the same in a few instances. If there is no change in population the 1score should be 0. They only receive a score of 1 is there is a loss in population.

 

[rjoubert]

UPDATE [DistressF] SET [DistressF].1Score = IIf([TempDistressF]![Yr2Population] > [TempDistressF]![Yr1Population], 1, IIf([TempDistressF]![Yr1Population] >= [TempDistressF]![Yr2Population], 0, IIf(IsNull([TempDistressF]![Yr1Population]) OR IsNull([TempDistressF]![Yr1Population]), Null, Null)));

 

[Golom]

You also need to mention table [TempDistressF] somewhere. You are referencing fields from it but you are not joining it to table [DistressF].

UPDATE [DistressF] D INNER JOIN [TempDistressF] T 
       ON D.[SomeField] = T.[SomeField]

SET D.1Score = 
IIf(T.[Yr1Population] IS NULL Or T.[Yr2Population] IS NULL, NULL,
IIf(T.[Yr2Population] > T.[Yr1Population], 1, 
IIf(T.[Yr1Population] > T.[Yr2Population], 0, [red]???[/red])));

Where [red]???[/red] is where you need to supply a value when the two fields are equal.

 

[goslincm]

Excellent, thanks so much. Now I will just work myself through the other 8 update queries so they are scoring simillar.