If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Setting up Tables
I'm sure people make posts like this frequently, but here comes one more.
I'm creating a database, and I first started out trying to modify a template to suit my needs, but after spending umpteen hours on it, I've decided to start over from scratch so that I can set it up to be the most efficient. Having said that, I'm at a loss as to setting up my tables properly so that they will have the proper relationships. Here is what I need from my database, and maybe someone will be nice enough to explain what I should have in which tables, how the relationships should be set up, and then I can take it from there to create the forms, reports, and queries. The purpose of the database is to manage student data, but since it is for a military school, there are some other things that I need to track that civilian schools will not have. Student Data: Last Name First Name Middle Initial Rank DOB SSN Phone # e-mail address Home of Record (Address, City, State, ZIP) Room # Class # (there will be multiple students per class) Status Arrival Date Graduation Date Departure Date Follow-On Orders Notes Archive (a checkbox that is used to determine if the student is a current student or prior student) PFA Data (Physical Fitness Assessment): (These are conducted every two weeks and I need to track each students results of the assessment): Date Name Age Height Weight BCA Sit-Reach Sit-Ups Sit-Up Score Push-Ups Push-Up Score Run Time Run Score Composite Score Barracks Data (I'm using a separate table so that I can create a combo box on the forms that will show which rooms are available when I intake a student, and then using the Archive field, I can remove a student from a room and make the room available in the combo box): Room # Key # Student Assigned Any help in setting up the tables properly would be greatly appreciated. Thanks in advance. |
#2
|
|||
|
|||
Setting up Tables
Oh, I should also add that I would also like to track grades for the classes
as well (there are about 13 classes they take). Thanks again! |
#3
|
|||
|
|||
Setting up Tables
I would look at the PFA table. The biggest problem is that you are going
across, like a spreadsheet, instead of down, like a database table. Ask yourself this: what happens to your table, queries, forms, and reports if someone decides that pull-ups would be good for PT? They may all need changing. I recommend something like so: OH! Before we go there, don't use Date or Name for field names. I'd also get rid of the dashes - in the field names. Why? Those two words are reserved and can cause problems. I also recommend no special characters, including no spaces, in field or table names. The underscore _ is the only exception. Looking at your other fields, definitely dump the # in the names. Push-Ups could be PushUps or Push_Ups for example. Back on track: You should have a primary key field in Student_Data. The SSN could be a candidate as long as every one of your students have a SSN. If you are US military, that shouldn't be a problem but don't forget the Privacy Act of 1974! With the primary key in the Student_Data table, you don't need to repeat things like Name and Age in the PFA table. PFA_ID STU_ID PFA_Date Weight BCA 1 1 1/1/10 210 1 2 2 1/1/10 134 2 Activities Table PFA_ID Activity Result 1 Run 12:30 1 SitUp 23 1 PushUps 13 2 Run 11:20 2 SitUp 33 2 PushUps 23 Where are the scores? If you have a formula to compute them, you do that as needed based on the age, gender, activity, and result as needed in a query, form, or report. You can even compute the age from the DOB field. You shouldn't store such derived data. You'll need to join these tables togethere to get it to work. If this all sounds like gibberish, maybe I haven't explained it well enough. In that case I highly recommend getting some relational database training or reading "Database Design for Mere Mortals" by Hernandez before proceeding any further on this database. -- Jerry Whittle, Microsoft Access MVP (Retired E-8) Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Gntlhnds" wrote: I'm sure people make posts like this frequently, but here comes one more. I'm creating a database, and I first started out trying to modify a template to suit my needs, but after spending umpteen hours on it, I've decided to start over from scratch so that I can set it up to be the most efficient. Having said that, I'm at a loss as to setting up my tables properly so that they will have the proper relationships. Here is what I need from my database, and maybe someone will be nice enough to explain what I should have in which tables, how the relationships should be set up, and then I can take it from there to create the forms, reports, and queries. The purpose of the database is to manage student data, but since it is for a military school, there are some other things that I need to track that civilian schools will not have. Student Data: Last Name First Name Middle Initial Rank DOB SSN Phone # e-mail address Home of Record (Address, City, State, ZIP) Room # Class # (there will be multiple students per class) Status Arrival Date Graduation Date Departure Date Follow-On Orders Notes Archive (a checkbox that is used to determine if the student is a current student or prior student) PFA Data (Physical Fitness Assessment): (These are conducted every two weeks and I need to track each students results of the assessment): Date Name Age Height Weight BCA Sit-Reach Sit-Ups Sit-Up Score Push-Ups Push-Up Score Run Time Run Score Composite Score Barracks Data (I'm using a separate table so that I can create a combo box on the forms that will show which rooms are available when I intake a student, and then using the Archive field, I can remove a student from a room and make the room available in the combo box): Room # Key # Student Assigned Any help in setting up the tables properly would be greatly appreciated. Thanks in advance. |
#4
|
|||
|
|||
Setting up Tables
Thank you for the speedy reply. For the time being I think I'm going to
continue to use my old database from the template and continue to modify it, so changing the PFA table to match the accepted conventions may not happen just yet, but it is great info for when I create the table to keep track of the student's grades. I'll make sure I use the set-up you mentioned. I wish the templates Microsoft supplies would use the accepted conventions for naming, relationships, and normalizing. From now on I'm going to start from scratch whenever I need to make a new database. "Jerry Whittle" wrote: I would look at the PFA table. The biggest problem is that you are going across, like a spreadsheet, instead of down, like a database table. Ask yourself this: what happens to your table, queries, forms, and reports if someone decides that pull-ups would be good for PT? They may all need changing. I recommend something like so: OH! Before we go there, don't use Date or Name for field names. I'd also get rid of the dashes - in the field names. Why? Those two words are reserved and can cause problems. I also recommend no special characters, including no spaces, in field or table names. The underscore _ is the only exception. Looking at your other fields, definitely dump the # in the names. Push-Ups could be PushUps or Push_Ups for example. Back on track: You should have a primary key field in Student_Data. The SSN could be a candidate as long as every one of your students have a SSN. If you are US military, that shouldn't be a problem but don't forget the Privacy Act of 1974! With the primary key in the Student_Data table, you don't need to repeat things like Name and Age in the PFA table. PFA_ID STU_ID PFA_Date Weight BCA 1 1 1/1/10 210 1 2 2 1/1/10 134 2 Activities Table PFA_ID Activity Result 1 Run 12:30 1 SitUp 23 1 PushUps 13 2 Run 11:20 2 SitUp 33 2 PushUps 23 Where are the scores? If you have a formula to compute them, you do that as needed based on the age, gender, activity, and result as needed in a query, form, or report. You can even compute the age from the DOB field. You shouldn't store such derived data. You'll need to join these tables togethere to get it to work. If this all sounds like gibberish, maybe I haven't explained it well enough. In that case I highly recommend getting some relational database training or reading "Database Design for Mere Mortals" by Hernandez before proceeding any further on this database. -- Jerry Whittle, Microsoft Access MVP (Retired E-8) Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Gntlhnds" wrote: I'm sure people make posts like this frequently, but here comes one more. I'm creating a database, and I first started out trying to modify a template to suit my needs, but after spending umpteen hours on it, I've decided to start over from scratch so that I can set it up to be the most efficient. Having said that, I'm at a loss as to setting up my tables properly so that they will have the proper relationships. Here is what I need from my database, and maybe someone will be nice enough to explain what I should have in which tables, how the relationships should be set up, and then I can take it from there to create the forms, reports, and queries. The purpose of the database is to manage student data, but since it is for a military school, there are some other things that I need to track that civilian schools will not have. Student Data: Last Name First Name Middle Initial Rank DOB SSN Phone # e-mail address Home of Record (Address, City, State, ZIP) Room # Class # (there will be multiple students per class) Status Arrival Date Graduation Date Departure Date Follow-On Orders Notes Archive (a checkbox that is used to determine if the student is a current student or prior student) PFA Data (Physical Fitness Assessment): (These are conducted every two weeks and I need to track each students results of the assessment): Date Name Age Height Weight BCA Sit-Reach Sit-Ups Sit-Up Score Push-Ups Push-Up Score Run Time Run Score Composite Score Barracks Data (I'm using a separate table so that I can create a combo box on the forms that will show which rooms are available when I intake a student, and then using the Archive field, I can remove a student from a room and make the room available in the combo box): Room # Key # Student Assigned Any help in setting up the tables properly would be greatly appreciated. Thanks in advance. |
Thread Tools | |
Display Modes | |
|
|