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.
Printed Date: 10 Jul 2020 at 8:46pm
Software Version: Web Wiz Forums 12.03 -

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, - find out why here .


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.
Access 97 (48 KB): - uploads/5/
Access 2000 (60 KB): - uploads/5/

Consider donating $1 per sample downloaded," rel="nofollow - find out why here .<

Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.03 -
Copyright ©2001-2019 Web Wiz Ltd. -