By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,826 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,826 IT Pros & Developers. It's quick & easy.

History/Archive Tables - Normalization - Table Levels

P: 89
I am using Access 2013 on Windows 8

After receiving info on the below linked thread, I have found myself with a new dilemma.

I was advised it might be best to merge the history table in with the progress table.

I am running into issues with merging and I am hoping there may be some other options that I don't know about.

My database has several required levels to go through prior to arriving in an In Progress Table. Each level has required data including the foreign key to the level above it.

After the progress is complete, a history table is appended. If I merge the two tables I will need to:

1- Store unneeded progress info permanently in 4 tables
2- Create fake data so that the history records can be entered into the progress table.
3- Create fake data for new records that do not need to go through the progress stage.

Basically, I will be creating and storing a lot of data that I do not need and that is not real.

I understand the benefits of eliminating a history table and I am trying to find an alternative.

I could use some advice please.

zmbd signs up for a Beginner Scuba Course
I need to record the data for - course >> Option for completion >> scheduled course >> session dates >> progress >> completed (appended to history)

ADezii signs up for a rescue class
I record his pre-req courses that he took from one of those PADI instructors ;) directly into history.
Then I use the scheduling process for the rescue class.

It wastes time to schedule an entire course just to record a date and cert name.
Jan 15 '14 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 5K+
P: 5,397
Can you give me a list of the tables you have and their relationships?

-related to client 1:M, related to class 1:m
-other relationships
Jan 15 '14 #2

P: 89
Like this??

Category (1 to Many) >> Name
Name (1 to Many) >> Info
Info (1 to Many) >> Number & Date
Number (1 to 1) >> Date
Number & Date (1 to Many) >> Schedule
Staff (1 to Many) >> Schedule
Staff (1 to 1) >> Student
Facility Schedule (1 to Many) >> Schedule
Facility (1 to Many) >> Facility Schedule
Schedule (not sure) >> Progress
Schedule (1 to 1) >> Calendar (not a table)
Student Info (1 to Many) >> Progress
Student Info (1 to Many) >> History
Info (1 to Many) >> History
Student Info (1 to Many) >> Student Contact
Category also determines data displayed on calendar for the schedule table, so new categories will need to exist so that fake schedules do not display. Schedule >> Calendar – Each record in schedule goes into the calendar – not sure what that is called – but calendar is a form so….
Number & Date are same level but date has unresolved issues due to it coming from a min query based on the schedule table. I cannot bypass Schedule and go directly from Number/Date to Progress because date will not exist without schedule and date needs to be in History.
Jan 15 '14 #3

Expert Mod 5K+
P: 5,397
Yep, let me sit on this a few moments. We're short handed in the lab again today so I have a few extra hats on my head...
Jan 16 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.