Referential Integrity (1 Viewer)

cindy.endris

Newbie
Local time
Today, 12:37
Joined
May 29, 2008
Messages
20
Good Morning All,

I need YOUR help! I am trying to build a relationship (with referential integrity) between two tables but Access is not allowing me to. The message I received stated:

"Microsoft Office Access can't create the relationship and enforce referential integrity.
Data in the table 'tbl 401k' violates referential integrity rules. For example, there may be records relating to an employee in the related table, but no record for the employee in the primary table. ...."

I double checked the records and it matched perfectly. I even brought the data over to excel and use the EXACT function to reverify. I even try to use different join type and still receive the same message mentioned above.

What other rules are there in regards to Referential Integrity?

Any suggestions/comments will be greatly appreciated!

Cheers, Cindy E.
 

twoplustwo

Registered User.
Local time
Today, 12:37
Joined
Oct 31, 2007
Messages
507
Well what are you trying to link?

Customers? Orders? Goats?
 

cindy.endris

Newbie
Local time
Today, 12:37
Joined
May 29, 2008
Messages
20
It shouldn't matter what I am trying to link, should it? I am linking Employee Codes to Employee Codes.
 

twoplustwo

Registered User.
Local time
Today, 12:37
Joined
Oct 31, 2007
Messages
507
Well no, as different scenarios have different relationships (i.e. one-to-one, one to many) Establishing which is required for your particular need is important.

I don't understand why you're linking to seemingly identical tables?? What data is in there??
 

cindy.endris

Newbie
Local time
Today, 12:37
Joined
May 29, 2008
Messages
20
I think we're not on the same page here.

I am not linking two identical tables together. I'm linking two identical fields together to create the relationship between two tables because that is the only way to build a relationship between two tables. I need to be able to create that relationship with enforce referential integrity to build my queries, forms, and reports. Does this make sense?
 

kidrobot

Registered User.
Local time
Today, 15:37
Joined
Apr 16, 2007
Messages
409
are you using the special Relationships window for tables?
 

cindy.endris

Newbie
Local time
Today, 12:37
Joined
May 29, 2008
Messages
20
I don't know if it's special. ha! Yes, I am using the relationship window. Is there another way to build a relationship?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:37
Joined
Sep 12, 2006
Messages
15,614
not sure, but possibly its a key issue - would the foreign key in the many table HAVE to be the primary key of the ONE side. I often dont bother with enforcing integrity, so I dont know for certain, offhand

If it isnt a key issue, then you MUST have some floating records

Try the unmatched query in access again
 

Banana

split with a cherry atop.
Local time
Today, 12:37
Joined
Sep 1, 2005
Messages
6,318
Cindy,

I'd second Gemma's advice as Access can't magically link together two tables if there's even just one record that has no corresponding match in another table.

Also, just to be 100% sure, though I think very unlikely, are both fields you're trying to link of same data type?
 

cindy.endris

Newbie
Local time
Today, 12:37
Joined
May 29, 2008
Messages
20
Sorry for the late reply. To answer boblarson's question, I wish I could post the database but I cannot. It contains confidential information. I got good news though. I figured out the problem. I just query the two tables together and find a blank value so I deleted that one record to enforce the referential integrity. Somehow excel didn't pick up on that but I think it's because I had a relationship between the two tables so it only brought over matching records to .xls

However, I got another problem as usual. I have about 20 tables and all of it EXCEPT for one has One-to-Many relationships. The one table has the One-to-One relationship. How can I change it to One-to-Many?

Thanks again for taking the time to help me out! Cheers, Cindy
 

Banana

split with a cherry atop.
Local time
Today, 12:37
Joined
Sep 1, 2005
Messages
6,318
Cindy, glad you found that erratic record. :)

As for that one-one that is set whenever you link two keys that are declared as primary keys. For a one-many, the many-side table's linked key shouldn't be a primary key for that table. Just add another ID in that table, and make it a primary key, but link to the old primary key then you should now have a one-many relationship.

Also, just in case, check that the old primary key, after it's no longer set a primary key has its Index property to "Duplicates OK", not "No Duplicates"
 

cindy.endris

Newbie
Local time
Today, 12:37
Joined
May 29, 2008
Messages
20
Thanks Banana! Actually, I figured out why access didn't set it as one-to-many because there again was a missing record in the other table (blank value).

But now I'm onto the queries and another problem occur. I have 4 queries and they all look fine. But then I decided to query of 4 queries into 1 so I can generate a report. (The reason why I had to have 4 separate queries is because I have so many fields)

My problem is that when I go to datasheet view, it shows no records. It may be another missing record or something. It's weird.

Any suggestions?
Cheers, C
 

boblarson

Smeghead
Local time
Today, 12:37
Joined
Jan 12, 2001
Messages
32,059
(The reason why I had to have 4 separate queries is because I have so many fields)

Frankly that statement scares me. It really makes it sound as if you need to do additional normalization. How many fields are we talking about?
 

cindy.endris

Newbie
Local time
Today, 12:37
Joined
May 29, 2008
Messages
20
ha ha! Sorry about scaring you. You reminded me that the reason I received an error message was probably due to the fact there were missing record (blank value). The error message I received earlier when I tried to put all the tables in 1 query was:

"Subscript out of range"

Heck, I didn't know what that meant and so I made the assumption that it had too many fields. So I remembered what the Access instructor told me about staggering my queries if I had too many fields for 1 query.

So when you told me that I scared you, I decided to try to run the query using all of my tables (now that I resolved the missing record problem) and it WORKED.

Then again another problem has occur when I try to generate a report.

This is a bit more complex so here's the story:

My database consists of all the employees' benefit package information. Such as whether they have medical, dental, and vision along with the coverage level, premiums, etc.

I want a report that shows what each employees have so I can give it to them on an annual basis. However, the report is not generating in the way that I want.

One example is that not ALL of the employees' dependents are listed on the report. It only shows one dependent. How can I get it to show all of the dependents on one report per employees? Some employees have no dependents, some have 1, and others may have 5.

Any suggestions?

Thanks again for your help,
Cindy E.
 

Banana

split with a cherry atop.
Local time
Today, 12:37
Joined
Sep 1, 2005
Messages
6,318
How many fields do you have in each of query? The maximum is 255, and darned if I've ever broken 30 fields in a single table. If you really have that many fields, then this is a major problem, and not fixed by staggering queries (which I suspect your instructor may have been talking about optimizing for faster queries, but not as a workaround for maximum numbers of fields).

Also, can you post your query's SQL? To do that, open your query in design, then choose View -> SQL View. Copy & paste the SQL and put this between code tages, like this:

[code]SQL Statement here[/code]

so it comes out like this:
Code:
SQL Statement
 

Users who are viewing this thread

Top Bottom