Re: Append action query not working out.

A trust is a "client" that consist of other clients, who could also be
clients in their won right, and could be members of other trusts, directors
of other companies, or members of other families.

The way I like to work with that kind of data is described in this article:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

What I'm suggesting is not that far from what you are doing anyway. Any
entity is going to have a name. Corporate entities could even have a
Birthdate and Deathdate (important in some scenarios, irrelevant in others).
There could be sub-types (M/F for individuals, fixed/discretionary/... for
trust). Things like Directors are actually related entities rather than
repeating fields in the core record.

If that's not useful for you, no worries. If the original question was how
to debug the append query, we can work on that if you prefer.

--
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.

"Travis" <travismorien@xxxxxxxxx> wrote in message
news:1133516613.320596.141980@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> 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


.