Table Relationships w/ Related Record error

[Dave D]

This seems like it should work but I keep getting an error. I have the following tables:

tblVendors
tblContractors
tblPOCs

tblPOCs holds the points of contact for Vendors & Contractors. I want to set up a 1:M relationship with tblVendors -> tblPOCs and tblContractors -> tblPOCs with cascade delete - if I delete a Vendor than I want to delete all of the associated Vendor's POCs (same with Contractors). All works fine if I have just Vendors/POCs or Contractors/POCs, but if I have both Vendors & Contractors with a 1:M with POCs then I get an error stating a related record is required in (e.g.) tblContractors.

I don't see how this can be a circular relationship; it seems rather straight forward... to me, that is. Perhaps I have my table and/or relationship structure laid out incorrectly?

Thanks for any help.



PS - when solved, I get to work a similar issue with addresses, but the relationships will be 1:1.

[burrina]

You get the error when and under what conditions? Can you post a screenshot of your relationships? What exactly is the error?

[Dave D]

Here is a picture of an example relationship. I get this error when I try to add a record to tblPOCs.

[burrina]

You still did not say what error you get. Also, Please describe your Schema. That is, your goal with the relationships. Is tblPOCs your primary table and you can have multiple Vendors and multiple Contractors, etc... ?

[Dave D]

If I try to add a record to tblPOCs with a fkId from tblVendors I get the error: You cannot add or change a record because a related record is required in table 'tblContractors'

One vendor can have multiple POCs
One contractor can have multiple POCs
POCs is NOT the primary table. I might, in the future, have other organizations that have POCs.

I can write the VBA code to do what I want, but I would rather use the inherent database functions to cascade delete the related POC record when I delete a parent record from tblVendors or tblContractors. However, before I can delete I need to be able to add a record with this setup.

[burrina]

The problem is with the Relationships. By design you are saying you have to have a Vendor and a Contractor before you can have a POC
Fix this and the problem will disappear. We can't see what else is going on with your forms/queries,etc...

[orange]

How do Contractors and Vendors relate?

[Dave D]

There is no relationship between Contractors and Vendors. However, they both have POCs. I was just trying to not have a Contractor's POC table and a Vendor's POC table. I was trying to have a single POC table.

[thebigthing313]

Why not have 2 separate foreign key columns in tblPOC, one for Vendors and one for Contractors? Then deletes on either Vendors or Contractors will cascade to tblPOC.

[Dave D]

I have already tried your suggestion. In fact, I have tried every combination I can think of. I still get the same error mentioned above. I looked at the Northwind example to see how they (MS) handled addresses (which would be similar to POCs). Each entity in Northwind has their address within their table. There is no single "addresses" table that can be shared amongst the different entities.

I'll leave this one open for a while in hopes of a solution but I'm thinking it's not doable.

[orange]

Can you tell us in a few lines, plain English what are Contractors and Vendors and WHAT exactly is a point of contact POC in your business?
Also, it isn't that you want to create a 1:M relationship, it is that the data within your business represents a !:M or M:M relationship.

[Dave D]

Vendors sell me things. Between the salesmen, PMs, contracts people, I may have a half-dozen POCs for each vendor and I need to keep contact info on each.

Contractors do the work. Between the PM, billing, etc. I may have 3+ POCs for whom I need to keep contact info.

I was looking to have a single POCs table, but it doesn't look like I can do that. I can see the Pros for multiple tables...

[thebigthing313]

So..

Vendors 1:M POC
Contractors 1:M POC

Vendor POC's do not relate to Contractor POC's. Vendors and Contractors do not share POC's.

If I understood correctly, then yes, you will need to keep them separate. If you want a single POC table, then you would need junction tables from Vendor and Contractor to POC, i.e tblVendor 1:M tblVendor-POC 1:1 tblPOC and tblContractor 1:M tblContractor-POC 1:1 tblPOC.

[Dave D]

Thank you. I will need to look into this more thoroughly. I shall report my results, but it may take me a while as I now have additional tasks to complete.

[kagoodwin13]

In my opinion (take that for what it's worth!) this seems like it would work with a normal join, but you would lose referential integrity.

I think you could also have vendors and contractors in one table. Unless you can think of a very specific reason why they would need to be in separate tables. It seems like to me that you are collecting common data on them already, so they could easily be one table called tblOrganizations or something similar.

Org_ID     Org_Name     Org_Type    webAddr     preferredStatus
--------------------------------------------------------------
1             Acme           Vendor       acme.com   Yes
2             Brawndo       Contractor                   No