Final Results from 1 or 2 scores

[Rhonda B]

i have made a report that shows the scores and placings for contestants.
Some events have 2 sets of scores and so have two final results which get
added togeter and then divided by 2 to give a final percentage in the final
mark Box.
Then sometimes the same event has only 1 set of scores and 1 result and so
the report needs to reflect result 1 as the Final percentage
eg Place Contestants name Result 1 Result2 Final mark
-- 1 Betty Boo 56% 75%
65.5%

1 Betty Boo 56%
56%

ie. In the query under Final mark I have the following,
Final Mark=([Result1]+[Result2])/2 and this give the Final percentage.

however, if there is no [result2], then [result1] is the final result and
no calculation is needed just transfer the figure to the final percentage
box. the above formula doesnt workin this instance. what can I put into
the query to fix this?

Cheers RB

 

[Tom Wickerath]

Hi Rhonda,

Try using an Immediate IF statement:

IIf Function
Returns one of two parts, depending on the evaluation of an expression.

Syntax: IIf(expr, truepart, falsepart)

Final Mark=IIF(IsNull([Result2]), [Result1], ([Result1]+[Result2])/2)


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Larry Daugherty]

The best path to the solution you want starts back at the analysis of
what your application is to do and then creating a schema that
reflects the entities in play in your application and the
relationships between them.

Result1 and Result2 are what's called "repeating fields". When you
recognize that you're using repeating fields, take the fields out of
the current table and create a new table with just the information
from the repeating fields and relate it to the original table in a
one-to-many relationship. The original table is on the "one" side and
your new table is on the "many" side. Enforce Referential Integrity
and enable cascading deletes. Read up on Form/Subform in Access help
and go through the whole process of getting things to play well
together.

When you have things properly organized then running a totals query
against the results is a piece of cake (choose your own metaphor).
Divide the SumOfResults by CountOfResults. Works like a charm!

You might find it beneficial to lurk
microsoft.public.access.gettingstarted and
microsoft.public.access.tablesdesign for starters. Also, have a peek
at www.mvps.org/access if you haven't been there already. It's an
outstanding resource for Access developers.

I know that you wanted an immediate solution for your current problem.
There are ways to get there but they would just help you dig a deeper
hole. Getting the data defined and related correctly is the hallmark
of a maintainable and extendable application.

Good fortune with your development.

HTH