Linking tables

[spiggo89]

I have four tables, the main one is like so:
table Person:
PersonID
Firstname
Address
Type ["Host", "Organizer", "Sponsor"]

the other 3 are like this:
table: Hosts
HostID
PersonID

table Organizers:
OrganizerID
PersonID

table Sponsors:
SponsorID
PersonID
Cash
Description

Each person can only be either a Host, Sponsor, or Organizer.
I have established the proper relationships in Access, but my problem is if i make a form to create Hosts say, it will create a person of type host, but it will not make an entry into the Hosts table linking the person's id. I presume this is because it doesn't know that the type field specifys which subclass the person is.

My question is how to get around this problem, can I somehow tell it that type indicates what other table to use, or can I manually insert them? (Since I have an individual form for each subclass)

 

[miltiadis21]

Have you linked them?
Select the subform and Set into the data tab property
These Values
LinkMasterFields PersonId
LinkChildFields PersonId

Hope this yes

 

[spiggo89]

I am not sure where to find these options, but I think I have set up the relationships correctly. I have uploaded an example of my database here: http://download.yousendit.com/6839E9A1131E5257

The form in there will add people to the persons table as Hosts but it doesn't also insert an entry into the Hosts table with the newly created PersonID, which is what I am trying to do.

 

[miltiadis21]

here you go You need to study normalization more or see other databases

 

[spiggo89]

yes but that only works because sponsor is the only type that has parameters, the other ones dont and so access wont create them for me because i dont enter anything.

 

[miltiadis21]

yes but that only works because sponsor is the only type that has parameters, the other ones dont and so access wont create them for me because i dont enter anything.

You didn't had any parameters in Your tables(organizer, Host) only the id's linked from the first place
If you want you can rephrase your question

 

[spiggo89]

EDIT: I have figured a way around this, but now i have a new problem.

I have a table called 'Location' like this:
LocationID
Name (Text)
Capacity (Number)
Indoor (Yes/No)

locations can be indoor or outdoor, for indoor I need to record whether there is internet or not, and for outdoor I need to record the size in metres squared. How can I make the form change to show indoor or outdoor table depending on what the user has selected in the yes/no field?

 

[miltiadis21]

try to avoid boolean values.Create a Frame insert a yes and no field bound to your field
then If Frame.Value = 1 Then
me.indoor.visible=true
Else
Me.indoor.Visible = False
me.outdoor.visible= true
End If
Something like this also make your lbls or text boxes are not visible