Software

Ask something
Question Locked

Unable to link two tables

0 helpful
12 replies

by eckardtk

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

12 replies

by eckardtk

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.

by scott_heath

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

by eckardtk

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

by Former member

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.

by Former member

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.

by eckardtk

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

by eckardtk

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

by shellbot

figured it was something simple, because it seemed that you had eveything done right.

You should hand out a few thumbs for this thread... :)
The other lads deserve thumbs as well for eliminating any other potential problems!

This discussion is closed to new replies.