Not sure why this one confuses me... I think it should be much simpler.

This is very related to project planning, so please bear with me. I have project timeline templates that I want to apply to different projects. I do have and use MS Project, but I find Project has done it's duty in helping me plan these templates and now I need the flexibility of Access to take care of the rest (plus, I'm much more proficient in Access).

Here's an example of one of the timelines and the milestones (relative days is the date from the beginning of the project that the step must be completed):

Customer Order Date, Sequence 1, Relative Days 0
Sales Order Entry, Sequence 2, Relative Days 5
Material Shipment, Sequence 3, Relative Days 14
Material Arrival, Sequence 4, Relative Days 28
Shipment Loaded, Sequence 5, Relative Days 60
Bill Customer, Sequence 6, Relative Days 90

So basically, that simplified project has 6 steps which should take a total of 90 days (my projects have up to 16 steps).

I have 15 to 20 different project templates (using some common milestones such as Customer Order Date and Sales Order Entry, etc).

I want to apply a template to a new order and keep track of the status (actual date achieved vs. date planned) to monitor the progress of the order.

Here is the problem:
Right now, I have an order log where I enter the Order Number and related info (order due date, etc.)... then I have another table where I essentially paste in a copy of the timeline template for that project type (ie. the example above) and keep the status.

However, the major problem is that it's not relational. I want to link the Order Number to the associated timeline template. In addition, if I have two projects that have some steps that are related I want to update them both at once (ie. material arrival may be for 3 or 4 different orders). I keep shipment tracking numbers and often combine shipments. Also, if I decide that Sales Order Entry should only take 4 days instead of 5 then I have to go and update every project with that step in it. Lastly, I want some of the milestones to be related to another (ie. Material Arrival is linked to Material Shipment)... this is for reporting and tracking purposes.

I'm thinking I need a table for:

Milestones (ie. Sales Order Entry, Bill Customer, etc.)
Timelines (ie. choosing all the milestones for one particular project)
Order Log
Shipments (to keep tracking numbers and to associate different orders to a single shipment)

Also, probably a table for Milestone Completions??? Not sure about this one...

Now, how do I set up the relationships between the tables... ???

What do you mean by this statement?

"then I have another table where I essentially paste in a copy of the timeline template for that project type (ie. the example above) and keep the status"

Anyway, EVERYTHING is relational. Sometimes the entities are not readily apparent within your business model. When this happens we tend to under define the entities, by that I mean we tend to mix entities (have tables that have attributes from more than one entity). This is often caused by the terms (or names) we give the attributes. Another problem is that we sometimes tend to try and store calculated values that aren't readily identifiable as such. For example, Timelines. A timeline is actually a calculated value based on the beginning and end dates and times for a given activity.

I use an append query to paste the related records into a new table.

I am trying to redesign this now.... very difficult for me. Any help or advice is very much appreciated (like you wouldn't believe)!

Here is a sample of the database. I can't seem to get the data entry to work for Shipments...

Each shipment should reference one or more projects (orders)... and only a specific milestone on that project.

I reread your statement again, and ensured that for the time line I am only storing the relative days. Therefore, if my project takes 91 days, it will be calculated to complete 91 days from now.

I have looked at your structure and think I know why you're having a problem with the shipment detail. Are you sure that the shipment is an attribute of a milestone? Also, something isn't ringing true with me concerning the timeline table and the milestone table.

My understanding of projects is that they are made up of tasks (I think you are calling these Timelines). Each task takes a certain amount of time to complete. Along the way to completing a given task there may be none, one or more milestones that are achieved. A milestone might also be achieved without benefit of a task (i.e. the task is the milestone). If this understanding is correct then you do not have the relationships established correctly. A milestone is not an attribute of a timeline. A milestone enjoys a many-to-many relationship with the timelines. Shipments would also enjoy a many-to-many relationship to timelines and are independent of any particular milestone.

You'll have to correct me if my assumptions are wrong.

Well, a shipment can be attributed to milestones from various projects.

For example, we ship some materials for many different projects together. Therefore, the dates for that shipment apply to each one of the projects for which is contains materials.

Your understanding is correct... projects are made up of tasks, and each takes an certain time to complete (and for planning, an estimated time). We compare estimated vs. actual to monitor status. These tasks may or may not be depending on a previous task (but in this database, I've left dependencies out). In this database, Tasks = Milestones.

I will have a look at the relationships to see if I can fix it, I think I have a hint at what you're getting at.

Thanks very much!