Re: Append action query not working out.


Allen Browne wrote:
> Travis, could you just have one Client table, that contains all these kinds
> of entities, with a ClientTypeID field that identifies whether the entry is
> a person, trust, company, or SMSF?

No, not really.

> I suspect there will be enough common fields to do that. For example, you
> could have a MainName field that contains the surname of individuals, the
> company name of companies, and so forth.

I've got the database organised by client groups.

A "group" could equate to a "family", but sometimes it might be some
kind of syndicate.

A group consists of people, trusts, companies and self managed
superannuation funds.

The information for a person includes their personal details like names
and DOB, plus contact details, risk profile, health issues, financial
goals, tax file number, GST registration etc.

For a company I need only know the name of the company (one field, no
first name and surname stuff) and some basic legal stuff like who are
the directors and shareholders, tax file number information, GST etc.
No risk profile, no DOBs etc.

For a trust the information is similar to that of a company, but
instead of "directors" there is information on trustees, appointors and
guardians, instead of shareholders you've got beneficiaries. There is
also a field to identify the type of trust, e.g. "fixed",
"discretionary", "hybrid" and whether the trust has made a family trust
election.

For a self managed super fund the information is similar to that of a
trust, but has differences just as the trust information differs from
the company information.

Doing it all on one table would be very messy.

> If the field list is vastly different for each of the 4 types, then it may
> be necessary to create 4 related tables where you can enter the fields
> specific to the different types, but you can often get away without that.
> The interface would normally be a subform in Form view, and you show the
> appropriate one in AfterUpdate of ClientTypeID and in Form_Current. To the
> user, this doesn't look very different from one large form. If you are
> concerned that the user might not fill in the entry, you could use the
> AfterInsert event of the main form to generate the appropriate related
> record. (You would also need to respond appropriately if the user later
> changed the ClientTypeID.)

My major form is organised by group (Group ID) with four subforms
(table format) for people, companies, trusts and self managed super
funds.

Each of the subforms have a button "edit this client" (or "edit this
company" etc) which pops up another form with much more detailed
information on that client. I close that then I'm back at the Group,
where I can click on other people and related entities.
>
> In essence, what I'm suggesting is not very different from your Entity + 4,
> except that you put everything you can in the combined table, and only use
> the extra tables for the odd-ball fields.

I thought about that and initially I did have the tables organised that
way, but it didn't really work. About the only thing that's common to
all types of entity is the tax file number! There is no date of birth,
gender, health or risk profile information required for any of the
other entities, people don't have shareholders/beneficiaries and
directors/trustees.

While all entities can own assets, only a person can have life
insurance. etc

The forms contain quite detailed information, these allow the complete
description of a group's financial situation and investment
proclivities.

Any other suggestions?

Travis

.