Data population help - Charity work

[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.

 

[John W. Vinson]

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).

Are you perhaps trying to enter the data directly in Tables? If so... DON'T.

It is certainly *NOT* necessary nor appropriate to store the troop information
redundantly in the scorecard table!

I'd strongly suggest using a Form with Subforms and combo boxes as a data
entry and editing tool. You could have, for example, a Form based on the Troop
table, with a single Subform based on the Patrol table, with a second subform
(on the Patrol subform) based on the scorecard table. This will let you see
troop and patrol information onscreen while entering score data for that troop
and that patrol.

John W. Vinson [MVP]