Newbie in trouble

[Julie McEwan]

Hi,

This is my first, and very new. I was wondering if anyone could help me. I
have a table with contacts for a sporting body, and I want to make a table
with points that have been earned for a particular event, and not sure the
best way to set this up, so that ultimately I will be able to see a "total
points earned" column. Does this happen through queries, or through the
reports? Any help on this would be great.

Best Regards
Julie

 

[Ken Snell [MVP]]

I'm not understanding how contacts, events, and points are related. Can you
give us more information about the data that you want to store in your
database, and what you'll want to do with it? That will help us suggest some
table setups for you to use (table setup is the very first, most important
part of creating your database).

 

[Al Camp]

Julie,
This is really a bit too "broad" a question to answer effectively.
You're really asking how to design the whole database, and you've given us
very little information to work with.
Perhaps we can just get a start...
1. Given the info you've provided, you'll first need to design a table with
3 fields.
Contact (text)
PointsDate (date)
PointsAwarded (numeric)
2. Build a form based on that table that allows you to enter the associated
information on the date the points are awarded.
3. Build a report that sorts the data by GameDate, Contact, and then sum the
points in the Report Footer.
= Sum(PointsAwarded)

You might consider an introductory Access course that might be available
in your area, or purchase a beginner's Access book before you get started.

Do the table first, and post back to the newsgroup when you run into
specific problems.

hth
Al Camp

 

[Julie McEwan]

Ok, sorry.

The only thing that I want this database to do, is keep track of the events
that have been played through the year, and the points that the person
earned in those events. There are 3 events per year, so it isn't very much
info, and I want to be able to have a "total sum" for the last 3 years. eg.
Event 1 in 2003 earned 2000 points, Event 2 in 2003 earned 1250 points, and
so on, until event 3 in 2005. I just want to display a total of all the
points for those 3 years.

It is a ranking points system, and the total rankings points will work as
follows

50% of rankings earned in 2003 will be retained
75% of rankings earned in 2004 will be retained
100% of rankings earned in 2005 will be reatined

All points added up at the end of this will give current ranking points.
Next year will be the same but it will be 2004-2006

I have created a table with contacts listed, eg. first name, surname,
address etc. Just not sure the best way to do the additions and sums that I
have mentioned above, and the best way to display them. I have worked it out
on excel, and can give the website address where I have displayed the
rankings points if that would help.

Am I in way over my head???? Be honest, I won't be offended. I just thought
that it seemed a fairly easy task.

Thanks for your time.

Julie

 

[gls858]

Ok, sorry.

The only thing that I want this database to do, is keep track of the events
that have been played through the year, and the points that the person
earned in those events. There are 3 events per year, so it isn't very much
info, and I want to be able to have a "total sum" for the last 3 years. eg.
Event 1 in 2003 earned 2000 points, Event 2 in 2003 earned 1250 points, and
so on, until event 3 in 2005. I just want to display a total of all the
points for those 3 years.

It is a ranking points system, and the total rankings points will work as
follows

50% of rankings earned in 2003 will be retained
75% of rankings earned in 2004 will be retained
100% of rankings earned in 2005 will be reatined

All points added up at the end of this will give current ranking points.
Next year will be the same but it will be 2004-2006

I have created a table with contacts listed, eg. first name, surname,
address etc. Just not sure the best way to do the additions and sums that I
have mentioned above, and the best way to display them. I have worked it out
on excel, and can give the website address where I have displayed the
rankings points if that would help.

Am I in way over my head???? Be honest, I won't be offended. I just thought
that it seemed a fairly easy task.

Thanks for your time.

Julie

My first thought was that this would be a much better fit with Excel.
Are the number of contacts static or do they change.

gls858

 

[John Vinson]

Ok, sorry.

The only thing that I want this database to do, is keep track of the events
that have been played through the year, and the points that the person
earned in those events. There are 3 events per year, so it isn't very much
info, and I want to be able to have a "total sum" for the last 3 years. eg.
Event 1 in 2003 earned 2000 points, Event 2 in 2003 earned 1250 points, and
so on, until event 3 in 2005. I just want to display a total of all the
points for those 3 years.

Ok... you're talking about events, and you're talking about points,
and you have a table of contacts. I don't understand from what you've
posted what (if anything) the contacts have to do with the points.
Does each Contact earn points?

Guessing that they do, you'll need the following tables:

Contacts
ContactID
LastName
FirstName
<other bio and contact data>

Events
EventDate Primary Key
<description of the event>

Scores
ContactID ' link to Contacts, who earned points
EventDate ' link to Events, where they earned them
Points Number ' how many points they earned at this event

A Totals Query grouping by Event (or by ContactID, or both, I'm not
sure how you want to do the totalling) and Summing the value of Points
will give you the total points for that grouping. The total need not
and should not be stored in any table; just recalculate it.

It is a ranking points system, and the total rankings points will work as
follows

50% of rankings earned in 2003 will be retained
75% of rankings earned in 2004 will be retained
100% of rankings earned in 2005 will be reatined

I have no trace of a clue what this means. "rankings"? You've
described points, but you have not described what you mean by
"ranking" nor by "retained". Do you want to delete points for some
events?

All points added up at the end of this will give current ranking points.
Next year will be the same but it will be 2004-2006

I have created a table with contacts listed, eg. first name, surname,
address etc. Just not sure the best way to do the additions and sums that I
have mentioned above, and the best way to display them. I have worked it out
on excel, and can give the website address where I have displayed the
rankings points if that would help.

Am I in way over my head???? Be honest, I won't be offended. I just thought
that it seemed a fairly easy task.

You may be a bit too close to the problem to explain it in a way that
us database geeks can understand. You know what you mean by "ranking"
and "retain" - I for one do not, and cannot figure out how to
translate it into database terms.

But I'm thinking that (once we're all communicating in each others'
jargon) this should be quite doable in Access.

John W. Vinson[MVP]

 

[Julie McEwan]

Sorry, again, like I said, very new at this. Would it help if i gave the
link of the points that I have calculated with excel?

 

[John Vinson]

Sorry, again, like I said, very new at this. Would it help if i gave the
link of the points that I have calculated with excel?

Not particularly. Could you explain in plain English what's going on?
What are "points"? How do you determine a "ranking"? What does it mean
to "retain"? Just explain it as if (as is the case) you were talking
to someone who knows absolutely nothing about the situation.

John W. Vinson[MVP]

 

[Julie McEwan]

Ok, we are a sporting body. There are 3 main events that happen in the year.
When a member wins a certain event they get a certain amount of points. So
the winner of the event will get for example 1000 points, and the second
place getter will get 800 points and third place getter will get 600 points.
all the way down to 72nd place. Just for example. After the 3 events are
finished for the year, the points get added together to get a total amount
of points, and the person with the most points is considered Number 1 ranked
player, like in the tennis.

To get the most recent results possible, total rankings points earned for
all 3 years are not kept. Only 100% of points earned for current year, 75%
of points earned for year before, and 50% of points earned for year before
that. All of these points are then added together to come up with the total
ranking points. For example, if I won an event in 2003, and received 1000
points, and did not finish any other events, my total ranking points for
that year would be 1000 points, but for 2003, only 50% of them would be
used. If I also earned 1000 in 2004, 75% of these would be used, and also
earned 1000 points in 2005, 100% of these would be used, therefore giving me
a total ranking points of 2250 (500 + 750 + 1000)

What I am looking for is to work out a database, that as these events take
place, I can add them into the database, with their most recent results, it
would then calculate the 3 years points, and give me 1 total figure, as the
example above, would give me a total of 2250

I know that you might not be able to help, and I may have just confused
things greatly, but any help would be appreciated.

Best Regards
Julie