Print Page | Close Window

FlatFileDemo.mdb (beginner)

Printed From: Roger's Access Library
Category: Roger's Access Library
Forum Name: Roger's Download Samples
Forum Description: A compendium of Microsoft Access 97 and 2000+ routines, each separated into its own sample database.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=347
Printed Date: 21 Nov 2019 at 8:07am


Topic: FlatFileDemo.mdb (beginner)
Posted By: Roger Carlson
Subject: FlatFileDemo.mdb (beginner)
Date Posted: 01 Aug 2008 at 4:27am

Please consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm - find out why here .
 

FlatFileDemo.mdb

This example illustrates some of the difficulties inherent in bad (non-normalized) database design and how they can be corrected. It is not a formal tutorial on Normalization, rather it is a series of examples which allow you to demonstrate the kind of trouble that a non-normalized can produce. I use this example in my classes as an introduction to why you would want to normalize a database design.

I use it like this:

1) Open table: NonNormalized. This has repeated values. How do you create a query/report which shows each employee's employment history and the average salary? You have to interrogate each set of job history fields (these are the repeated values). Very difficult. (See Report: NonNormalized)

Solution: Put each employment history in its own RECORD, but you have to repeat the common employee information in order to do that.

2) Open table: FlatFile_1NF. Now you can create a query/report (FlatFile_1NF) to easily see the employment history, but you now have data integrity problems. I add a new record. I have to type all the employee information over again. I spell the employee name wrong. I type the SS# (primary key) wrong. My report is wrong. I show the data discrepancies.

Solution: Split the table into two tables: Employee and Salary History. Show the ease of reporting.

3) Show the two tables and explain that the information is grouped logically and linked based on the SS#.

4) Go to the query "FullTable" which joins the two tables so that it looks just like FlatFile_1NF. Type in an existing SS# into the NewRecord. All the employee information automatically appears. I can't mis-spell or input incorrect employee information.
 
Download
Access 97 (48 KB):
http://www.rogersaccesslibrary.com/forum/uploads/5/FlatFileDemo97.zip - uploads/5/FlatFileDemo97.zip
Access 2000 (60 KB): http://www.rogersaccesslibrary.com/forum/uploads/5/FlatFileDemo2k.zip - uploads/5/FlatFileDemo2k.zip



-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Print Page | Close Window