- Local time
- Today, 03:25
- Joined
- Feb 19, 2002
- Messages
- 43,275
I made a lot of nit-picky changes to the schema but you should also check each table because I may have missed stuff.
1. I removed 0 as the default for FK's. 0 is never valid so the default should be null
2. I changed the setting of the AllowZLS from yes to no for all the fields involved in the little lookup tables. I did not fix the teachers table or the other fields in the student table. In general, I ALWAYS change the AllowZLS property to No because I prefer that fields that are empty be null. That way I can always test for null and not have to worry about what data type a field is. Remember, dates and numbers are NOT STRINGS and so the AllowZLS doesn't apply to them since they can never contain a ZLS. It also prevents the problem with fields like LastName. If you have AllowZLS set to yes, the user can enter a blank name and even if the field is defined as required, Access will allow it which makes no sense. So, always change this property to No just on GP (general principles)
3. I removed all the extraneous indexes on the FK fields. When you create a relationship using the relationship window, Access automagically creates a HIDDEN index on the FK fields so you do not need to and should not create a second one.
4. The relationship between TermStreams and Students was backwards so I removed the StudentID from TermStreams and added the FK to TermStreams into Students
5. I renamed the Students table for consistency. Consistency is your friend. It will save you lots of hair pulling and head banging.
6, I added unique indexes to the text fields on the lookup tables. I don't like the way this is broken down but since it is not technically wrong, I didn't change it. I just fixed it to work correctly. I also made these fields required. So on tables like Years, Terms, Grades, etc. there is a unique index on the "text" field AND the "text" field is marked as required. It makes absolutely NO SENSE to have a row in one of these tables if it doesn't have a "text" field value. It also makes no sense for the "text" field in any of these tables to be duplicated so I added the unique index.
7. I added a new column to the Years table and named it Prior. If you will look at the frmStudents, you will understand why. I added a combo box so that the TermStreamID could be selected. Look at the RowSource query and you will see WHY I dislike this structure. But in any event, I made a concatenated name to make it easy for someone to pick the right TermStream. The query sorts the prior years to the bottom of the list but leaves them there and out of the way. I also added code to the Form's BeforeUpdate event to prevent a user from picking a TermStream for a prior year.
8. I also cleaned up the related tables of Term Streams. GradeStream existed but wasn't being used correctly so I used it correctly. I don't like it but again, it isn't wrong to have this as an intermediary table.
1, 2, 3, 5, 6 are things you need to learn to do by rote. You NEVER want to deviate. I have code I can run to clean up Access' default mess after I create a table. It is in one of my examples so I'll include a link to it. But the code cannot clean up your sloppy choices regarding allowing duplicates and not using indexes correctly. That takes discipline and some time to make it second nature so you don't even think about it.
Things I didn't fix
1. The frmYears and it's subforms. There was no point in doing that if I didn't fix the schema correctly.
2. I didn't fix all the poorly formed names. Names should contain ONLY letters, numbers, and the underscore. No embedded spaces, no special characters and also NO VBA function or property names. The link above includes a form that shows some of what bad things happen when you include non-standard characters in object names. Access INMNSHO, is way too lax regarding table, column, and object names. It is this laxness that causes you more work later. Access does it to be "friendly" to new users but in reality, this "friendliness" just causes problems down the line when you start writing code and queries.
Things I recommend that you change.
1. The attachment data type is one of those abomination fields that can't be upsized. I assume you are using them for photos of students and teachers. Unless you are planning on storing multiple images, I would just get rid of the attachment data type and save yourself some later agnst. Just use the OLE object type instead to hold ONE image. Better still, use a text field and store a link to the image. That will save a lot of database bloat that will start to affect the database after a few years of use. If you are storing multiple indexes, just make a child table and put the images in that so you can support multiples. Change your form to use a subform to show the child table with the OLE objects rather than using the attachment control. You get exactly the same functionality without having to learn a new variant of SQL or having a problem should you ever want to upsize the BE to sQL Server. And it is no more work if you do it from the start.
1. I removed 0 as the default for FK's. 0 is never valid so the default should be null
2. I changed the setting of the AllowZLS from yes to no for all the fields involved in the little lookup tables. I did not fix the teachers table or the other fields in the student table. In general, I ALWAYS change the AllowZLS property to No because I prefer that fields that are empty be null. That way I can always test for null and not have to worry about what data type a field is. Remember, dates and numbers are NOT STRINGS and so the AllowZLS doesn't apply to them since they can never contain a ZLS. It also prevents the problem with fields like LastName. If you have AllowZLS set to yes, the user can enter a blank name and even if the field is defined as required, Access will allow it which makes no sense. So, always change this property to No just on GP (general principles)
3. I removed all the extraneous indexes on the FK fields. When you create a relationship using the relationship window, Access automagically creates a HIDDEN index on the FK fields so you do not need to and should not create a second one.
4. The relationship between TermStreams and Students was backwards so I removed the StudentID from TermStreams and added the FK to TermStreams into Students
5. I renamed the Students table for consistency. Consistency is your friend. It will save you lots of hair pulling and head banging.
6, I added unique indexes to the text fields on the lookup tables. I don't like the way this is broken down but since it is not technically wrong, I didn't change it. I just fixed it to work correctly. I also made these fields required. So on tables like Years, Terms, Grades, etc. there is a unique index on the "text" field AND the "text" field is marked as required. It makes absolutely NO SENSE to have a row in one of these tables if it doesn't have a "text" field value. It also makes no sense for the "text" field in any of these tables to be duplicated so I added the unique index.
7. I added a new column to the Years table and named it Prior. If you will look at the frmStudents, you will understand why. I added a combo box so that the TermStreamID could be selected. Look at the RowSource query and you will see WHY I dislike this structure. But in any event, I made a concatenated name to make it easy for someone to pick the right TermStream. The query sorts the prior years to the bottom of the list but leaves them there and out of the way. I also added code to the Form's BeforeUpdate event to prevent a user from picking a TermStream for a prior year.
8. I also cleaned up the related tables of Term Streams. GradeStream existed but wasn't being used correctly so I used it correctly. I don't like it but again, it isn't wrong to have this as an intermediary table.
1, 2, 3, 5, 6 are things you need to learn to do by rote. You NEVER want to deviate. I have code I can run to clean up Access' default mess after I create a table. It is in one of my examples so I'll include a link to it. But the code cannot clean up your sloppy choices regarding allowing duplicates and not using indexes correctly. That takes discipline and some time to make it second nature so you don't even think about it.
Useful code AND the effects of using bad column names
This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...
www.access-programmers.co.uk
Things I didn't fix
1. The frmYears and it's subforms. There was no point in doing that if I didn't fix the schema correctly.
2. I didn't fix all the poorly formed names. Names should contain ONLY letters, numbers, and the underscore. No embedded spaces, no special characters and also NO VBA function or property names. The link above includes a form that shows some of what bad things happen when you include non-standard characters in object names. Access INMNSHO, is way too lax regarding table, column, and object names. It is this laxness that causes you more work later. Access does it to be "friendly" to new users but in reality, this "friendliness" just causes problems down the line when you start writing code and queries.
Things I recommend that you change.
1. The attachment data type is one of those abomination fields that can't be upsized. I assume you are using them for photos of students and teachers. Unless you are planning on storing multiple images, I would just get rid of the attachment data type and save yourself some later agnst. Just use the OLE object type instead to hold ONE image. Better still, use a text field and store a link to the image. That will save a lot of database bloat that will start to affect the database after a few years of use. If you are storing multiple indexes, just make a child table and put the images in that so you can support multiples. Change your form to use a subform to show the child table with the OLE objects rather than using the attachment control. You get exactly the same functionality without having to learn a new variant of SQL or having a problem should you ever want to upsize the BE to sQL Server. And it is no more work if you do it from the start.
Last edited:
