Question

Locked

Unable to link two tables

By eckardtk ·
Hallo!
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.

11 total posts (Page 1 of 2)   01 | 02   Next
| 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: http://support.microsoft.com/kb/112111

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)

Enjoy,
Scott

http://scottisageek.spaces.live.com

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?

Regards,
Scott

http://scottisageek.spaces.live.com

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.
Alex

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.

http://free.hostultra.com/~gs2088/untitled.jpg

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

Hallo,
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

http://support.microsoft.com/kb/113494

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

http://www.access-programmers.co.uk/forums/archive/index.php/t-151082.html

"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.
Alex

Back to Software Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums