Keeping track of jointly owned assets

[travismorien]

I hope there is an easy way to do this...

I'm doing a database which keeps track of things that people or
entities (entities = trusts and companies) own, among other things.

When assets are owned by individuals, its easy. I have a table listing
the various assets, a table listing the various people and entities and
a third table with the IDs of the people/entities and the IDs of the
assets they own.

But I'm not sure how to go about recording an asset which is jointly
owned or owned tenants in common.

I have a column of percentage ownership, its easy to put two
individuals down as owning an asset in 50% shares. A listing of the
assets owned by the group will then show the asset twice with 50%
shares, I was wondering how I might be able to structure the query so
instead of getting e.g.

"$100,000 property at 2 Sesame Street" 50% owned by Ernie as joint
tenants
$100,000 property at 2 Sesame Street" 50% owned by Bert as joint
tenants
Total assets: $100,000"

I would instead get:

"$100,000 property at 2 Sesame Street" 50/50% owned by Ernie and Bert
as joint tenants
Total assets: $100,000"

Thanks...

Travis

[Tom van Stiphout]

On 5 Dec 2005 08:36:56 -0800, tr**********@yahoo.com wrote:

Sounds like a classic many-to-many situation, so you'll have to
redesign your tables.
tblPersons
PersonID (PK)
PersonName

tblAssets
AssetID (PK)
AssetName
AssetValue

tblOwnership
PersonID (PK)
AssetID (PK)
PercentOwnership

-Tom.

I hope there is an easy way to do this...

I'm doing a database which keeps track of things that people or
entities (entities = trusts and companies) own, among other things.

When assets are owned by individuals, its easy. I have a table listing
the various assets, a table listing the various people and entities and
a third table with the IDs of the people/entities and the IDs of the
assets they own.

But I'm not sure how to go about recording an asset which is jointly
owned or owned tenants in common.

I have a column of percentage ownership, its easy to put two
individuals down as owning an asset in 50% shares. A listing of the
assets owned by the group will then show the asset twice with 50%
shares, I was wondering how I might be able to structure the query so
instead of getting e.g.

"$100,000 property at 2 Sesame Street" 50% owned by Ernie as joint
tenants
$100,000 property at 2 Sesame Street" 50% owned by Bert as joint
tenants
Total assets: $100,000"

I would instead get:

"$100,000 property at 2 Sesame Street" 50/50% owned by Ernie and Bert
as joint tenants
Total assets: $100,000"

Thanks...

Travis

[travismorien]

Hi Tom,

My tables are in fact already set out in the manner which you describe.
I'm just not sure how to get access to report ownership as "joint
ownership" rather than reporting it twice with two 50% owners.

Its a query thing...

Travis

[Tom van Stiphout]

On 5 Dec 2005 18:19:26 -0800, tr**********@yahoo.com wrote:

OK, you wanted:
"$100,000 property at 2 Sesame Street" 50/50% owned by Ernie and Bert
as joint tenants
Total assets: $100,000"
If you literally want this output from a query, that's rather tricky
because of the concatenation of the two (or N) Ownership records. I
would probably do that using a custom function. The query might look
like this:
select AssetValue, AssetAddress, OwnersConcat(AssetID) from tblAssets

The OwnersConcat function:
public function OwnersConcat(byval lngAssetID as Long) as String
dim db as dao.database
dim rs as dao.recordset
dim sql as string
dim strResult as string
set db=currentdb()
'sql to get the list of owners for this AssetID
sql="select PersonName, PercentOwnership from tblAssets inner join
tblOwnership on tblAssets.AssetID = tblOwnership.AssetID where
tblAssets.AssetID=" & lngAssetID
set rs = db.openrecordset(sql, dbOpenSnapshot, dbForwardOnly)
'Alternatively use a stored parameter query.
while not rs.eof
strResult = strResult & rs!PersonName & " (" & rs!PercentOwnership &
"%), "
rs.MoveNext
wend
rs.close
set rs=nothing
set db=nothing
strResult=left$(strResult, len(strResult)-2) 'strip trailing ", "
OwnersConcat = strResult
end function

-Tom.

Hi Tom,

My tables are in fact already set out in the manner which you describe.
I'm just not sure how to get access to report ownership as "joint
ownership" rather than reporting it twice with two 50% owners.

Its a query thing...

Travis

[travismorien]

Tom van Stiphout wrote:

On 5 Dec 2005 18:19:26 -0800, tr**********@yahoo.com wrote:

OK, you wanted:
"$100,000 property at 2 Sesame Street" 50/50% owned by Ernie and Bert
as joint tenants
Total assets: $100,000"
If you literally want this output from a query, that's rather tricky
because of the concatenation of the two (or N) Ownership records. I
would probably do that using a custom function. The query might look
like this:

Thanks for your help. The output I want will actually be like this:

Family Group: Jack and Jill Smith : the name I have given the
group, could also be "The Smith Family"

The Smith Family consists of Jack, Jill, the Hill Water Company Pty Ltd
and the Smith Family Trust.

Jack owns a Toyota worth $10,000, Jill has a Ford worth $12,000. They
own a house together, worth $200,000, their family trust owns a factory
worth $1,000,000 and their business (The Hill Water Company Pty Ltd)
owns $50,000 worth of equipment for drilling wells.
I want to create a table which summarises their assets as follows:

<asset> <value> <owner>
Car (Toyota) $10,000 Jack
Car (Ford) $12,000 Jill
House $200,000 Jack and Jill as 50/50% joint tenants
Factory $1,000,000 Smith Family Trust
Well drilling equipment $50,000 The Hill Water Company Pty Ltd

In other words, a listing of all assets owned by all people in the
group. If it is 100% owned by a single person or entity, just name the
person or entity as owner. If it is owned in some other percentage by
two named entities, state <EntityID> "and" <other EntityID> "as"
<percent owned by entity> "/" <percent owned by other entity> <joint
tenants or tenants in common>.

Travis

[travismorien]

And I'll add that sometimes assets are owned fractionally but the rest
is owned by someone I'm not interested in. e.g. 30% share in a farm,
the other 70% is owned by somebody else.

So in that case...

Farm $1,000,000 Jill 30%

Net worth = $300,000

Travis

[Allen Browne]

Travis, if you were to use a structure like the one we were discussing
before, like the relationships diagram in this page:
http://allenbrowne.com/AppHuman.html
you could add a Percentage field to tblGroupClient to indicate that a client
has an xx% share of the group's assets.

You also add an Asset table, with a ClientID (foreign key to
tblClient.ClientID). In this structure, a "client" can be an individual
(person) or a corporate entity (trust, partnership, ...), so it is *dead*
simple to enter assets that are owned by persons or corporate entities all
in the one Asset table.

You can then query the assets of the individual client, and UNION that with
the assets of any corporate client that the individual client is a member
of, to get a listing of the individual's immediate and derived assets.

The UNION query could then become the RecordSource for report you are asking
for. Your original request in this thread was for a shared and deduplicated
list, so the report would group by AssetID, and you would need a function
call to generate the string that contatenates the individuals who share-own
the asset into a single string. This kind of thing:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<tr**********@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

And I'll add that sometimes assets are owned fractionally but the rest
is owned by someone I'm not interested in. e.g. 30% share in a farm,
the other 70% is owned by somebody else.

So in that case...

Farm $1,000,000 Jill 30%

Net worth = $300,000

Travis