Building Complex Relationships and a Paper Trail

[Robert T]

Hello:

I need help with a complex application one of my co-workers must build.
Hopefully I can explain it so that it makes sense.

In our agency, we need to track transactions so we can create a paper trail
of how funds were disbursed and distributed to various people. Although there
are numerous financial institutions involved, here’s a basic example.

1. Institution “A†writes a check to Person “B†for $90,000.
2. Person “B†keeps 1/2 of the money.
3. Person B gives person “C†$30,000 and Person “D†receives $15,000.
4. Person “D†in turn gives 1/3 of his share to person “Eâ€.

We need to create a paper trail that tracks all of the money from
Institution "A" to where it finally ended up with person “E†so we can prove
where the money originally came from.

In addition to tracking the money trail, we need to track all of the
individuals and build relationships. For example, Person "B" and "E" above
are involved in this money trail, but we also want to search to see if they
were involved in one of several hundred other transactions. So we also need
to build relationships to see which individuals are involved in various
transactions. Wheeeeeeeeeeeeeeew…….. I hope that’s clear.

I need some help getting started in designing the tables and building the
proper relationships. Any ideas would be greatly appreciated.

 

[mscertified]

First, let me say I have not thought about this too much but at first glance
it seems you need a table to hold the originator, this will have a primary
key of ID identifying the originator and original amount. This ID will then
be used to track all transfers involving that original amount.
Then it seems you need a Transaction table with TransferFrom column,
TransferTo column, Amount and Date/Time.
All persons or institutions that can transfer money or receive money need to
be stored in another table called Institutions
E.g. To transfer $100 from person A to Person B
TransactionID: autonumber
OriginatorID: OriginatorID
TransferFrom: InstitutionID1
TransferTo: InstitutionID2
Amount: $100
Date/Time:

-Dorian

 

[Robert T]

Hello "MScertified" wrote:

I don't know if that will do everything I need, however, at the very least,
it should jump start my thought process. You certainly came up with some
excellent ideas.

Thanks

 

[George Nicholson]

I'm thinking 2 tables (generally aircode, but queries were tested).

tblEntities (Persons or Institutions)
EntityID (PK)
...EntityName, Address, ContactInfo, etc

tblTransactions
TransID (PK)
TransAmount
TransDate
ApplyToTransID (FK on TransID, but can be Null)
PaidByID (FK on EntityID)
PaidToID (FK on EntityID)

For Original payments (Level1) ApplyTo would be Null.
For all other payments ApplyTo would refer to the preceding Transaction.
(Level2 thru Level-n-)

qryTransLevel1:
SELECT TransID, ApplyToID, TransID AS OriginTransID, TransDate, TransAmount,
PaidByID, PaidToID
FROM tblTransactions WHERE ApplyToID Is Null;

qryTransLevel2:
SELECT tblTransactions.TransID, tblTransactions.ApplyToID,
qryTransLevel1.OriginTransID, tblTransactions.TransDate,
tblTransactions.TransAmount, tblTransactions.PaidByID,
tblTransactions.PaidToID
FROM tblTransactions INNER JOIN qryTransLevel1 ON tblTransactions.ApplyToID
= qryTransLevel1.TransID;

(...Repeat qryTransLevel2 for Level3, Level4, etc., basing those queries on
tblTransactions and the 'next higher' level query. Note that OriginTransID
is being "carried down" throughout the query series so you can group all
related payments. Unfortunately you'd have to predetermine the number of
levels you are prepared to support, although adding more later is as simple
as adding an additional query - or writing the code to only have as many
queries as is necessary...)

qryTransUnionAll:
SELECT "Level1" AS TransLevel, TransID, ApplyToID, OriginTransID,
TransDate, TransAmount, PaidByID, PaidToID
FROM qryTransLevel1
UNION SELECT "Level2" AS TransLevel, TransID, ApplyToID, OriginTransID,
TransDate, TransAmount, PaidByID, PaidToID
FROM qryTransLevel2
(...additional UNION claues for each qryTransLevel-n- created above...)

Report Sorted on OriginTransID, TransLevel
Report Grouped on OriginTransID

Report would give you, for each Originating TransID, a list of the related
subpayments. Note that more than one payment can have the same ApplyToID, so
if Entity1 make payments to both EntityB and EntityC (and those Entities
also make multiple payments...) and the structure should still be able to
associate all those payments.

PaidBy or PaidTo can be independently queried at any time

HTH,

 

[Dale Fye]

This structure will also lend itself to a treeview control for visualizing
the relationships.

 

[Robert T]

George and Dale:

I was out sick, had to go to the doctor yesterday so that's why I didn't
respond. I just say your idea and it looks great, thanks so much.

Robert