Unable to link two tables

By eckardtk ·
I have a main table/form called Contacts with details about persons and companies. I want to have a second main form that tracks the scheduled events such as presentations, workshops etc as well as who is going to attend (subform).

I have been able to establish most relationships. The remaining problem is that I cannot link EventId (PK) from table Event- Schedule to EventId (FK) in table Attendance. When I click Create, an error message pops up:
?Access cannot create this relationship and enforce referential integrity ??

Here is the overall layout:

Table Contacts
PK = Contact ID

Table Attendance
PK = Attendance ID
FK = Contact ID
FK = Event ID <== problem

Table EventSchedule
PK = Event ID < == problem
FK = EventType ID

Table EventType
PK = EventType ID

The grand scheme is to set up two main forms for data entry:
- Contact Details
- Event Participation

The Event Participation form would consist of two parts:

(a)Main form = description of the event; based on the table EventSchedule

(b)Subform = who is going to participate; based on table EventAttendance. There is also a lookup field in the subform which is linked to table EventType.

How can I make this work??

Besides the point: I tried to copy and paste a screen shot of the tables window here but I am unable to accomplish that. I had saved the screenshot as a Word file.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Some ideas

by scott_heath In reply to Unable to link two tables

Here's a MS KB:

My guess is you have an EventId already defined Attendance table that does not exist in the EventSchedule table. The easy way to find out:

select eventid from attendance where eventid not in (select eventid from eventschedule)


Collapse -

table link not working

by eckardtk In reply to Some ideas

Hallo Scott,
I looked again on the error message. It clearly says there is a problem with records in the Attendance table. Currently, the database is empty - no data yet except for a few lookup fields.

I also checked the link you provided. It appears I did everything I could according to the article.

What occurs to me now is that:

(a) the attendance information only makes sense when taken together with a contact name. And, I have stablished that relationship between Contacts table and Attendance table without a problem.

(b) The EventSchedule table/ form could easily be a stand-alone item. Except, that I want to have the attendance information entered here too, albeit into a subform. It makes sense to first enter the event details and then continue with who is going to attend on the same form/subform

The way I understand it, the underlying tables for main form and subform need to be linked together. Only for that reason did I introduce the Event ID into the Attendance table. But, somehow my logic seems to be screwed up. Access won't allow me to establish this PK/FK relationship by using EventID.

Collapse -

Post file?

by scott_heath In reply to table link not working

Can you post the mdb file somewhere. If the tables are empty there shouldn't be any pricacy issues. I work in SQL Server not Access, but most of the principals are the same and I've helped others with Access databases, so maybe I can figure it out for you.

BTW, did you make sure the data types are identical?


Collapse -

table link not working

by eckardtk In reply to table link not working

Hallo Scott
I like to post the file and, I looked at the website you provided. I cannot find a place where I can post my file. I tried mail and I tried blog - it always asks to sign up for something called windows life.

Collapse -

Worked OK For me

by techrepublic.posting In reply to Unable to link two tables

Created the tables, with NUMBER datatype (because you said it was an ID), and I was able to create the relationship just fine.

You must be doing something wrong. Check your datatypes, spelling of the fields, and the direction of your relationship.

Collapse -

The link

by techrepublic.posting In reply to Worked OK For me

The url given above was copied from my address bar directly into the post, but I don't know why it isn't working...the file is there...guess hostultra is having problems.

Collapse -

Re: worked for me

by eckardtk In reply to Worked OK For me

I have set all FK fields to number (long integer) since the PK's are all autonumber fields.
I make sure that I drag the PK field onto the FK field.
I checked one more time the spelling.
What else could I be doing wrong ?? (since it obviously worked for you.)

Collapse -

Have a look at this

by Shellbot In reply to Unable to link two tables

Are you following the correct procedure for dragging your keys? ..(one to many, not many to one)

Collapse -

And this

by Shellbot In reply to Have a look at this

"Currently, the database is empty - no data yet except for a few lookup fields."
Could it be that because your table(s) is empty its not liking it?

Collapse -

And this

by eckardtk In reply to And this

Hallo Shellbot,
the UK forum link provided the solution. There was a BLANK record in the Participants table. I would never have thought of this.
Thank you for your help.

Related Discussions

Related Forums