How to set up a grid in a table

[Kingspur]

Hello everyone,

I am organising various athletics meetings. Different races are graded differently by their importance. I have a table 'Athletes' and a table 'Meetings' which shows which grade each race is.

Am I able to create a table which would be able to recognise how many points are awarded depending on the position an athlete comes.

I have a grid as follows

1st place 2nd place 3rd place
Grade A 10 7 4
Grade B 6 4 2
Grade C 3 2 1

So if someone wins a grade A race they earn 10 points. If they finish second in a Grade C race they get 2 points.

I would like to create a Race Results table, where you would enter the meeting and the placement.
I want to be able to just enter the placement and for the system to recognise
a) which grade the race is (it would get this information from the main 'meetings' table)
b) how many points need to be awarded to the athlete once the placement is entered.

Any help or suggestions would be fantastic.

many thanks in advance

 

[gemma-the-husky]

the easiest way is to have one table for these results

tblPoints
Grade Place Points

so your rows look like

A 1 10
A 2 7
A 3 4
B 1 6
B 2 4
B 2 2
etc

now if you know someone came second in a grade B race, you can do a lookup, on B,2 to find the points (4)

not tried offhand, but you may well be able to join this table to your results data, to avoid having to use the lookups.

the main point is not (necessarily) to store it as you would probably do in a spreadsheet

--------
doing it the way you presented it may be OK, as long as there are always points for 3 places. If sometimes you want to award points for more than 3 places (or even award fewer than 3 places), then it would get hard for you yiour way, whereas there's no extra work required at all, if the data is in a long thin table.

 

[Kingspur]

Thanks for that Gemma,

So when you say 'do a look-up', is that a process on Access or do you mean manually look up on the table.

The effect I am trying to achieve is to be able to enter the placement a certain athlete finished, and for the system to automatically assign the correct number of points based on the Grade/Placement information stored in another table.

Is that possible.
As you can no doubt tell, I have never really used Access before so am fumbling around in the dark.

thank you in advance

 

[DCrake]

Ok, if you have a table that holds the static information about each grade of race and the default value that a placement is worth, so fininshing second in a grade C race is worth 4 points.

When you are entering results for individual athlethes you would first pick the athlete from a table of participants. Then you would select the type of race, say 400m. You events table will already know that a 400m race is a grade C race, becuse you told it so when you added the event. So all you need to do is to enter the position the runner came in.

A lookup will say ok lookup the points awarded in the events table where the race equals 400m.

It should then find 4 points. This is then awarded to the runner.

Your lookup will look similar to this

Me.TxtPoints = DLookup("fldPoints","tblEvents","EventID=" & IntEventID)

Where Me.TxtPoints = the value given to the runner
fldpoints = the value to look for
tblEvents = which table to look in
EventID = which field to compare against
IntEventId = condition to compare with

CodeMaster:

 

[gemma-the-husky]

kingspur

what happens in Access is that you have what looks like a series of interrelated spreadsheets. you can handle these very flexibly -( much more so than a spreadsheet) in ways that draw different spreadsheets together

so if you have one spreadsheet that shows

competitor place
jim 1st
fred 2nd
mike 3rd

and another table that shows

place ponits
1st 5
2nd 3
3rd 2
4th 1

you can draw these table together toi give a pseudo table (its called a query) that shows

name position ponits
jim 1st 5
fred 2nd 3
mike 3rd 2

now this is made a bit harder because in your case you also need to involve the event grade, to decide which of the points plans you want (but definitely dont try and fix this by having more than one points awarded table). In your case, you might be able to construct a query to resolve this ie to use the race grade AND the position (i'm pretty sure this is doable, but i havent played with this to see exactly how). But even if its not achievable by a simple query, then instead en for each result you can write program code to lookup the points value, based on position AND the race grade. (in a similar fashion to the way you might do a vlookup in excel)

 

[Big Pat]

Hi Kingspur,

I've just set something up (very quick and dirty) along similar lines to what Gemma mentioned in her first reply. See attachment. It seems to me you can do the whole thing with three simple tables. One - just as Gemma described, Two - a list of the athletes, Three - a table to store the actual results.

Then it just needs a simple query to link all three together and you can sum all the points awarded.

I'd be interested to see what others make of this. Is this the right approach? (I haven't set up any formal relationships - are they needed?)

The whole thing has only three tables, one form for data entry and and one query to show the results.

But it works, as far as I can see. The only thing is after you've selected a race in the form, the combo box displays only the grade. I always wonder how you're SUPPOSED to handle this. I use DLookups to show the other fields I want to see (haven't bothered in this sample) but I know some folks think DLookups are the spawn of Satan. Never understood why though.

 

[gemma-the-husky]

i've not played around with this, but I would expect you could limit the points selector to a single grade of race, by introducing an unother table (query) to limit the points, without needing to do lookups

i will post this when i get a chance

 

[Kingspur]

I have attached my test database and also a typical Excel spreadsheet which is what I would be using to import the data into the 'Results' table.

I just don't know how to get the database to recognise that
eg- London is a grade A race therefore when I put in the 'results' table that somebody has come first in London it needs to attribute 10 points to that athlete.

I don't know whether what I am looking to do is possible?

Sorry for being such a pain and I really appreciate all your time so far

 

[gemma-the-husky]

try this uploaded dbs in access 97

open the form and see what the code on the button does, then look at the queries etc

the active race is stored in a public variable in the module


View attachment track.zip

 

[Kingspur]

I think my final hurdle is as follows:
In excel you can have a cell where you put a formula in based on other cells and it populates the answer.
I don't know if you can do the same sort of thing in Access though?

Basically I reckon I need to create a query or a report (who knows??) which will show the total number of points gained for all meetings per athlete

I have a Results table already in place-
So somehow I need Access to recognise the value in the 'meeting' field.
Lookup the same value in the 'meeting' table and attribute the correct race grade.

Once it knows the correct race grade, it then needs to recognise the value iof the 'position' field in the 'results' table and lookup both that same value in the 'Points' table and the race grade that it has already worked out previously

So it is a double lookup- in Excel speak it would be something like
IF grade='A' AND position= '1st' then display '10' points.

The more I try to explain it the more complicated it sounds.
I'm starting to think that this is not something that I am going to be able to achieve in Access?

Or it is going to be way beyond my ability if it can be done.

Well I'll wait to see if you Access experts can shed any light on this.

Many thanks as always for the time you are spending on this.

 

[gemma-the-husky]

did you look at the example i posted?

 

[Kingspur]

Yes thanks for that.

I have decided to go for a simple version of my original idea.
It makes it much easier for me.

So my final question is- I have a query set up which shows athletes and total points they have throughout the season.
I can get it to display in descending order so that the top athlete is at the top of the list.

The only thing I need to finish it off, is somehow to be able to insert a field which displays their position next to it.

eg- when I run the query, the top athlete has a '1', 2nd shows '2' etc.

Again, I have no idea if this is possible but it would be great to be able to add this as when I run the report, it is impossible to see at a glance where any particular athlete is in the rankings?

Any ideas?

many thanks

 

[gemma-the-husky]

when you run the report, yuo can add an automatic ranking number to achieve this, i think