Relational database problem

[casheltown]

I'm trying to use Microsoft Access to produce a contacts database for a local charity. Unfortunately I can't get my head completely round relational databases. I understand the basics but can't solve a particular problem. Can anyone help?

I currently have four tables (Contact, Organisation, Children, SpecialNeeds) that need the following relationships:

• One Contact may have many Children;
• One Child may have many SpecialNeeds;
• One Organisation may have many Contacts;
• Some Contacts have no Organisation.

It is the last of these that's causing me the problem. We need to have individual contacts who may, or may not, be part of an organisation. Each time I connect the primary keys I have a problem if I try to enter a Contact without them being in an Organisation. Any thoughts (explained simply for an almost complete novice) most welcome.

[urtrivedi]

1 One Organisation may have many Contacts;

2 Some Contacts have no Organisation.

these 2 are conflicting needs, you need to decide first on this which is preferrable for you

and by the way what u mean by contacts, its just address or anything else

[casheltown]

Contacts is basic address information.

I understand that they are conflicting but need both options. The situation is that there are lots of contacts, some of them will be staff of partner organisations, many of them will just be individual members or supporters of the charity. Would this suggest I need two sets of tables, one ofor individuals and one for organisations, with no relationship between them? A slight problem arises because some contacts would then be on both lists.

[urtrivedi]

contact (contact_id (pk), contact_name) independent

organistion(org_id (pk), org_name) independent

contact_child(child_id (pk), contact_id (fk), child_name)
child_needs(child_id (fk,pk), child_need_no (pk), child_need_description)

contact_organisation (contact_id (pk,fk),org_id(pk,fk))

when u create form for contact, u can add sumform for organistaion under it

same way when u create for for organistaion , u can add subform for contact

[casheltown]

That's fantastic urtrivedi, I'll have a go at that and see how I get on. Many thanks for your help.

I'll leave the thread open for now in case there are other suggestions.

[casheltown]

Tried it but didn't work completely. The relationships I have are as in picture.

I can get the contact_child as a subform of the contacts form, which I need, but can't get the contacts form as a subform of the organisations form. It only seems to want to insert the linking table, contact_organisation_link. I've set both the fields in that table as primary keys. Is there a step I'm missing out?

[urtrivedi]

design looks fine to me.
try to insert record mananully in table without form, it allows or not?

Later try create new forms using wizard as follows
1) create contact-org link table grid type of form
2) create new contact form with above subform, link with contact id
3) create new org form with one above subform, link with org_id

[casheltown]

Still having problems. The Contact, Contact_child and Child_needs table linking seems to work ok with the subdatasheets cascading properly like so:

However, the Organisation, Contact tables link doesn't seem to work as it cascades (see below) but doesn't have any existing associated Contacts. If I enter a contact in the subdatasheet it appears as a new contact in the Contacts table.

When I try to add Organisation subform to Contact form, or reverse, it doesn't offer me ID fields that match.

[urtrivedi]

this looks like table view.

Have you created your own form and subforms

[casheltown]

I've tried creating forms and carrying out the steps you suggested above:

*design looks fine to me.
try to insert record mananully in table without form, it allows or not?

*Later try create new forms using wizard as follows
1) create contact-org link table grid type of form
2) create new contact form with above subform, link with contact id
3) create new org form with one above subform, link with org_id**

When I try step (3) I'm getting org_id on one side and contact_id on the other so can't link org_id with org_id.