Newb Relationship help needed

[mpreston14]

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

[rpeare]

The master events table should have a FK (foreign key) to your MASTER PEOPLE table instead of storing the first and last name you want to store the ID of the MASTER PEOPLE table and give your MASTER EVENTS it's own primary key as well. Second. Your age group can be calculated based on the date of birth and the event date, you do not need to store or data enter the age group. If you are dealing with a database that is already written and you are trying to find a way to link the two you'll likely have to do some data conversion to make the link work before it can be useful for data entry. If you can't or don't have the control of the database to make that relationship change you are going to have trouble forever with your database. For example

Let's say you have two John Smith people. let's also say (for the sake of argument) that they are in the same age group. One was born on 1/1/1990 the other born on 1/2/1990. You have no way to tell which is which in your master events table.

Further, let's say someone mistyped John Smith to be Jon Smith but you have no Jon Smith in your MASTER PEOPLE table. You'd have no way of determining who it was based on the data. The whole key to a database is the RELATIONSHIPS between tables, right now yours have none and you have to enforce that before you can start querying or doing reporting with any degree of accuracy.

[orange]

I think you should review the Reserved Words list from Access. Your "tables" have some reserved words.
Here's a list http://allenbrowne.com/AppIssueBadWord.html
You might want to lookup some naming conventions and some access usage suggestions.
See Crystal's Material

[mpreston14]

Thank you for the fast reply. The AgeGroup table is the AgeGroup at the event the person worked, lets say "13-15" not the age group the person belongs to. I am sorry I should of clarified that. But I understand what you are saying there.

So, If there is no commonality other than a persons name between the Master People table and the Master Event table am I doomed? Lets say I place an autonumber ID field in the Events table; what relationship map do I have? There really isn't a way to link that to a person is there? (sorry thinking as I type) other than manually entering the data.

Should I just populate a "New Master People" table from the Master Events Table by assigning an auto number ID and then manually populate the extra data I wanted in the "original master people" table into the new one? I suppose I could eventually build something that looks at the name in the new table, and only enter the original Master People Table detail (Registration #, etc) if it is a singular match?

[mpreston14]

Those were just examples to simplify my question. Thank you for the suggested reading though, I will definitely read it!

[orange]

There is a tutorial that could help you here

There are other examples under the EXAMPLES section on this page

[rpeare]

If I were you and you have existing data and you're trying to create a relationship between the two I'd add an autonumber field to your MASTER EVENTS table (I think i access 2007+ you can create an autonumber after there's data in the table which is a help) and add a MASTER_ID field as well (number field assuming ID in the MASTER PEOPLE table is an autonumber).

Then physically go through your records and identify which MASTER PEOPLE record belongs to which MASTER EVENT and physically type in the FK to the MASTER PEOPLE table. I don't know how much information you have but doing it automatically may be a danger because you're linking on two fields that are not going necessarily going to give you an accurate match.

After you update the MASTER PEOPLE table with the PK and FK anything else you want to do (that you mentioned in your first post) will be simple.

[mpreston14]

I think I understand:

If I add a field called PeopleID to the Master Events table, then add People ID to the Master People Table, so long as I link them (manual effort) I should be good. For example:


MASTER EVENT TABLE:
PeopleID 1
LastName Smith
FirstName John
Event XYZ
AgeGroup 13-15
Position ABC

MASTER PEOPLE TABLE
PeopleID 1
RegistrationNO 7632434923430
LastName Smith
FirstName John
Gender M
DOB 01/01/1990
etc,etc'

Before my manual entry, I then, in the Relationship tool create a relationship between the MASTER EVENTS PeopleID and the MASTER PEOPLE PeopleID and everything will be "ok"?

How would I best handle if a new "person" is imported into the MASTER EVENTS table and is assigned a PeopleID but doesn't exist in the MASTER PEOPLE Table?
Is there a way to check against the name fields and then create a report of possible "New People"?

Sorry ADHD meds have worn off...

[rpeare]

your master event table should also have an EVENTID (autonumber) so that you can identify a record accurately.

If you are importing data continually from an outside source (text file or excel file, etc) you're going to have to perform an import then go through your 'new' data to make sure you correctly link it to your master people table. If you have people registering through a website what is the back end for the data on the website? if it's a SQL table you can create an identity field on the SQL tables for each of the two tables and then you don't have to do any of this in access.

[mpreston14]

I dont have access to their systems, we are providing a service to these 3 organization by using our people., The organizations that send me the data for our people have control of what they send and are generally not willing to edit output from their systems...it drives me nuts but I cant change it....hoping to massage what I can get out of them for our internal use.

thanks for your help!

[mpreston14]

Thanks everyone, its working great now! I have a few other issues to work out but the design is now solid. Reading all of that info really helped me to visualize how the database should be set up. I still fight with wanting a view like I would have in excel but I think it will just take time to get used to it. SOLVED!