Row count value evaluated against different thresholds.

[aaron.usa]

I humbly ask your expert opinion in an effort to save me countless
hours of trial and error.

A table exists called 'BandScore' in it are five diff't values.

ScoreA = 5
ScoreB = 4
ScoreC = 3
ScoreD = 2
ScoreE = 1

Counting the rows in the 'analyze' table I need to use that value to
determine which score gets applied and place that value in a table
called 'scores' field called 'gapscore' (until I understand the full
process, at which time I can put all of these calcs into one
statement, I'm hoping)

This score will be derived from the following statement;

If the row count from the analyze table > 15 then [Scores].[gapscore]
= [BandScore].[ScoreA]
If the row count from the analyze table > 11 <= 15 then [Scores].
[gapscore] = [BandScore].[ScoreB]
If the row count from the analyze table > 7 <= 11 then [Scores].
[gapscore] = [BandScore].[ScoreC]
If the row count from the analyze table >3 <= 7 then [Scores].
[gapscore] = [BandScore].[ScoreD]
If the row count from the analyze table <3 then [Scores].[gapscore] =
[BandScore].[ScoreE]

Oh wise ones, how would you make this happen?

I certainly thank you and offer mad adulation for any assist.

Kind Regards,
Aaron

 

[Douglas J. Steele]

You're not going to be happy with this, but the proper way to achieve what
you're trying to do would be to redesign your table.

You've got what's known as a repeating group, and that's a violation of
database normalization.

Take a look at the treatise by Tom Ellison that Allen Browne has at
http://www.allenbrowne.com/ser-58.html

There's also what I wrote in my December, 2004 "Access Answers" column in
Pinnacle Publication's "Smart Access". (Note that this is actually an
example of the approach Tom prefers to avoid). You can download the column
(and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[aaron.usa]

You're not going to be happy with this, but the proper way to achieve what
you're trying to do would be to redesign your table.

You've got what's known as a repeating group, and that's a violation of
database normalization.

Take a look at the treatise by Tom Ellison that Allen Browne has athttp://www.allenbrowne.com/ser-58.html

There's also what I wrote in my December, 2004 "Access Answers" column in
Pinnacle Publication's "Smart Access". (Note that this is actually an
example of the approach Tom prefers to avoid). You can download the column
(and sample database) for free athttp://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

I humbly ask your expert opinion in an effort to save me countless
hours of trial and error.

A table exists called 'BandScore' in it are five diff't values.

ScoreA = 5
ScoreB = 4
ScoreC = 3
ScoreD = 2
ScoreE = 1

Counting the rows in the 'analyze' table I need to use that value to
determine which score gets applied and place that value in a table
called 'scores' field called 'gapscore' (until I understand the full
process, at which time I can put all of these calcs into one
statement, I'm hoping)

This score will be derived from the following statement;

If the row count from the analyze table > 15 then [Scores].[gapscore]
= [BandScore].[ScoreA]
If the row count from the analyze table > 11 <= 15 then [Scores].
[gapscore] = [BandScore].[ScoreB]
If the row count from the analyze table > 7 <= 11 then [Scores].
[gapscore] = [BandScore].[ScoreC]
If the row count from the analyze table >3 <= 7 then [Scores].
[gapscore] = [BandScore].[ScoreD]
If the row count from the analyze table <3 then [Scores].[gapscore] =
[BandScore].[ScoreE]

Oh wise ones, how would you make this happen?

I certainly thank you and offer mad adulation for any assist.

Kind Regards,
Aaron- Hide quoted text -

- Show quoted text -

It's a new table and DB so re-design is not out of the question.

Those links will give me the answer I seek?

Thanks for the msg.

Best!
Aaron

 

[francislovenfosse]

ne m'envoyer plus rien merci