I'm creating a database for our sports tournaments where I'll probably have 3 tables. We have about 7 different tournaments, I call "Events", and each event can have many players and each player can play in many events. The problem I run into is the following:
Since before I worked at my company, they basically used Filemaker as a glorified spreadsheet so they could print out mailing labels and create customized letters. We begin our "recruiting" process by adding a large list (over 200 players) to the database. They're basic information is entered into a number of fields, but for the most part all we have is their first, and last name, their school/team they play for, their school year, their position, and an address to mail them information to. Now, at this point, my company would create ONE database for EVERY event so they didn't mix up the players! From all the research and learning I've done about Filemaker, this method is primitive at best. After we mail out information about our events to these players, only a percentage send in the application. Once we receive their application, we do a find of the last name to see if we did in fact mail to them, and then update their information accordingly.
What I would like to do is compile ALL of the event databases into one as to make reporting and summaries much, much easier. I would like the database to be able to keep records of ALL the kids we mail information to, as well as the kid who have sent in applications so I can make reports showing how many kids we have for Event A, B, etc...
So far, I have 2 tables set up, one for Events and one for Players. In the Events table, my primary key will be the Event ID that stores information about each different event (cost, dates, directions, etc.). In the Players table, the Primary key will be Player ID, since each player is a unique record and we don't want to produce duplicates so I can avoid sending information to the same kid more than once. How do I go about connecting these tables so I can accomplish the reports I mentioned? I have an idea that I'll have to make a sort-of Line-items type of table that connects the 2, but I'm not too sure what/how to do.
If anyone can offer any advice, I'd be extremely grateful! I've been working on this for months and I'm literally stuck in this quicksand of an issue. I'm trying to have it resolved by the end of March and I have no idea what to do.
Thank you in advance!!