History/Archive Tables - Normalization - Table Levels

[RockKandee]

I am using Access 2013 on Windows 8

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

http://bytes.com/topic/access/answer...n-table-levels

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.

Example:
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.

[zmbd]

Can you give me a list of the tables you have and their relationships?

i.e.
tbl_client
tbl_class
tbl_enrollment
-related to client 1:M, related to class 1:m
othertables
-other relationships

[RockKandee]

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
NOTE:
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.

[zmbd]

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...
%(|:-)