MS Access – Help with Relationships

ms access

I am in need of some assistance. I'm fairly new at access & dbs, and have managed to get a bit accomplished, but have run into a wall. I'm not sure it is my inexperience or if access cannot accomplish what I want (or if it is just not easy…)

Anyhow, I need to create a couple relationships. I’ve got a few, but now I’m getting in too deep for my level of experience, and can’t figure out how to get it to do what I want.

Basically I need to record the following:

• Base/Parent Contract (BC) 

   o    BC DOES have Line Items (CLINS)

   o    BC CAN have Modifications (Mods)

   o    BC CAN have Delivery Orders (DO)

• Delivery Orders (DO) 

   o    DO DOES have CLINS

   o    DO CAN have Mods

Example:

Contract: F12345-D-17-0001

 Delivery Order 0001

      CLIN 0001AA   

      CLIN 0002AA

 Delivery Order 0002

      CLIN 0001AA

      CLIN 0002AA

      CLIN 0003AA

      Modification P00001

      **Modification P00002**

 Modification P00001

 Modification P00002

Each one of these bullets has its own set of data. The contract number is unique by itself, but its number actually flows down to each of the others, as they get appended to it…. For example, the BOLD Mod above would actually be listed as “F12345-17-D-0001-0002-P00002”. Under each base contract the Modifications, CLINs and Delivery Orders all basically start from 1 and increase, so you can see that there will be many of those records with the same number, so it is a different ID used as the key for each of those.

Anyhow, I have been able to establish these relationships:

 Subordinate DO to Parent Contract

 Subordinate Modification to Parent Contract

 Subordinate CLIN to Parent Contract

 POC Contact Info to Base Contract

What I am having problems doing is associating: (I think because they are 2nd layer subordinate to the Base Contract)

 CLINs that go to Delivery Orders and 

 Modifications that go to Delivery Orders

It all hinges off the Base/Parent Contract and cascades down from there.

Here is my relationship table.
Here is my relationship table

And this is what my form looks like – how I want the info portrayed in the end if it helps.

And this is what my form looks like - how I want the info portrayed in the end if it helps.

Best Answer

You'll notice that I do not mention every table. Hopefully that would not be necessary as you begin to understand the proper normalization patterns. You should be able to analyze and setup relationships on those other tables using the same patterns.

Once you make these changes, you may find that you have questions about the proper way to get and display related data, for example the contract number for a given CLIN--especially if you've eliminated the redundant fields as I advise. Look into crafting proper queries and using such queries as the Record Source for forms and reports, etc. Further questions about such queries would require new questions. Also, question regarding query specifics and SQL may be more well received on Stack Overflow.

  1. The overall principle here should be proper database normalization. Although it is often not practical or necessary to adhere to every level of normalization, it should at least a guide for the eventual table and relationship structure. These concepts can be a bit abstract, but it is still best to study it.
  2. Delete duplicate fields like [CLIN Info Table].[Contract Number], [CLIN Info Table].[Delivery Order Number], [Delivery Order Table].[Contract Number], etc. The point of a relational database is to always get such information via the relationships. If you want a [Contract Number] for a given CLIN, then you execute a query which returns the related value from the proper table.
  3. Looking at the relationships as they are now defined (in the original image), you can see 1-to-many relationship between [Delivery Order Table] and the [Base Contract Data], but as I understand your description this relationship is backwards. You want multiple Delivery Orders per contract, not the other way around.
    • For this relationship, I suggest 1) deleting the existing relationship between the two tables; 2) eliminating [Base Contract Data].[Delivery Order ID]; 3) Creating an enforced relationship between [Base Contract Data].[Contract ID] (the primary key) to [Delivery Order Table].[Contract ID] (the foreign key).
  4. Delete [Delivery Order Table].[CLIN ID] to eliminate confusion.
  5. Since [CLIN Info Table] will have records linked directly to [Base Contract Data] and directly to [Delivery Order Table], then it needs to have two separate "relationships". This can be accomplished in at least two ways.
    • The first scheme involves having two foreign keys in [CLIN Info Table], one for each of the tables. This scheme is only valid if a single [CLIN Info Table] can be linked to only one [Delivery Order ID] record or one [Base Contract Data] record. If the answer to my comment question is that the exact same [CLIN Info Table] can be linked to multiple Delivery Orders or Contacts, then you'll need to skip to the alternative scheme.
      • One of them you already have established correctly: [CLIN Info Table].[Delivery Order ID] (foreign key) already related to [Delivery Order Table].[Delivery Order ID] (primary key). Leave that as is.
      • Next remove the existing relationship between [CLIN Info Table] and [Base Contract Data] and delete [Base Contract Data].[CLIN ID]. You'll notice this is the same type of incorrect relationship as in #2 above.
      • Add a relationship between the existing fields [Base Contract Data].[Contract ID] (the primary key) to [CLIN Info Table].[Contract ID] (the foreign key).
    • The alternative scheme satisfies the next level of normalization. It may also be necessary if the exact same [CLIN Info Table] can be linked to multiple other-table records. It requires a third table for establishing each relationship. It avoids the problem of having multiple null foreign keys. I'm sure this topic could be heavily debated, but I personally think it is just a matter of preference. Adding a third table in a relationship necessarily makes queries more complex. Although this pattern is meant to eliminate certain other relationship problems, it also establishes the ability to have orphaned child records, which if a huge problem, you'd need some way of managing those.
      • Eliminate foreign key fields from [CLIN Info Table]
      • Create a new table named something like [Oder CLIN] with only two required fields: [Delivery Order ID] and [CLIN ID]. You need to create appropriate indexes for these field, depending on your answer to whether the same exact CLIN can be linked to different Delivery Orders.
      • Create another new table named something like [Contract CLIN] with only two required fields: [Contract ID] and [CLIN ID]. You need to create appropriate indexes for these field, depending on your answer to whether the same exact CLIN can be linked to different Contracts.
      • Create relationships between the foreign keys in the new tables to their corresponding primary tables.
Related Question