Form Issues

[harryfraser]

Hi there,

I posted on here a while ago but cant find the thread. I'm having some issues with my database system. I think it is a problem with the relationships I have created and I cant seem to work out why it wont work. Incidently if someone can help me i'd be happy to provide an example of my database to show how it is supposed to work, if you can't understnd the description.

The system I have developed is essentially an account management system for universities and their associated contacts. The relationship is entirely one to many across the database and goes something like this:

One institution can have many contacts.

I am having an issue with a sub form of the contacts form. The subform is called comments and the relationship is again one to many. The nature of the relationship is that one contact can have many account comments. I have linked the tables together so that the contacts primary key is the comments foreign key.

The issue I am having is that it does not display the comments for that individual account, rather it shows comments for all contact accounts. How can I change this - it is probably very obvious but i'm struggling with working out how to do it...

Thanks

Harry

 

[boblarson]

Since I don't know your exact table structure I will have to give you a somewhat generic answer.

Let's say you have

Institution Table
InstitutionID - PK (Autonumber)
InstitutionName

Contacts Table
ContactID - PK (Autonumber)
FirstName
LastName

InstitutionContacts Table
InstitutionContactID - PK (Autonumber)
InstitutionID - FK (Long Integer)
ContactID - FK (Long Integer)

Comments Table
CommentID - PK (autonumber)
ContactID - FK (Long Integer)
CommentDate

So then you have the comments form as a subform on the Contacts form. The Master/Child links would need to be by ContactID. If the subform control (control that houses the subform on the parent form) has those links set it would only display the comments that go with that contact.