Relationship headache

[Iona]

Greetings to the gurus.

If I could get some advice, it would be massively appreciated.

I am near completion on a membership db in access2003. I have a sinking
feeling I may need a redesign at the table level. I have posted on another
website but not much luck, and I can't seem to find any related previous
postings anywhere.

Here's the situation. Standard contact inputs in tblcontacts. A routing table
for the 4 unique membertypes. Related to the routing table are 4 unique
tables with membertype details. The idea being that on a main form, the user
inputs the contact info, selects a member type, and then inputs the relevant
membertype details in a subform. I have created the main form and can import
the correct membertypedetails subforms through a dropdown box. No problems
there.

The problem is that, I can't seem to associate individual contact records
with their following unique membertypes. I can't seem to relate tblcontacts
with the appropriate unique membertype table at the relationships window
stage. Where can I set this relationship up? Is it possible to set up a
relationship at the form/subform stage? ( perhaps the contactsID would be
understood in the membertype subform?)

Please please please, any help from the Access crew?

 

[mnature]

Here's the situation. Standard contact inputs in tblcontacts. A routing table

for the 4 unique membertypes. Related to the routing table are 4 unique
tables with membertype details. The idea being that on a main form, the user
inputs the contact info, selects a member type, and then inputs the relevant
membertype details in a subform. I have created the main form and can import
the correct membertypedetails subforms through a dropdown box. No problems
there.

tbl_Contacts
ContactID (PK)
ContactInfo (shorthand for all the details you will need for contacts)
MemberTypeID (FK) (to tell you what type of member this is)

tbl_MemberTypes
MemberTypeID (PK)
MemberTypeInfo (this will be the names of the member types)

Open the relationships window, and make a relationship between
MemberTypeID's in MemberTypes and Contacts tables.

Put enough fields into your Contact table for any information you need about
a contact, no matter what member type they end up being.

Create queries that filter your contacts into all the different member types
that you define (use one query for each type). Each query should contain
just the information fields that you want to see for that member type.

Create forms based on each discrete query. The form will contain just the
information fields that you want to see for that member type, which will have
been chosen in the query that the form is based on.

 

[Iona via AccessMonster.com]

Thanks mnature..

Managed to get around my original problem, everything should be ticking over
nicely. Appreciate the input.

Here's the situation. Standard contact inputs in tblcontacts. A routing table
for the 4 unique membertypes. Related to the routing table are 4 unique

[quoted text clipped - 3 lines]

the correct membertypedetails subforms through a dropdown box. No problems
there.

tbl_Contacts
ContactID (PK)
ContactInfo (shorthand for all the details you will need for contacts)
MemberTypeID (FK) (to tell you what type of member this is)

tbl_MemberTypes
MemberTypeID (PK)
MemberTypeInfo (this will be the names of the member types)

Open the relationships window, and make a relationship between
MemberTypeID's in MemberTypes and Contacts tables.

Put enough fields into your Contact table for any information you need about
a contact, no matter what member type they end up being.

Create queries that filter your contacts into all the different member types
that you define (use one query for each type). Each query should contain
just the information fields that you want to see for that member type.

Create forms based on each discrete query. The form will contain just the
information fields that you want to see for that member type, which will have
been chosen in the query that the form is based on.