how to link 3 tables that seem to be table, subtable, subsubtable

[Catherine Jo Morgan]

This is for a database for a recreational tree climbing business. There are
different kinds of climbs offered, as well as occasional other services like
tree canopy research. So we have a table called Services.

My problem is that one kind of service has a variety of types. It's called
Introductory Climbs. Within that category, there are quite a few choices:
birthday party, family climb, festival climb, father-daughter climb, etc. I
can make a table for Introductory Climb Types, of course. But how do I link
this to the Services table?

Eventually of course I'll want a form made so that if Introductory Climb is
selected as the service, then a list of Introductory Climb Types opens so
the type can be selected.

Then if it's a birthday party, additional fields are required: the birthday
person, birthday person's gender, and birthday person's age. So there needs
to be a separate table for Birthday Parties with these fields.

How to relate these three tables? Services, IntroductoryClimbTypes, and
BirthdayParties?

TIA

 

[Kelvin]

Don't bother creating relationships. Just go ahead and make your tables.
Then when you are ready to create your forms make subforms for these tables,
then use code in the OnCurrent of the form and AfterUpdat event of the conbo
box to set the subform based on the choice made. You don't have to create
relationships to link data. Relationships are just a default setting.

Kelvin

 

[Dorian]

But the table relationships window is a wonderful design
tool and shows the structure of your database clearly.
Also, its necessary to use it to enforce referential
integrity which to my mind is essential in any database.

 

[John Vinson]

This is for a database for a recreational tree climbing business. There are
different kinds of climbs offered, as well as occasional other services like
tree canopy research. So we have a table called Services.

My problem is that one kind of service has a variety of types. It's called
Introductory Climbs. Within that category, there are quite a few choices:
birthday party, family climb, festival climb, father-daughter climb, etc. I
can make a table for Introductory Climb Types, of course. But how do I link
this to the Services table?

Eventually of course I'll want a form made so that if Introductory Climb is
selected as the service, then a list of Introductory Climb Types opens so
the type can be selected.

Then if it's a birthday party, additional fields are required: the birthday
person, birthday person's gender, and birthday person's age. So there needs
to be a separate table for Birthday Parties with these fields.

How to relate these three tables? Services, IntroductoryClimbTypes, and
BirthdayParties?

I may be going out on a limb here, but I have to rather emphatically
disagree with Kelvin. Relationships are NOT just a default setting!
They (at the least) maintain relational integrity, protecting you from
entering many kinds of invalid data; and they automatically create
indexes making your database work more efficiently (you can manually
create indexes too, but most of the common ones would be handled by
the relationships).

I'd see that you have at least the following entities (aside from the
obvious ones such as Customers and so on). A given Climb event should
have a field ClimbType, linked to a table of ClimbTypes, linking the
ClimbTypesID (or whatever you choose as the primary key) one to many
to the ClimbType field in the Climbs table. You'ld have another field
Level linked to a small table with values Introductory, Intermediate,
Advanced; I'd sort of expect that one could have a birthday party at
any of these levels, but if you want to restrict it to Introductory
climbs, you could include a Level field in the ClimbTypes table. The
combo box you use to choose the ClimbType could be based on a query
selecting the desired level, using a query criterion of

=Forms!yourdataentryform!cboClimbLevel

in the query.

The BirthdayParty table (and perhaps other specific kinds of climbs)
are a classic case of "subtyping" - every climb has some common
features such as the location, the date of climb, the guide I presume,
etc; but only some climbs need to deal with the birthday specific
fields. One way to handle this is to create a one-to-one relationship
between the Climbs table and the Birthdays table, linking on ClimbID.
You'ld only enter a record into Birthdays if it were that kind of a
climb; on your form this could just be in a subform, left blank for
other types of climb.

 

[Tim Ferguson]

You don't have to create
relationships to link data. Relationships are just a default setting.

I'm with John on this: the above must be the most succinct failure to
understand the point of a R database I have ever seen!

Relationships are the number #2 method of protecting your data from
becoming a pile of rubbish. Relationships are the way that you prevent
someone from deleting the birthdayparty record in the ClimbTypes table, and
orphaning all the birthday type of Parties that depend on it. Or creating a
ClimbType which belongs to a non-existent Service.

This really is fundamental stuff, and if you are having difficulty using
Access at this level, it might be a good idea to go back to Paradox or
something and do some reading about databases in the meantime.

Best wishes


Tim F

 

[Kelvin]

I have to retract my earlier statement. I did not mean to imply that
relationships are not useful. Let me rephrase my thinking.

I was just trying to say that since the poster was getting stuck
understanding how to create the relationship in the relationship window, to
ignore it at this point.

I apologize for opening a can of worms.

Kelvin

 

[Tim Ferguson]

I was just trying to say that since the poster was getting stuck
understanding how to create the relationship in the relationship
window, to ignore it at this point.

To me, it's like telling someone learning to drive their first car, not to
worry about steering if it's a bit too difficult...

B Wishes


Tim F

 

[Kelvin]

Besides referential integrity and seeing a layout of the tables, what are
the other uses for creating relationships in the relationship window. I
usually layout my database on paper before I start so to me that part of the
relationship window is not useful. I also normally don't allow referential
integrity to handle deleting data.

Don't get me wrong, understanding the relationship between data is
important, I just don't see the use of the relationship windows all that
important, except for referential integrity. I can see how relationships
are like steering a car, but to me the relationship window is like steering
a car traveling at 100 mph, do it if you can, but if you can't it doesn't
stop you from getting where you're going.

Kelvin

 

[Tim Ferguson]

I just don't see the use of the relationship windows all that
important, except for referential integrity.

I _think_ I know the answer to this question, but I'm going to ask it
anyway. What do _you_ use to create relationships with, if not the
relationships window?

Of course it is possible to do it all in DDL commands and even DAO for the
really flagellants out there -- but neither of those is exactly friendly.

B Wishes


Tim F

 

[Kelvin]

Tim,

I just use joins to define the relationships in queries and parent/child
links in forms and reports. Setting up my tabels with primary keys and
foreign keys identify my 1-to-many relationships. Many of the database I've
created have to track all entries so I use a VOID field instead of deleting
records. I also use command buttons to add records to subforms, which arn't
enabled until the main form is populated so referential integrity for adding
and deleting records isn't needed. My keys are set up as IDs which never
get changed so the updating in referential integrity isn't needed either.

I'm sure I haven't been programming as long as you, but I really don't see
much of a need for the relationship window.

Happy holidays.

Kelvin

 

[Tim Ferguson]

I really don't see
much of a need for the relationship window.

<sigh>


B Wishes


Tim F