So i built this really awesome, huge, complicated database. Now, I'm slowly discovering that the design was built wrong in the first place. Ahh, the power of "hands-on learning"

This database tracks Job announcements and the employees that get hired. For jobs, I have a table called [tblJobs]. This table has several "selection fields" in it (where I've used another table to program the acceptable entries). Mainly though, this table [tblJobs] has a LOT of dates. Each date corresponds to a time line which tracks the progress of each job and then calculates how fast it was done, who did it, etc...

I had originally built this database so that each date was it's own field. After a general discussion about it with a friend, I'm told that I should have reduced this table even further by associating all those different dates to a single action and then track that action in a seperate table. But now, i'm having problems putting this new concept to work.

My tables look like this:

Person in charge of job > [tblSpecialist]
Jobs > [tblJobs] connected to a [tblSpecialist] through "SpecialistID"
Action of Job > [tblAction] connected to [tblJobs] through "ActionID"

Most all of my number crunching will come out of those "actions" and I'm told that these calculations will now be a lot simpler. How? Before, if i wanted to calculate the time from action1 to action4, I just did a simple date subtract formula for each field. Ok, so say there are 25 actions (a "description" and a "date") How do i refer to a job's specific action?
Would it be: [tblJobs].[tblAction].[Date] ???

Also, by separating these tables, it has confused me on how i can upload data from excel. I assume I need to break up my spreadsheet and upload independently to each table? (boy what a mess that will be)

But now for the super, duper, main question.
Previously, I had a form to input the data and each field was respectively represented by a nice, clean control for each date. Now that i have just one action field to represent all these dates, how can i display that on a form so that it looks the same as before (a control for each possible action)

Wow, I'm sorry this is so long and I hope it makes sense.

Edit: After thinking further, i don't believe I have the tables right. This is still concept, I have not built it. If the ActionID (located in [tblJobs]) has multiple entries, how will that show up? by listing the job 25 different times? I hope not :(

It is the process of normalisation or normalization. On Wikipedia you'll find the steps to perform it. You don't need to go any further than the BCNF (Boyce-Codd Normal Form) which is slightly better than the third normal form.

Next time, post a sample database with all the tables and fields in place. It makes commenting much easier.

Enjoy!

OK. The old database with the old table design is all I have, so I built this sample database to attach here. My initial question is: How does the table structure look?

Also, on the JOBS form there are 2 questions. They are:

#1 This form shows all the fields in [tblJobs] which includes a "Service" field. [tblSpecialist] is connected to [tblService]. How can I also show my users who the Specialist is on this Form when the form is connected to one specific table.

#2 Same type of question: How can i get [tblJobAction] to show on this form? I want to show the actions for this job. I also want each action to be in it's own control, even if it is blank. Will I have to have a seperate popup form to display all the actions?

Edit: I can't attach this from work, i'll have to take it home to attach it.

It's not letting me upload my attachment.... The attachment screen abruptly stops and changes to an Error Screen saying there was a problem with the "Access World Forums Database"

Edit: Nevermind, i'm just going to scrap the whole idea and move foward with my giganta-base....