hi
Jim,
Welcome! I read your introduction post. Since you plan to track information in schools for the whole state, it might be good to start with a well-designed contact management system that you can enhance as needed. I have 2 that you can download.
This is designed for others to build around and includes lots of extra tables that you won't need -- they serve as examples for expanding the contacts, depending on what business you're in.
Contact Template for Access
http://msaccessgurus.com/Contacts.htm
videos:
Introduction to the Contact Template (30:43 minutes)
Projects, Tasks, and Time (3:30 minutes)
This one is simpler and designed for personal contact management so the database isn't split into front-end and back-end. It is easier to understand since it doesn't do as much, but is still fairly comprehensive. It also newer, so I like my fieldnames better.
My Contacts
http://msaccessgurus.com/tool/MyContacts.htm
I didn't make a video for MyContacts, but wrote a few articles about some of its features:
Managing Contacts
https://sfmagazine.com/post-entry/november-2018-access-managing-contacts/
Cross-referencing Contacts and Lists
https://sfmagazine.com/post-entry/december-2018-access-cross-reference-contacts-and-lists/
Pop-up Related Forms
https://sfmagazine.com/post-entry/january-2019-access-pop-up-forms/
Both downloads are free and the source code is open, so you can change it how you need it to be.
-----------
Your questions:
1. First question, should I separate first and last names in the AD table?
I would suggest putting all contacts in one table. Names are separated. Then create an AD table that links to a contact in the Contacts table for the contact information. In the AD table, you would add additional fields that are specific to the role as an athletic director.
2. Second question, My next step will be to create one or more tables for the Coaches and team sports. Would it be best to create a table for each sport and list the coach and school, or create one large table listing the coach, school, and all the team sports and have a check box for each sport the coach is involved in? Some coaches will be included in more than one sport, and sometimes with more than one school.
Again, I would put coaches in the Contacts table and create another table for Coach information such as where their office is and what their hours are. Since a Coach may coach more than one sport, I would make a cross-reference table between Coaches and Sports with a RoleID foreign key linking to a Roles table so you can designate the coach as a head coach, assistant coach, substitute coach.
Perhaps you want to combine Athletic Directors and Coaches into one table since Athletic Directors may also be coaches, and you can designate their role with each sport. you might also want to put a RoleID in that table for their primary function. You could also put other support functions in here like ball-boy and water-carrier
While its a bit more than you need, I have a table with Sports in my free CalendarMaker tool that you can bring into your database and use. It has many more records than you'll want! I actually spent a couple days putting all the sports in that I could think of and find information about. My CalendarMaker tool also has a very crude feature for scheduling sporting events. The main thing this download does, though, is make calendars!
CalendarMaker
https://msaccessgurus.com/tool/CalendarMaker.htm
I did a presentation on the CalendarMaker in case you want to learn more about what it does here:
CalendarMaker presentation to Access User Groups
This might seem like a lot of information, and it is! The more work you put into design, as in building cabinets, the better your final database will be.