I am fairly new to access so please bare with me!
I have a database of individuals where I want to score them and rank 1 through 100 based on their total score.
I have a test database where I worked on the calculations and reports, they work fine there, BUT I am getting turned around with relationships and "Master" data in the true database.
1) I have a "Master People" table containing all the individuals with a lot of data on it, ID#'s first, last, middle, gender, dob etc. This list was imported from an Excel SS that came from a web-based application used to register all of the people.
2) I have "weekly event data" in the form of 3 Excel spreadsheets from 3 disparate sources (that cannot format the data the same) that is composed of: First, Last, and detailed info for the scoring that I import into a table for each data source. I then use an append query to update a "Master Event" table that has the combined detail in the desired order and displays the calculated score per event.
Each of the people can have multiple events on the same day from across various age groups and positions (see below) so in theory there can be many entries per person (and will have potentially hundreds/thousands). I was thinking of creating an autonumber ID field but as each person can have multiple events I don't think that will solve my issue.
The "Master Event" table contains only name info, not an "ID" so I cannot for the life of me think how to link them and define the relationship between the "Master Event" table and the "Master People" table.
The final reports should have detail from the "Master People" file but also include the cumulative score, as well as the individual events detail and score. The reports that I need right now are:
a) Top 100 People by Total Score
b) Summary by Person including the "Events"
c) Score by Gender
The reports will be used to assign people to events, ie higher scored people should be placed at more demanding events, and some events are gender specific and should people should be assigned to that event based on that.
For example:
The "Master People" table has:
ID
Last
First
DOB
Gender
etc, etc
The "Master Events" table has
Last
First
Event
AgeGroup
Position
EventDate
EventTime
Score
Event, AgeGroup, and Position are individual tables with the scoring detail by each category used to calculate via the query the score per event.
I sincerely appreciate your thoughts and help!
Mike