Awarding points

B

Bergjes

Hi,
can somebody help with the following:

I have 2 tables:
1. Etappe
- number
- result1
- result2

2. Voorspeling
- name
- prediction1
- prediction2

Now there must be created a 3rd tabel with the "Scored points" of each
name per etappe. (There are 15 names and 21 etappe)
The scored points are as follows: if result1 = predictionX then 20
points else 0; if result2 = predictionX then 15 points else 0

Please advice?
Erik
 
J

Jason Lepack

What realtionship is there between an Etappe and a Voorspeling? Or,
does one Voospeling score points on all Etappe?

Based on the loose translations I could find, an Etappe is a "Stage",
and Voorspelling is a "Forecast". So we're obviously attempting to
gues the outcome of some event.

I would expect that each Event (Etappe) has a number and multiple
results (is it always going to be 2 or will it change?)

Then I'm assuming the Voorspelling is a persons guesses, so the name
is the persons name. Do they always get only two guesses?

Cheers,
Jason Lepack
 
B

Bergjes

Hi Jason,
thanks for your quick response.

Your presumptions are true.
Etappe = Stage and Voorspelling = Forecast and Yes we are attemting to
gues the outcome of the "Tour de France 2007".

The person has to predict 10 names who will score most points over all
stages. You can score points if one of your riders finishes within the
top 10 of each stage.
So for each stage you can win points.

Erik
 
J

Jason Lepack

Do you have an email that I can send an example to?

Just send me an email at jlepack <--at--> gmail <--dot--> com
 
B

Bergjes

Hi Jason,

Many thanks for your work.
I think I can use your database, and will work at it the coming days
to make it my own.
There are a few items I want to build in. I will try it myself, but if
I don't succeed I hope you can help me again. For now thanks again.

Erik
 
B

Bergjes

Hi Jason,

here I am again.

First I want to thank you for your quick reply's and the sample
database, great work!!!!!

Perhaps you can help me with the following.
I have to create a daily ranking of the stages, I need them for
calculating daily "money" prices.

I followed the instruction on http://support.microsoft.com/kb/q120608/
, and that almost worked.
The problem is that the ranking is been calculated for "all" records,
not just per stage.

Is it possible to do this with just one query, or do I have to create
multiple queries (as much as there are stages)?

This is the code I used (based on your query
points_for_picker_by_stage)
Ranking: (Select Count(*) from points_for_picker_by_stage Where
[Points] > [points_for_picker_by_stage_1].[Points]) + 1

Thanks,
Erik
 
J

Jason Lepack

You will need two queries. points_for_picker_by_stage, as well as
another one to rank the records.

To rank by stage, you also have to add a comparison between the stages
to the where clause of the subquery.

I used this:
Rank: (select count(*) from points_for_picker_by_stage as B where
B.stage_name = A.stage_name and B.points > A.points)+1

I have changed the stage_number field to a text field and called it
stage_name, you can substitute stage_number if that is still the
case. I aliased points_for_picker_by_stage to A in the main query and
B in the subquery. Notice the comparison of the stage_name, that's
the key to getting the rank by stage.

Cheers,
Jason Lepack
 
B

Bergjes

Jason, YOUR THE MAN!!!

Works like a charme.

Where have you learned working with databases like that?

I want to learn that too.

I'll be back.

Erik
 
J

Jason Lepack

See comments inline...

Jason, YOUR THE MAN!!!

Works like a charme.

Where have you learned working with databases like that?
Click to expand...

Google Groups, and actually working with databases.
I want to learn that too.
Click to expand...

This case is simply a logic thing.

A persons ranking is based on his points. If a person has none higher
than himself then he is 1st, 1 higher, 2nd, etc. Notice the pattern
that the number above + 1 = the rank.

Therefore we want to get the count of how many people have points
higher than the record.
select count(*) from B where B.points > A.points

This is a subquery, it's assuming that there is another query going on
above this, the whole query would look like this:
select
A.person_id,
A.points,
(
select
count(*)
from
A as B
where
B.points > A.points) as Rank
from
A

So for each person_id in A, the subquery for Rank selects the amount
of person_id's that have points greater than A's points.

The next step is to add 1 of course to get the rank.

You're specific instance has multiple stages, well we are counting the
amount of people in B with points higher than A then we only want to
compare the people who have the same site as A.

-- This wouldn't work because it would compare all points, not just
points within the site
select
A.person_id,
A.site_id,
A.points,
(
select
count(*)
from
A as B
where
B.points > A.points) as Rank
from
A

-- This will do it
select
A.person_id,
A.site_id,
A.points,
(
select
count(*)
from
A as B
where
B.site_id = A.site_id
and B.points > A.points) as Rank
from
A

Again, it's all about thinking through what you want to do, the query
comes from the logic.
I'll be back.
Click to expand...

Thanks for the warning ;)

Cheers,
Jason Lepack
 
B

Bergjes

Hi Jason,

I said I'll be back, so here I'am.

I hope you're kind enough to help me. I think I want to much to soon.
Perhaps it's better for me to start with something more simple. Anyway
I hope you can help me once again with this problem.

I want to calculate daily money prices for the first 5 places.
For the last few years I calculated this with a excel sheet I made,
but I wonder if this is possible with access to?
I send you the excel sheet, so you can see for yourself how things are
been calculated. (note that I use "Altrank" for ranking the Pickers).
Hope to here from you soon.

I tried some things in access but I can't figure out where to start.
I made a tabel "ratio", I made a cross-query for summing the places
but I can't use them together to multiplie the ratio with the sum of
the places.
Please help me.
 
Top