Append action query not working out.

[travismorien]

I have four tables of different "entities". One table contains
information for "people", one for "trusts", one for "companies" and one
for "self managed super funds". Each type of entity has an autonumber
ID, "Person ID" "Trust ID" "Company ID" and "SMSF ID"

A "portfolio" table holds information about what shares, funds and
properties everyone owns. But because its organised by "PersonID" it
currently only can hold information for people, not trusts, companies
or SMSFs.

So, I've created another table which has three columns:

Entity_ID Entity_Type PTCS_ID (the last being "person or trust or
company or SMSF ID)

The Entity_ID is an autonumber, the second column specifies whether
it's a person, company, trust or SMSF and the third column is the
"Person ID" "Trust ID" "Company ID" or "SMSF ID"

With this table I can now reorganise my portfolio table to work on
Entity IDs instead of Person IDs.

Now the problem arises of how to update the Entity ID table every time
a new person, company, trust or SMSF is added.

The solution would be to write an append action query which is called
by the "After Insert" event.

The bit I'm banging my head on is how to design the query.

I basically want a query that returns the following "take the
Person/Company/Trust/SMSF ID of the record just created and the type of
entity and append this to the tblEntityIndex table."

The result would be that the tblEntityIndex table gets one extra record
with the Type and PTCS_ID of the latest person, company, trust or SMSF.

The query I've designed doesn't do that at all, in fact it appends the
Type and PTCS_ID of every person (or company, or trust, or SMSF,
depending on which query gets executed from the respective entity
subforms.)

Worse, it adds them three or four times.

For example:

There are six trusts in the Trust table, TrustIDs are 1,2,4,5,6,7
(Three was deleted)

But the records appended to tblEntityIndex are as follows:

EntityID Type SubtypeID
628 Trust 1
629 Trust 1
630 Trust 1
631 Trust 1
632 Trust 2
633 Trust 2
634 Trust 2
635 Trust 2
636 Trust 4
637 Trust 4
638 Trust 4
639 Trust 5
640 Trust 5
641 Trust 5
642 Trust 5
643 Trust 6
644 Trust 6
645 Trust 6
646 Trust 6
647 Trust 7
648 Trust 7
649 Trust 7

Your help in this regard would be greatly appreciated!

Travis

 

[Allen Browne]

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?

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.

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

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.

 

[Travis]

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

 

[Allen Browne]

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.

 

[Travis]

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.

Well if for no other reason than if I can resolve this one issue I
won't have to reorganise the rest of the database, I'd like to debug
the query.

I've played with the query a bit and I've gotten rid of the duplicates,
but this SQL...

INSERT INTO EntityIndex ( SubtypeID, Type )
SELECT Trusts.TrustID, "Trust" AS Expr1
FROM Trusts WHERE TrustID = (SELECT Max(TrustID) FROM trusts AS A
WHERE A.TrustID = Trusts.TrustID);

Still appends every single trust to the tblEntityIndex table. Six
trusts = six new entries.

I only want one new entry: the latest one. I can't figure out why my
"maximum" isn't working....

Travis

 

[Travis]

I think I've cracked it...

INSERT INTO EntityIndex ( SubtypeID, Type )
SELECT Trusts.TrustID, "Trust" AS Expr1
FROM Trusts WHERE TrustID = (SELECT Max(TrustID) FROM trusts);

This appears to do what it is supposed to, if I enter a new trust which
happens to have Trust ID 12 the EntityIndex table will gain a new
record

<Entity ID> Trust 12

Thanks for your advice, I'll certainly take into consideration your
information on designing relationships.

Travis

 

[Travis]

Moving along...

So the EntityIndex table doesn't refer to non-existent entities, I'd
need to do an action query to delete a record from the EntityIndex if I
delete that record from an entity table.

So if I delete Trust ID = 6 the query will look up an entry in
EntityIndex and delete the record where Type = Trust and SubtypeID =
the record I'm deleting.

A query based on MAX won't work in this case, as I may have added
entities since that entity was created.

And apart from the SQL, would I trigger this query with the "After Del
Confirm" event?

Travis

 

[Allen Browne]

You could use AfterDelConfirm, though you would need to use that in
combination with the Delete event since the record being deleted is no
longer available in AfterDelConfirm.

If this application could ever become an ADP, you may also need to know that
the order of events will be different:
http://support.microsoft.com/default.aspx?scid=kb;en-us;234866

An alternative would be to put cascading deletes from the main table to the
4 related tables. Then trap the attempt to delete the record, and delete
from the main table instead.

 

[Travis]

Actually Allen, although I've now resolved the index issues I've
decided to have a go with a single giant Entities table anyway, with an
EntityType field. There are probably pros and cons with both
approaches, I'll try it both ways and see which I like more.

I've got quite a bit of data in the people, companies, trusts and SMSFs
tables, so I was wondering if there was an easy way to merge two or
more tables apart from exporting them to Excel and combining them using
cut and paste?

Travis

 

[Allen Browne]

Good for you. Hope you find it a rewarding and worthwhile experience.
Naturally, there is no one right way to design a database. The goal is the
simplest possible structure that provides the best integrity and the
flexibility to cope with anything - three conflicting aims.

If you build a new table, you can use an Append query to populate it with
the data in another table. In query design view, it's Append on the Query
menu, and you can then map the relevant fields. Repeat for the other 3
tables.

 

[Travis]

Thanks Allen,

One of the reasons for adopting this multi table approach was because
it was easier to design the forms the way I want them. I would have a
main form able to select the group and a tab control with "people",
"companies", "trusts" and "SMSFs" pages and the subforms for each of
these on the pages.

My experimenting with a super-table based database shows that if I
arrange my forms the same way it won't work. I created a table which
had the fields "ID" (Autonum), "Group", "Type" and nine more fields
"Person Field 1" .. 3, "Company Field 1" .. 3 and "Trust Field 1" .. 3

I then did a form based on group, like my existing database, and a tab
control with "People", "Companies" and "Trusts" on the tab pages. I
then inserted a "Person" "Company" and "Trust" tabular subform on the
respective pages.

However, as I expected, entering two lines of data into each subform
populated the super-table with only two records, which contained
information in all of the various fields. I would want that to happen
if the second page was additional information about a particular
person, but in this case I don't want that.

Obviously I'll need some form of filter to be attached to the tab pages
so entering a new record in the companies form creates a new company
rather than adds company-specific data to a person.

The question is... how?

And just FMI, back on my old database, how would I be able to navigate
from a Person form to their portfolio, bearing in mind that clicking on
the "Edit this person's portfolio" button I've added to the person form
needs to take the user to a series of entries filed under Entity ID,
rather than PersonID. While I'm not developing that database any
further, I really would like to know how to do this anyway as I can see
other circumstances where I will need to pull up related forms via a
query like that.

And incidentally, to you offer paid consulting services for advice on
issues like this? I'm in Perth as well, and there are certain other
features I am planning for my software, like the ability to import an
Excel or CSV commission statement from various fund managers and have
the commissions credited to a client's account, which go well beyond
simple Access stuff and into the realms of full blown VBA programming.
I'm a fee for service financial planner so commissions actually present
a significant administrative problem for me since I need to keep track
of them and rebate them at an appropriate frequency. At some stage I'm
going to decide that my time is best spent on my clients rather than
continuing to learn the finer points of Office Application
development....

Travis

 

[Allen Browne]

Hey, Travis, 4 separate *forms* for entering the different kinds of entities
could be a really good idea. You can get exactly the layout you want, show
only the appropriate fields, and label them appropriately for the specific
type. But, the records end up in the same table, so the relationships to
other tables are dead easy to maintain also.

In answer to your question about how to filter each of the 4 forms (or tab
page subforms if you prefer), each would have a RecordSource query that
selects just the applicable ClientTypeID, e.g.:
SELECT tblClient.* FROM tblClient WHERE ClientTypeID = "SMSF" ORDER BY
tblClient.MainName;

In anwer to your FMI question, if do have 4 different forms for one table,
you do have to know the ClientTypeID so you can open the right form for the
kind of person. Very often, you can make that info available to yourself,
e.g. by including tblClient in the RecordSource for the form, or by adding a
hidden column for ClientTypeID to the combo where you select the client. For
the cases where it is not already visible, it's just a DLookup() to get the
ClientTypeID value so you know which form to open.

You're in Perth. Great. Yes, we do this for a crust as well. My email
address is spelled out in the sig below, or the phone number is on the
website.

Commissions can certainly get involved: tiered levels/rates, splits,
periods, etc.

 

[Travis]

Hey, Travis, 4 separate *forms* for entering the different kinds of entities
could be a really good idea. You can get exactly the layout you want, show
only the appropriate fields, and label them appropriately for the specific
type. But, the records end up in the same table, so the relationships to
other tables are dead easy to maintain also.

In answer to your question about how to filter each of the 4 forms (or tab
page subforms if you prefer), each would have a RecordSource query that
selects just the applicable ClientTypeID, e.g.:
SELECT tblClient.* FROM tblClient WHERE ClientTypeID = "SMSF" ORDER BY
tblClient.MainName;

Ok, that did the trick as far as eliminating the people from the
company form etc, but it doesn't automatically set the Entity Type to
"SMSF" or whatever.

So I guess the next bit is to do an action query. Similar sort of SQL
to what I had to use when creating my "EntityIndex" table in the "old"
database, but instead of an append I'm using an update.

Using the afterinsert event I'll need to call an update query to make
the client with the greatest number (e.g. the entity just assigned an
autonumber) type equal "SMSF" etc.

SQL:

UPDATE tblEntities SET tblEntities.EntityType = "SMSF"
WHERE (((tblEntities.EntityID)=(SELECT Max(EntityID) FROM
tblEntities)));

I assume there is no easier or more efficient way to do it?

And speaking of update queries, I'd still like to know if there is any
easy way of getting the ID of the record I just changed.

I think one instance where this would be useful include wanting to
select one entity (a person) as the primary contact and for checking
the primary contact box (yes/no in check box format) to trigger an
update query to set all other people in the group to primary contact
status "No".

Another would be when setting the postal address. A button control or
check box "postal address is the same as the street address" could
trigger an update of postal in this way.

Also, when editing the details of other people in the group I would
want to have a button for "contact details same as primary contact in
group".

In anwer to your FMI question, if do have 4 different forms for one table,
you do have to know the ClientTypeID so you can open the right form for the
kind of person. Very often, you can make that info available to yourself,
e.g. by including tblClient in the RecordSource for the form, or by adding a
hidden column for ClientTypeID to the combo where you select the client. For
the cases where it is not already visible, it's just a DLookup() to get the
ClientTypeID value so you know which form to open.
Thanks.

You're in Perth. Great. Yes, we do this for a crust as well. My email
address is spelled out in the sig below, or the phone number is on the
website.

I'll bookmark your site and be in touch some time if I need it.

Commissions can certainly get involved: tiered levels/rates, splits,
periods, etc.

Yep, but in my case I have my own Australian Financial Services License
(my web site is www.travismorien.com) and all I'm interested in is
adding them all up, subtracting off any fees for service, and running
reports which tell me how much I've got to pay into which bank
accounts. The reports themselves probably won't be too great a
problem, its more the fact that I want to import commission
spreadsheets.

Some give the commission statements in paper form only, so I've got to
create a convenient form for manually entering commissions. Others
give it in Excel or CSV format and I've got to set up a query that can
parse the spreadsheet, identify the client codes and match them up with
my own EntityIDs, identify the commissions being paid and whether the
figures quoted include GST or whether GST is added on at the end, and
then file these in a commissions table. And when I've done the Excel
spreadsheets, I'll then need to figure out how to parse the same
information from PDF and Word documents or even web sites, since some
statements arrive in these formats.

When I've mastered that, I'll apply similar techniques to importing
transactions from transaction statements so portfolios can be
maintained automatically.

I wrote my first VBA function only two days ago (and it was nothing
special, just an openquery), and that at the moment is just about the
full extent of my VBA skills. Obviously I've got a long way to go
before I can finish my system...

Travis
www.travismorien.com

 

[Allen Browne]

You have separate forms for entering the 4 times, and you need this one to
default to "SMSF" when you create a new record. That's just a matter of
opening the form in design view, selecting the ClientTypeID field, and
setting its Default Value property to "SMSF" or whatever. (The text box can
be hidden, and it still works.)

In Form_AfterInsert or Form_AfterUpdate (or even Form_BeforeUpdate with
Access (JET) tables), you can get the autonumber from the form like any
other control. It's just:
Forms!Form1!EntityID
If you use Access 97, you need to be aware that this can give you the wrong
number if you have hundreds of records and have deleted one in that session:
http://allenbrowne.com/bug-04.html

Love the idea of the investment FAQ on your website. Providing info like
that is very much in the spirit of these newsgroups.

 

[Travis]

You have separate forms for entering the 4 times, and you need this one to
default to "SMSF" when you create a new record. That's just a matter of
opening the form in design view, selecting the ClientTypeID field, and
setting its Default Value property to "SMSF" or whatever. (The text box can
be hidden, and it still works.)

Ah, even easier!

In Form_AfterInsert or Form_AfterUpdate (or even Form_BeforeUpdate with
Access (JET) tables), you can get the autonumber from the form like any
other control. It's just:
Forms!Form1!EntityID

I haven't seen this one before, how do I use it?

If you use Access 97, you need to be aware that this can give you the wrong
number if you have hundreds of records and have deleted one in that session:
http://allenbrowne.com/bug-04.html

I use Access 2002 and will need it to run on 2000 to be compatible with
some of the other computers in my office. I think I should be ok.

Love the idea of the investment FAQ on your website. Providing info like
that is very much in the spirit of these newsgroups.

You and I occupy similar roles in our respective fields. If you go to
aus.invest or the Vanguard Diehards forum (www.diehards.org) and take
on the role of hapless newbie by asking a financial planning or
investment related question there is a good chance I'll be the one
answering it. I'll answer just about any FP question in the newsgroup
short of a request for a full financial plan via usenet.

Travis

 

[Allen Browne]

This example shows how to automatically assign your default newsletters to a
client at the time when a new client is entered into the database. It reads
the new EntityID (primary key) from the form, and assigns it as the foreign
key in the related table:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ClientMail ( ClientID, MailTypeID ) SELECT " &
Me.EntityID & " AS Expr1, MailType.MailTypeID FROM MailType WHERE
MailType.IsDefault = True;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

All the best.