I have a sheet with the following information

Team W L PCT Pyth %
Baltimore 38 49 0.437 0.48588214748205
Boston 53 34 0.609 0.612495589772804
Chicago Sox 39 47 0.453 0.415344230998688
Cleveland 52 36 0.591 0.564140709038061
Detroit 52 34 0.605 0.612782350342339
Kansas City 38 50 0.432 0.458356141848638

I would like to add a column that will compare PCT and Pyth %. If the former
is greater than the latter that field will have the term "Underrated" else it
will have the term "overrated." How do add a field conditionally? Is that
something that's done in design view?

You do this in a query. As a matter of fact, the percentages can and should
also be calculated in a query. Just add a query column:

Result: IIf([PCT] > [Pyth %], "Underrated", "overrated")

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"David Gerstman" <(E-Mail Removed)> wrote in message
newsD403CFD-7805-43B1-BC8E-(E-Mail Removed)...
>I have a sheet with the following information
>
> Team W L PCT Pyth %
> Baltimore 38 49 0.437 0.48588214748205
> Boston 53 34 0.609 0.612495589772804
> Chicago Sox 39 47 0.453 0.415344230998688
> Cleveland 52 36 0.591 0.564140709038061
> Detroit 52 34 0.605 0.612782350342339
> Kansas City 38 50 0.432 0.458356141848638
>
> I would like to add a column that will compare PCT and Pyth %. If the
> former
> is greater than the latter that field will have the term "Underrated" else
> it
> will have the term "overrated." How do add a field conditionally? Is that
> something that's done in design view?
>

Thank you very much.
And I assume I could nest the IIF statements if I had two criteria I need to
fulfill?

David

"Arvin Meyer [MVP]" wrote:

> You do this in a query. As a matter of fact, the percentages can and should
> also be calculated in a query. Just add a query column:
>
> Result: IIf([PCT] > [Pyth %], "Underrated", "overrated")
>

Yes you may. You might also look at the Choose() and Switch() functions in
VBA help, and a Select Case statement if you'd like to write your own custom
function to run in an Access query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"David Gerstman" <(E-Mail Removed)> wrote in message
news:C0E6083D-7623-4072-A0F8-(E-Mail Removed)...
> Thank you very much.
> And I assume I could nest the IIF statements if I had two criteria I need
> to
> fulfill?
>
> David
>
> "Arvin Meyer [MVP]" wrote:
>
>> You do this in a query. As a matter of fact, the percentages can and
>> should
>> also be calculated in a query. Just add a query column:
>>
>> Result: IIf([PCT] > [Pyth %], "Underrated", "overrated")
>>
>

Again thanks.

One last thing: How can I format the newly generated fields? (I followed
your advice and did the caluclations in the query ( e.g. pct: [W]/([W]+[L])
). Say I only want 3 decimal places or I want to round to the nearest
integer? Can I do that in the query or do I need VBA for that. (There's
nothing in the format menu for adjusting number formats.)

David
"Arvin Meyer [MVP]" wrote:

> Yes you may. You might also look at the Choose() and Switch() functions in
> VBA help, and a Select Case statement if you'd like to write your own custom
> function to run in an Access query.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "David Gerstman" <(E-Mail Removed)> wrote in message
> news:C0E6083D-7623-4072-A0F8-(E-Mail Removed)...

You can use both Standard and your own custom VBA functions in your Access
queries. Other than some limited ability in SQL-Server, Access is the only
DBMS that allows that. Check into the Round () and Format() functions in VBA
help. They will probably fill your requirements.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"David Gerstman" <(E-Mail Removed)> wrote in message
news:77803070-DE85-4819-92F5-(E-Mail Removed)...
> Again thanks.
>
> One last thing: How can I format the newly generated fields? (I followed
> your advice and did the caluclations in the query ( e.g. pct:
> [W]/([W]+[L])
> ). Say I only want 3 decimal places or I want to round to the nearest
> integer? Can I do that in the query or do I need VBA for that. (There's
> nothing in the format menu for adjusting number formats.)
>
> David
> "Arvin Meyer [MVP]" wrote:
>
>> Yes you may. You might also look at the Choose() and Switch() functions
>> in
>> VBA help, and a Select Case statement if you'd like to write your own
>> custom
>> function to run in an Access query.
>> --
>> Arvin Meyer, MCP, MVP
>> http://www.datastrat.com
>> http://www.mvps.org/access
>> http://www.accessmvp.com
>>
>> "David Gerstman" <(E-Mail Removed)> wrote in message
>> news:C0E6083D-7623-4072-A0F8-(E-Mail Removed)...
>