Adding last record to another table with Append Query

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

[Allen Browne]

Answered in microsoft.public.access.queries