Web Development



MS Access help - Doing charity work

By bandwannabe ·
I am working on a database for the Boy Scouts and need help getting
my rankings set up. I do a little database at work, but not nearly
enough to do this efficiently.

I am trying to set up 4 main tables to track scores and rank scouts in
given events.

Table 1 - Troop - holds all data about each troop that is
participating. Each troop number is unique.
Table 2 - Patrol - holds data about each patrol - link to troop table.
Patrol names are not unique, but patrol/troop combinations are.
Table 3 - Event List - List of 11 events. 7 patrol based events and 4
troop based events.
Table 4 - Scorecard - Holds scores, both time and number
(depending on event) and some have high scores are good and
others want low.

I need to be able to record each patrol's scores for the given events
and rank them with divisions for 3 experience levels (defined on the
patrol record). I've gotten a query written (after about 4 hours of
trying) that will rank the patrols by event (each event has a separate
query), but the query cannot handle the experience considerations as
well. (ranking query is as follows: "SELECT PatrolName,
EventScoreNumber, EventName, (SELECT Count(*) FROM
PatrolScorecard AS PScore WHERE EventName=1 AND
PScore.EventScoreNumber > PatrolScoreCard.EventScoreNumber) +1
AS Rank
FROM PatrolScorecard
WHERE EventName=1;")

I think this would be simpler if I could get the troop data and
experience data referenced in the scorecard table, but can't get the
system to populate it. I don't want to make the user enter this data
repeatedly (even if it is linked together).

Can someone, PLEASE, help me with my setup so I can get this done.

I appreciate any help you can offer me. This is beginning to fry my
amateur brain.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Related Discussions

Related Forums