Need advice on table structure and on creating subforms

[CAD Fiend]

Hello,

I have a land development project that has a many-to-many relationship.
I have ONE question regarding table structures, and ONE question on how
to make a Form with two Subforms, below.
TABLE STRUCTURE QUESTION:

There are two parts to this preface.
1) There are many properties, that can be owned by many owners, one
property owned by one owner, or one property owned by many owners, or
many properties owned by one owner. So, for example, some of the
property ownership descriptions are Separate Property (One Owner),
Separate Property (Multiple Owners), Community Property (Husband and
Wife), etc. So one of the fields that will be required for the table is
going to be OwnerType.

2) In addition, this project involves percentage of ownership both on
the SURFACE and BELOW the surface (aka Minerals). So, there could be a
scenario where there are two owners that own the surface (50%-50%), but
only ONE of the owners owns the BELOW surface (100%). There will have
to be two fields, SurfacePercent and MineralsPercent.

Currently, I have three tables tblOwner, tblProperty, and
lnktblOwnership. tblOwner has Name, Address, and SSN fields.
tblProperty has PropAddress, Township, Range, and AssessmentNo.
lnktblOwnership is a junction table. For sake of brevity, I have left
out the other 40+ remaining fields.

My question: Do I put the OwnerType field in the tblProperty (because
the type of ownership is unique to that particular address) or should it
be in the lnktblOwnership? And what about the Surface and
MineralPercent fields? Should those go in the lnktblOwnership?

FORM QUESTION:

I know how to create ONE form for ONE table. But I'd like to create a
form that will allow me to reference all 3 of the above tables as a
tabbed sub form. The 2 tabs are to be Owner Info, and Property Info.
I'd like the parent form to have fields of Name, Address, AssessmentNo
change as the user is clicking on the tabs of the sub forms to reflect
whatever position they are in the database. I just don't know how to
begin. Do I start with a form in design view? Or do I do one in the
Wizard then attach the TabControl and then SubForm/SubReport control?
I've tried both together and can't figure it out. If anyone can point
me to a "primer" for Sub forms I also gladly appreciate that.

Thanks in advance.

Phil.

[Larry Linson]

By process of elimination, both the owner type and the surface/mineral
information have to go in the junction table. Neither applies to all owners
of the property, so can't go in the property table; nor to all properties of
the owner, so can't go in the owner table.

The junction table most certainly does not have to be limited to the foreign
keys that link the many-to-many tables -- it is the logical place
appropriate for holding any information that is unique to that particular
linkage of the records in the many-to-many relationship.

Larry Linson
Microsoft Access MVP

"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...

Hello,

I have a land development project that has a many-to-many relationship.
I have ONE question regarding table structures, and ONE question on how
to make a Form with two Subforms, below.
TABLE STRUCTURE QUESTION:

There are two parts to this preface.
1) There are many properties, that can be owned by many owners, one
property owned by one owner, or one property owned by many owners, or
many properties owned by one owner. So, for example, some of the
property ownership descriptions are Separate Property (One Owner),
Separate Property (Multiple Owners), Community Property (Husband and
Wife), etc. So one of the fields that will be required for the table is
going to be OwnerType.

2) In addition, this project involves percentage of ownership both on
the SURFACE and BELOW the surface (aka Minerals). So, there could be a
scenario where there are two owners that own the surface (50%-50%), but
only ONE of the owners owns the BELOW surface (100%). There will have
to be two fields, SurfacePercent and MineralsPercent.

Currently, I have three tables tblOwner, tblProperty, and
lnktblOwnership. tblOwner has Name, Address, and SSN fields.
tblProperty has PropAddress, Township, Range, and AssessmentNo.
lnktblOwnership is a junction table. For sake of brevity, I have left
out the other 40+ remaining fields.

My question: Do I put the OwnerType field in the tblProperty (because
the type of ownership is unique to that particular address) or should it
be in the lnktblOwnership? And what about the Surface and
MineralPercent fields? Should those go in the lnktblOwnership?

FORM QUESTION:

I know how to create ONE form for ONE table. But I'd like to create a
form that will allow me to reference all 3 of the above tables as a
tabbed sub form. The 2 tabs are to be Owner Info, and Property Info.
I'd like the parent form to have fields of Name, Address, AssessmentNo
change as the user is clicking on the tabs of the sub forms to reflect
whatever position they are in the database. I just don't know how to
begin. Do I start with a form in design view? Or do I do one in the
Wizard then attach the TabControl and then SubForm/SubReport control?
I've tried both together and can't figure it out. If anyone can point
me to a "primer" for Sub forms I also gladly appreciate that.

Thanks in advance.

Phil.

[CAD Fiend]

Thanks for your reply. I forgot to mention one thing, though. It turns out
after doing some checking, it IS possible to have ONE AssessmentNo with several
Township and Range values. Would this mean that I should make a junction table
to accomodate that relationship as well?

Larry Linson wrote:

By process of elimination, both the owner type and the surface/mineral
information have to go in the junction table. Neither applies to all owners
of the property, so can't go in the property table; nor to all properties of
the owner, so can't go in the owner table.

The junction table most certainly does not have to be limited to the foreign
keys that link the many-to-many tables -- it is the logical place
appropriate for holding any information that is unique to that particular
linkage of the records in the many-to-many relationship.

Larry Linson
Microsoft Access MVP

"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...

Hello,

I have a land development project that has a many-to-many relationship.
I have ONE question regarding table structures, and ONE question on how
to make a Form with two Subforms, below.
TABLE STRUCTURE QUESTION:

There are two parts to this preface.
1) There are many properties, that can be owned by many owners, one
property owned by one owner, or one property owned by many owners, or
many properties owned by one owner. So, for example, some of the
property ownership descriptions are Separate Property (One Owner),
Separate Property (Multiple Owners), Community Property (Husband and
Wife), etc. So one of the fields that will be required for the table is
going to be OwnerType.

2) In addition, this project involves percentage of ownership both on
the SURFACE and BELOW the surface (aka Minerals). So, there could be a
scenario where there are two owners that own the surface (50%-50%), but
only ONE of the owners owns the BELOW surface (100%). There will have
to be two fields, SurfacePercent and MineralsPercent.

Currently, I have three tables tblOwner, tblProperty, and
lnktblOwnership. tblOwner has Name, Address, and SSN fields.
tblProperty has PropAddress, Township, Range, and AssessmentNo.
lnktblOwnership is a junction table. For sake of brevity, I have left
out the other 40+ remaining fields.

My question: Do I put the OwnerType field in the tblProperty (because
the type of ownership is unique to that particular address) or should it
be in the lnktblOwnership? And what about the Surface and
MineralPercent fields? Should those go in the lnktblOwnership?

FORM QUESTION:

I know how to create ONE form for ONE table. But I'd like to create a
form that will allow me to reference all 3 of the above tables as a
tabbed sub form. The 2 tabs are to be Owner Info, and Property Info.
I'd like the parent form to have fields of Name, Address, AssessmentNo
change as the user is clicking on the tabs of the sub forms to reflect
whatever position they are in the database. I just don't know how to
begin. Do I start with a form in design view? Or do I do one in the
Wizard then attach the TabControl and then SubForm/SubReport control?
I've tried both together and can't figure it out. If anyone can point
me to a "primer" for Sub forms I also gladly appreciate that.

Thanks in advance.

Phil.

[Larry Linson]

If there are no properties which have multiple AssessmentNo.s, that could be
a one to many relationship, with the Property having a foreign key to the
Assessment table (which, I believe, had not been mentioned earlier). That
is, one Assessment, many Properties (though one fulfills this definition of
"many", as well as two or more)

Larry Linson
Microsoft Access MVP

"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...

Thanks for your reply. I forgot to mention one thing, though. It turns
out
after doing some checking, it IS possible to have ONE AssessmentNo with
several
Township and Range values. Would this mean that I should make a junction
table
to accomodate that relationship as well?

Larry Linson wrote:

By process of elimination, both the owner type and the surface/mineral
information have to go in the junction table. Neither applies to all
owners
of the property, so can't go in the property table; nor to all properties
of
the owner, so can't go in the owner table.

The junction table most certainly does not have to be limited to the
foreign
keys that link the many-to-many tables -- it is the logical place
appropriate for holding any information that is unique to that particular
linkage of the records in the many-to-many relationship.

Larry Linson
Microsoft Access MVP

"CAD Fiend" <pe******************@cox.net> wrote in message
news:42***************@cox.net...

> Hello,
>
> I have a land development project that has a many-to-many relationship.
> I have ONE question regarding table structures, and ONE question on how
> to make a Form with two Subforms, below.
>
>
> TABLE STRUCTURE QUESTION:
>
> There are two parts to this preface.
> 1) There are many properties, that can be owned by many owners, one
> property owned by one owner, or one property owned by many owners, or
> many properties owned by one owner. So, for example, some of the
> property ownership descriptions are Separate Property (One Owner),
> Separate Property (Multiple Owners), Community Property (Husband and
> Wife), etc. So one of the fields that will be required for the table is
> going to be OwnerType.
>
> 2) In addition, this project involves percentage of ownership both on
> the SURFACE and BELOW the surface (aka Minerals). So, there could be
> a
> scenario where there are two owners that own the surface (50%-50%), but
> only ONE of the owners owns the BELOW surface (100%). There will have
> to be two fields, SurfacePercent and MineralsPercent.
>
> Currently, I have three tables tblOwner, tblProperty, and
> lnktblOwnership. tblOwner has Name, Address, and SSN fields.
> tblProperty has PropAddress, Township, Range, and AssessmentNo.
> lnktblOwnership is a junction table. For sake of brevity, I have left
> out the other 40+ remaining fields.
>
> My question: Do I put the OwnerType field in the tblProperty (because
> the type of ownership is unique to that particular address) or should
> it
> be in the lnktblOwnership? And what about the Surface and
> MineralPercent fields? Should those go in the lnktblOwnership?
>
> FORM QUESTION:
>
> I know how to create ONE form for ONE table. But I'd like to create a
> form that will allow me to reference all 3 of the above tables as a
> tabbed sub form. The 2 tabs are to be Owner Info, and Property Info.
> I'd like the parent form to have fields of Name, Address, AssessmentNo
> change as the user is clicking on the tabs of the sub forms to reflect
> whatever position they are in the database. I just don't know how to
> begin. Do I start with a form in design view? Or do I do one in the
> Wizard then attach the TabControl and then SubForm/SubReport control?
> I've tried both together and can't figure it out. If anyone can point
> me to a "primer" for Sub forms I also gladly appreciate that.
>
> Thanks in advance.
>
> Phil.
>