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