Enforce referential integrity

[bubly1322]

Hello everyone!
I am new to the Microsoft Access. I have an issue with my database. I cannot create referential integrity; I mean enforce it in my database. I attached a screenshot of my database. The problem is that I have to make the relationship between tblCommunityCensus and tblNaturalAreas, tblSchools, tblCommunityCrime, and tblCommunityServices. All the tables have one common field which is comm_code. The comm_code is PK in the tblCommunityCensus and FK in the rest of the tables. All PK and FK have the same data type and I even checked the missing comm_code and even tried to add them to the child tables but still, I am getting an error sying data in the table 'tblSchools' violates referential integrity rules.. Any advice????

[June7]

You already have data in tables?

Why is comm_code a PK in two tables?

tblCommunityCrime and tblCommunityServices are already related to tblComunitiesByWard.

tblParkPathways and ...Stops cannot be linked to both tblComunitiesByWard and tblCommunityCensus by same key.

If you want to provide db for analysis, follow instructions at bottom of my post.

[bubly1322]

Yes, the data is in tables. I tried to make connections between all of the tables. The main point is to connect the tblCommunityCensus to tblNaturalAreas, tblSchools, tblCommunityCrime, tblCommunityServices, tblParkPathways, and tblTransitStops. The connections work between tblCommunityCensus and tblParkPathways, and tblTransitStops but the rest do not.

[June7]

Makes no sense for a table to be related two master tables on same field.

Have not addressed my question about comm_code.

[madpiet]

(You're going to hate yourself if you leave all the primary keys as PK.)
Imagine writing a query and reading it maybe six months from now. What does "PK" mean, or better, Which table does it refer to?

[bubly1322]

When I wanted to set up the PK for other fields in tblCommunitiesByWard I was getting error saying they would create duplicate values in the index. But I found out that I can change the PK for Name field which I've done already, now PK is Name field and FK comm_code for this table.

[bubly1322]

Sorry I am new to this

[June7]

And Name is a reserved word. Should not use reserved words as names.

Also, why have the PK autonumber field if you don't use it as primary key?

[Welshgasman]

(You're going to hate yourself if you leave all the primary keys as PK.)
Imagine writing a query and reading it maybe six months from now. What does "PK" mean, or better, Which table does it refer to?

Same would apply to comm_code as well would it not?

[bubly1322]

I tried to do relationships by using PK in tblCommunityCensus with other tables. I had to add the PK fields to other tables because when I tried to set the Primary key by using a different field it was saying they would create duplicate values in the index.
I try to do a query that will count how many for example schools are in the community (comm_code) in tblCommunityCensus.

[June7]

So why is Name field assigned as PK?

Sorry, I can't make sense of anything you are saying.

[bubly1322]

So why is Name field assigned as PK?

Sorry, I can't make sense of anything you are saying.

Sorry.. I made a little changes so maybe now it will more clear.. No name as Primary key and tblCommunities is a new tblCommunityCensus.

[June7]

Okay, that looks better to me. The issue is same?

Again, could provide db for analysis.

[madpiet]

Maybe post a (sanitized) version of your database. The comm_code in nearly every table? Without seeing the data, it's hard to tell what it means.

[Micron]

It's not looking great to me. Aside from the fact that I don't see a tblCommunity_Consensus, I see tables with PK fields and no foreign key for them in any other table. Also see 2 id fields in the same table, repeating fields in tblSchools and question the comm_code being in every table. I question that there is no link between what looks like a one to many (or parent/child if you prefer) between communities and services.

I think you should review db normalization and see if you can fix all of this, or post a detailed description that explains what it's all about. Whatever explanation you give, expect more questions because there's no way for you to anticipate everything we'd need to know. Maybe start with the links in this thread
https://www.accessforums.net/showthr...773#post521773