RE: [MS_AccessPros] Normalization Assistance Needed

Jerry,

I agree with Bill but would go a little farther with normalization and remove "Errors, Expression, Rate, etc." as field names. I would capture these values in a field named [ScoreItemID]. This would result in a table where each item would have its own score. If a test consisted of Errors, Expression, Rate, comprehension, accuracy, and interpretation this would result in 6 records in the related table.

If/when you add more score items, you should only have to add records to a table, not add fields and controls.

Duane Hookom MVP
MS Access

> From: wrmosca@comcast.net
> Date: Wed, 26 Dec 2012 12:46:18 -0800
> Subject: RE: [MS_AccessPros] Normalization Assistance Needed
>
> Beaver
>
> You're off to a good start, but there are some things you should change.
>
> 1. The level should be an attribute and not separate tables. What you need is a TestResult table with the Student ID, Book ID, Test ID and scores. See how that gives you relationships? Those first 3 are foreign keys relating to the various tables.
>
> 2. Session is related to the date so I would not include it in the table. If a date is changed resulting in a different session you cannot insure the session will also be updated. This should be a calculation shown in forms and reports based on a Session table with an AutoNumber primary key, a year, start date, end date, and session name.
>
>
>
> Say hi to Lumpy for me.
>
>
>
> Regards,
> Bill Mosca,
>
> My Nothing-to-do-with Access blog
>
> <http://wrmosca.wordpress.com> http://wrmosca.wordpress.com
>
>
>
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jerry Mathers
> Sent: Wednesday, December 26, 2012 11:00 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Normalization Assistance Needed
>
>
>
>
>
> This is probably a simple question, but.... I'm trying to put together a Reading Level database to be
> used to record reading information for elementary school students. There are a number of items students are
> scored on for their reading level including comprehension, accuracy, interpretation,
> etc. The database needs to keep track of
> two levels of reading I'm calling UpperLevel and Lowerlevel. These levels are determined by the book the
> teacher uses to test the student's reading ability, so it is the book that decides the level, not the student or the grade the student is in. Right now I have five tables to hold the
> data: StudentInfo, TestingDates,
> BookInfo, UpperLevel, LowerLevel.
>
> StudentInfo has fields like: StudentID {primary key}, LastName, FirstName,
> Grade, Teacher, etc.
>
> TestingDates includes: TestID {primary key}, TestDate, Session {meaning fall, winter, spring}
>
> BookInfo contains fields about the books used,
> including: TextID {primary key},
> TextTitle, TextLevel, TextWordCount, etc.
>
> LowerLevel contains fields about the student's scores
> based on the selected book used, including: LowerID {primary key}, Errors, Expression, Rate, etc.
>
> UpperLevel is basically the same as LowerLevel with a few
> differences on what is measured. The
> structure is the same with a primary key of UpperID
>
> Assuming these five tables represent a logical approach
> to storing the data, I cannot figure out how they relate to each other so that
> I can determine what all of the testing data was for a particular student. Or, so I can see all of the data for a
> particular teacher's class in order to help them make up student reading groups
> based on this data.
>
> Thanks for any help you can offer.
>
> Jerry

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/