Help Getting Started (1 Viewer)

jms3257

Member
Local time
Today, 04:02
Joined
Apr 18, 2020
Messages
33
Just starting to create a database, will include information about High School Sports. So far I have created a table with the school information, such as address, phone, fax, link to google map, website ect. Second table is for Athletic Directors. Including School, Name, E-mail, Cell Phone.

First question, should I separate first and last names in the AD table?

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.

If there is more information needed I will supply, trying to keep an already long post from getting longer.

Thank You
Jim Smith
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:02
Joined
Aug 30, 2003
Messages
36,118
Yes, separate name fields. It's easier to put them together than break them apart.

I'm thinking a single table, with fields for coach/school/sport. I wouldn't have fields/checkboxes for sports, I'd have multiple records if a coach is involved in multiple sports.
 

strive4peace

AWF VIP
Local time
Today, 05:02
Joined
Apr 3, 2020
Messages
1,003
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.
 
Last edited:

jms3257

Member
Local time
Today, 04:02
Joined
Apr 18, 2020
Messages
33
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.

Thank you so much for your reply. I have been looking it over for a while, and as you said there is a lot of information. If I understand correctly All AD's as well as Coaches should be in a Contact's Table. I could expand my current AD Table include Coaches and re-name it. However I will need to study up on the "RoleID" and "cross-reference table" not real sure what to do with those.

Been looking over your website, and will be looking at things you have there. Time for more studying. I am looking to keep this project rolling but don't want to get ahead of myself and put in a lot of time adding tables that I will need to redo latter because I got started on the wrong foot.

Jim Smith
 

zeroaccess

Active member
Local time
Today, 05:02
Joined
Jan 30, 2020
Messages
671
Just wanted to say that 8:00 AM - 9:15 PM is a long day in Access!
 

jms3257

Member
Local time
Today, 04:02
Joined
Apr 18, 2020
Messages
33
hi Jim,

you're welcome. Not to innundate you even more, but perhaps you want to start here:

Access Basics
http://www.accessmvp.com/strive4peace
Free 100-page book that covers essentials in Access

Learn VBA
http://www.accessmvp.com/strive4peace/VBA.htm


I have found both of these on your site, haven't started reading yet. Am working on another problem. This happened while I was still working, and would be deep inside the Pattern Systems database. I am not able to turn off my thoughts and am not able to get a really good nights sleep.

It does however bring up other things I should maybe change before it becomes a problem. My first table was (tblSchools) has the necessary information. When I created the (tblAthleticDirector) table I wanted to add a column for the school, I created another table (tblSchoolList) to use as a drop down. However If I ever need to add another school, I will need to add to both tables. Should I have created the drop down list from the original (tblSchools) or would that cause a problem latter down the road?
 

strive4peace

AWF VIP
Local time
Today, 05:02
Joined
Apr 3, 2020
Messages
1,003
I have found both of these on your site, haven't started reading yet. Am working on another problem. This happened while I was still working, and would be deep inside the Pattern Systems database. I am not able to turn off my thoughts and am not able to get a really good nights sleep.

It does however bring up other things I should maybe change before it becomes a problem. My first table was (tblSchools) has the necessary information. When I created the (tblAthleticDirector) table I wanted to add a column for the school, I created another table (tblSchoolList) to use as a drop down. However If I ever need to add another school, I will need to add to both tables. Should I have created the drop down list from the original (tblSchools) or would that cause a problem latter down the road?

hi Jim,

I have trouble going to sleep too! I usually play educational videos and listen, but if I still don't go to sleep, then I often play something on YouTube:

https://www.youtube.com/results?search_query=music+sleep

It isn't a good idea to put drop-down lists in the table design. You can do it, but they create issues. The place for those lists is on forms.

The Evils of Lookup Fields in Tables
http://theaccessweb.com/lookupfields.htm

I would put Schools in the Contacts table. Then in the Schools table (with CID to link to the contact information for the school), I would also create CID_AD (long integer, default value is null) to link to who the athletic director is.
 

jms3257

Member
Local time
Today, 04:02
Joined
Apr 18, 2020
Messages
33
Some how I had a feeling that is what the answer was going to be. is there a good way to remove the drop down from the table? or will I need to re-enter the information? if I simply change the properties from lookup wizard to short text or would that just empty the contents?
 

strive4peace

AWF VIP
Local time
Today, 05:02
Joined
Apr 3, 2020
Messages
1,003
Some how I had a feeling that is what the answer was going to be. is there a good way to remove the drop down from the table? or will I need to re-enter the information? if I simply change the properties from lookup wizard to short text or would that just empty the contents?

hi Jim,

easy to remove!

  1. Go to the table design
  2. Select a field with a lookup
  3. In the lower pane, click the Lookup tab
  4. Change Display Control to Text Box
Your field values won't change ... and then when you look at the Datasheet, you'll see the actual values!
 

zeroaccess

Active member
Local time
Today, 05:02
Joined
Jan 30, 2020
Messages
671
Some how I had a feeling that is what the answer was going to be. is there a good way to remove the drop down from the table? or will I need to re-enter the information? if I simply change the properties from lookup wizard to short text or would that just empty the contents?
As long as you know what is going on, I do not think it's a big deal.

All it is is a table-level lookup. So as an admin, I can change a value if needed and choose from real names instead of having to refer to a lookup table to find the right ID.
 

strive4peace

AWF VIP
Local time
Today, 05:02
Joined
Apr 3, 2020
Messages
1,003
With all due respect, Zero, it is good to adhere to best practices. Data should be entered and edited with forms, not tables. When you define a field to be a lookup, not only does it hide real values in the datasheet view, but it can also mess with queries and SQL. Just because you can do something doesn't mean you should ...

When developing, sometimes I do define lookups in tables, for ease of entering test data. Before deploying, however, I change these to normal fields -- don't want users to encounter odd problems because of this ... but then I also train users to create their own queries and reports. Never-the-less, it is good to remove lookups in field definitions in tables for other reasons ... performance for one.
 

zeroaccess

Active member
Local time
Today, 05:02
Joined
Jan 30, 2020
Messages
671
In most/all cases, yes. Users would use forms. But I'm talking from an admin's perspective.

Users won't encounter any issues while using the DB if you designed it properly (as you would, anyway). They won't have access to the tables.

But it sure makes it easier for an admin to troubleshoot issues from time to time. All it is is a lookup - so I see the real values/names instead of IDs.
 

strive4peace

AWF VIP
Local time
Today, 05:02
Joined
Apr 3, 2020
Messages
1,003
sorry to disagree ... still ... lookups in table design affect more than just hiding the value. Admins can use queries, that's what I do.
 

strive4peace

AWF VIP
Local time
Today, 05:02
Joined
Apr 3, 2020
Messages
1,003
ps, zeroaccess, just in case you don't already know, which you might ... if you preface an object name with "usys" (user system), it will be hidden along with other system objects unless you set Navigation Pane options to show it
 

zeroaccess

Active member
Local time
Today, 05:02
Joined
Jan 30, 2020
Messages
671
sorry to disagree ... still ... lookups in table design affect more than just hiding the value. Admins can use queries, that's what I do.
You would use a query to reopen a case for someone on request? Seems a bit cumbersome...that is one use-case.
 
Last edited:

zeroaccess

Active member
Local time
Today, 05:02
Joined
Jan 30, 2020
Messages
671
ps, zeroaccess, just in case you don't already know, which you might ... if you preface an object name with "usys" (user system), it will be hidden along with other system objects unless you set Navigation Pane options to show it
I didn't, but I usually just hide the navigation pane, ribbon, and everything so nothing is visible at all. No need to hide objects individually in my case :). I don't have users making queries though, at this point.
 
Last edited:

jms3257

Member
Local time
Today, 04:02
Joined
Apr 18, 2020
Messages
33
Reply to both Crystal and zeroaccess. At least for now I will be the only one using this, may change but not likely. I find it easier to add data in the table, at least for now. However I am trying to develop sound procedures from the start. I find it better especially when this project starts getting larger.
 

zeroaccess

Active member
Local time
Today, 05:02
Joined
Jan 30, 2020
Messages
671
Reply to both Crystal and zeroaccess. At least for now I will be the only one using this, may change but not likely. I find it easier to add data in the table, at least for now. However I am trying to develop sound procedures from the start. I find it better especially when this project starts getting larger.
Definitely get your forms developed so you can get a good workflow going with them. Design and use the forms from the perspective of someone else using them. Access isn't much better than Excel if just using tables. Good luck!
 

Users who are viewing this thread

Top Bottom