Need Help with portion of this design

[shelzmike]

Hey all. I am relatively new to actually building databases. Funny thing is that I took several classes on database design and understand the terminology and rules, but there is absolutely something to be said about the "street" experience when actually being able to put something together. That is what I am lacking, but am learning as I go.

So here is the problem. I am starting a database that is in Access 2010. I thought that I had normalized properly, and built the tables appropriately; however, when trying to create realtionships I am unable to. For some, the relationship type states that it is "indeterminate", which I know is not good. Plus, I cannot enforce referential integrity on between a main table I have and all its corresponding tables. I have a feeling this is where I am going wrong. I will try to make this as brief as possible as I know it sucks having to read dissertations on the forums.



Here is the background - we have a dispatch department that needs to keep track of incomplete jobs, which is the premise of the whole database. I have inserted an image with the relationship diagram (you may have to click it for a larger size.

The major players are as follows:

MA = Management Area (synonymous with region)
FSM = Field Service Manager
EA = Eastern Arc - this is a specific question that may or may not be on any given tracker item. They are presented on a form as a combo box.

I am hoping this is not so horrible it makes some of you sick! Seriously though, I cannot figure out any better way to separate the larger Tracker Table in this situation. I am quick to respond if there are any clarification questions. Thanks!

FYI - The farthest table to the right in intentionally cut out because I am not having any issues with that portion of the database.

[jzwp11]

The join between tbl_Dispatcher and tracker_tbl should be using the primary key field of the tbl_Dispatcher (I assume dispatcher_ID) not the dispInit field. As far as I can tell the other relationships look OK but if you still cannot make a join make sure that the foreign key field is of the same datatype as the corresponding primary key field. (autonumber=long number)

[ssanfu]

I saw the same thing as jzwp11 about the dispatch table linking on the wrong fields. I created your tables (in A2K) and all tables linked..... but I might not have the same field types as you. All table relationships were able to have referential integrity.

[shelzmike]

Thanks for the help so far both of you. Actually, I hadn't even noticed the Dispatcher to tracker table link being messed up.

Let me clarify though - I can get relationships to build, as you can see in the diagram; what I meant to say was that Access cannot pickup if they are Many to One, One To many, etc. It only says Indeterminate - so not sure what that means.

All data types are the same, indexes seems okay, keys seem okay - so I thought it was design problems.

Also, it will not let me turn on referential integrity - is that normal in this situation? THanks!

Mike

[ssanfu]

In a *copy* of your database, delete ALL data. Then 'Compile and repair'.
Now try to set your relationships. If you can set the relationships, add the data.

If you have data in the tables, Access sometimes won't allow referential integrity because the data violates the constraints.