Re: Specifying record source in a form control based on selection

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Well, this would cause me a problem in the current design as Units is
related to Tenants, by UnitID as a foreign key in each Tenant record.
Having multiple instances of the Unit to specify Ownership shares would
require the Tenant-Units relationship to change.

However, since in this version of the database, as I do not need to
treat the "Partnership" as a discreet entity for accounting purposes
(which was required in one of the former databases) one of the options
I had considered was to show individual ownership records linked to the
Unit by share, but it seems I would still need a 3rd table
(OwnershipShares) to achieve this.

I was thinking something along the lines of:

tblOwners
OwnerID (primary key)

tblUnits
UnitID (primary key)

tblOwnershipShares
UnitID - foreign key to Units table
OwnerID - foreign key to Owners table
%Ownership

tblTenants
TenantID (primary key)
UnitID (foreign key to Units table)

with the primary key of tblOwnershipShares made up of the 2 fields
UnitID + OwnerID

Then I could use a subform which lists the individual ownership
interests in my Units form. Tenants would only be indirectly related to
Owners, by the Units table.

Is this a valid concept or will it just get me into more trouble?

Again, the help is much appreciated.

-Skylark

TonyT wrote:
Hi Skylark,

Could you not just have your owners table, and then the property table
linked to it, with the field percentage owned, that way John Smith occurs
just once in owners, and property table has 3 records, 2 for 123 Pleasant
Str. OwnerID & 50% for each owner and 1 record for 456 Shady Lane at 100%
linked to John Smith again.
You should be able to ascertain all the information you want that way, I
think.

TonyT..

"Skylark" wrote:

It has been bothering me, as well, and I am certainly open to
suggestions...I have been wracking my brain on this for over a week. I
have tried a number of alternatives, and I seem to wind up back with
the same type of problem. Perhaps if I explain, it would help.
Basically I have a table Owners, that has no dependencies - it is the
first thing that must be entered in the database when we receive a new
management contract. Just about every other table in the database (with
the exception of category tables, and selection lists) has some
dependency on the Owner table.

Here is an example:

We have a management agreement for the property at 123 Pleasant St.
John Smith has a 50% ownership share in the property, and Jane Jones
has a 50% share.

We have another management agreement for 456 Shady Ln., but this
property is solely owned by John Smith.

So, there would be a record for both John Smith, and Jane Jones in the
Owners table. However, there would be a record in the Partnership table
(little more than an ID, a friendly name), and a third table
Partnership members with a unique record for each partner (multiple
field primary key consisting of Owner ID, and PartnershipID).

In the Paradox database, there is a list box in the Units form, that
occurs before you reach the Owners lookup field. The list box has
choices of Single Owner or Partnership. Once that selection has been
made, when you enter the Owner lookup field in the Units form, you are
presented with a list of records from the Owners table or a list of
records from the Partnership table, respectively. Since I need to be
able to report on Units by their ownership interests, I need to
reproduce this functionality in the Access database as well.

Any thoughts are appreciated.

-Skylark

Marshall Barton wrote:
Well, your situation is a lot more complex than I thought.
Maybe you really do need all that, but there still something
about the way the other tables link to the partnerships
table that feels wrong. Think about this some more, you
might be digging yourself into a hole that will cause no end
of trouble later.
--
Marsh
MVP [MS Access]


Skylark wrote:

Thank you Tony, I will try that. The tip about the field names is good
advice.

@Marsh: As for the Partnerships table, I agree it adds complexity but
there is a reason. The database is actually quite extensive, with many
relationshps among many tables. I only described the portions directly
related to my problem. For reasons of general accounting (expenses,
profit, loss, etc.), tax statements, mailing addresses, and other
things that need to be addressed as a matter of business practice on a
Partnership level versus a single owner level, the relationship between
Owners and Partners was needed. I have also been a bit constrained by
having to import data from 2 other databases, one that was written in
Paradox 4, and another that was developed in Access 2000 which was
really a confusing mess of a single large table that repeated the same
bits of information over and over (without input masks or other data
restraints), it has given me fits. I am very much learning as I go.
Since the accounting functions are actually going to be handled by a
commercial accounting package, I can probably eventually abandon some
of the tables, and their corresponding relationships in future, but I
am proceeding with caution as some of the historical information is
necessary for many of the reports we want to run, and I don't want to
alter the tables or relationships until I have the existing design
functional, and tested. There is probably a more streamlined way to
establish the relationships, to the bare minimum of what is needed, and
I hope to do so.


skylark_on_siege@xxxxxxxxxxxxx wrote:

I have created a database to record information related to property
management. Ownership of a property may be by a single owner or a group
of owners (in a partnership or corp.)

I have the following tables:

tblUnits
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT 25
Owner; TEXT 10
tblOwners
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerships
Fields: RecNum (autonumber)
ID; TEXT, 10 (primary key)
Name; TEXT, 25

tblPartnerMembers
Fields: PartnershipID (lookup field to tblPartnerships ID)
OwnerID (lookup field to tblOwners ID)
PercentOwnership
(PartnershipID & OwnerID together make up the primary
key)

What I want to happen, is in my form frmUnits that is used to enter
information for each new property, to have a list or combo box called
cbxOwnershipType, that lets the user select "Single" or "Multiple", and
after the selection, will provide the appropriate record source in the
combo box for looking up the owner ID, which is bound to Units.Owner,
as either the Owners table or the Partnerships table.

I have searched the groups and other sources a good bit, and while I
have found a lot of programming discussions about updating controls
based on other controls, I have been unable to find an existing topic
that helps me solve this problem. I do not know VBA, and I am a novice
in SQL. I mainly depend on the user interface in Access 2003 to
construct what I need.

Marshall Barton wrote:
You definitly should rethink the table relationships.
Instead of thinking about single owners and partnership
owners, try thinking in terms of owner. An owner is an
owner, the percentage of ownership may vary, but still be an
owner.

OTTOMH, I suggest that table tblOwners include the
percentage field and a foreign key (RecNum) to
tblPartnerships (Null if not in a partnership). I don't see
how tblPartnerMembers does anything besides get in the way.



.

Relevant Pages

  • Re: Specifying record source in a form control based on selection
    ... by UnitID as a foreign key in each Tenant record. ... OwnerID (primary key) ... Owners, by the Units table. ... there would be a record in the Partnership table ...
    (microsoft.public.access.formscoding)
  • Re: Steamboat Inn - Trent Lock
    ... > pub has finally changed hands. ... > The new owners are the BW/Scottish & Newcastle ... > directly contribute - should indeed start this sort of partnership. ... but when a navigational body starts ...
    (uk.rec.waterways)
  • Re: Finding a factory (and its owners) in 17C Scotland
    ... owners were illiterate and had to rely on others to spell their name. ... the name did not evolve from a business partnership - it is said ...
    (soc.genealogy.britain)
  • Re: GNU Public Licences Revisited (again)
    ... help you comment more intelligently on joint ownership vs. ... German and English joint ownership laws as applied to copyrights ... ours or in a communist-oid legal system. ... to have the joint owners try to reach agreement without seeking a legal ...
    (comp.programming)
  • Re: The Reality on Junior
    ... owners and drivers are who I follow. ... It appears that this is a Cat fight with Dale as ... she ended up getting 0% ownership for him. ...
    (rec.autos.sport.nascar)