Convert Flat Database to Relational Database

[Sylvia8146]

I currently have a flat, single table, database for recording the dates that staff attended various training courses. It consists of Name; and Dept; fields plus about 50 named Course fields, so that each record contains all the information about one member of staff and the dates they attended training. There are 460 staff records with attendance dates for around 50 different courses.

What I would like to do is convert this into three separate tables - Staff; Course; and DateAttended. I have created the tables and imported data into the Staff and Courses tables but I'm having problems with the date of attendance.

In the original table the data is stored horizontally and I need to change this to a vertical format (I think). Any ideas please?

 

[directorz]

How about 4 tables

tblStaff
StaffID - pk
Name

tblCourse
CourseID - pk
StaffID - fk to tblStaff
CourseName - fk to tblCourses / CourseName

tblCourses
Courses - pk

tblDates
ID - pk
refCourseID - fk to tblCourse
DateAttended

 

[Sylvia8146]

Thanks for your reply. I'm not sure I explained my problem correctly - I need to use the existing data and that is laid out as follows:
Name Base CPR Fire PDR Rehab Excel
Joe RAH 5/2/04 8/11/03 5/6/03 3/9/03 4/5/04
Mac YGC 3/8/03 12/5/02 6/7/03 4/5/04
Jim HMS 11/7/03 2/1/04 5/9/03 5/4/03

The result I need is like this:
Name Base Course Attended
Joe RAH CPR 5/2/04
Joe RAH Fire 8/11/03
Joe RAH PDR 5/6/03
Joe RAH Rehab 3/9/03
Joe RAH Excel 4/5/04
Mac YGC CPR 3/8/03
Mac YGC PDR 12/5/02
Mac YGC Rehab 6/7/03
Mac YGC Excel 4/5/04
Jim HMS CPR 11/7/03
Jim HMS Fire 2/1/04
Jim HMS PDR 5/9/003
Jim HMS Excel 5/4/03

I can't seem to get the dates from the current table into the new format - does that make sense?

 

[fneily]

If you're using Access, then first you need to Normalized your data in the way directorz showed. Relate your tables, construct your joins. Then it's a simple matter to get your final result.

It looks from your second post you have an Excel list. To get your final answer, just use the Transpose function.

 

[directorz]

One other thought. Check out MSWorks database. It's a cheapo but it does exactly what you want.

 

[PHV]

To normalize your data use an union query:
SELECT A.Name, A.Base, 'CPR' As Course, CPR As Attended FROM oldTable A WHERE CPR Is Not Null
UNION SELECT A.Name, A.Base, 'Fire', Fire FROM oldTable A WHERE Fire Is Not Null
UNION SELECT A.Name, A.Base, 'PDR', PDR FROM oldTable A WHERE PDR Is Not Null
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244