Linking two records in same table together

[primagic]

Was wondering what would be the best way to link to records in the same table together?

E.g. Have a client database and a client has a single account and also a joint account with his partner. So, I want to link the join account to the single account and vice versa

 

[fneily]

Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Can you post your table structure? It sounds like it's not normalized. Maybe a many-to-many relationship? So:

tblClients
ClientID
Firstname
LastName
etc.

tblAccounts
AccountID
AccountInfo fields

tblClientAcct
CAID
ClientID
AccountID
Common fields

 

[JoeAtWork]

I would second Fneily here, the relationship is properly between the Client and Accounts, not between Accounts and Accounts.

That's not to say there is anything wrong with self-joins in tables. The classic example is an Employees table that has a SupervisorID field that is a foreign key to the Employees.EmployeeID field. But for your requirements a self-join is not the proper relationship.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[primagic]

Table structure is

tblAccounts
-----------

AccountID
AccountType - Single, Joint, Company
ClientName
ClientAdddress
.....

All the client information is held in the tblAccounts. The AccountTypes is held in another tblAccountTypes.

So a client may have 2 entries in the tblAccounts, one for their single account and one for their joint account so this is where I want to link them.

I was thinking I could create a drop down list of all the clients on the database on a form and they select which clients link to that record.

Thanks for your advice everyone

 

[PHV]

So a client may have 2 entries in the tblAccounts
I.e. 2 ClientName, 2 ClientAdddress ???

 

[primagic]

Correct. For e.g.

Account 1 - Single
---------
Mr A Smith
12 Xyz Lane
Xyz

will also have a joint account

Account 2 - Joint
-----------------
Mr A Smith - Primary Account Holder
12 Xyz Lane
Xyz

Mrs B Smith - Secondary Account Holder
12 Xyz Lane
Xyz

So when I look up Mr A Smith I want to be able to see that he also has a joint account with account 2.

In the tblAccounts I have PrimaryName, PrimaryAddress and so on for the primary account holder but also in the same table I have secondaryname, secondaryaddress and so on.

So if its a joint account both primary and secondary fields are completed

 

[PHV]

Did you make sense with this article ?

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

The basic rule of relational databases - don't keep the same information in more than one place.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[SeeThru]

One issue is with this is that if a customer changed thier address, you have to change thier address at every individual account - you cannot change thier address in one place for all accounts.

If you do still need to do this, consider an additional table that stores primary keys it

tblRelated Acccounts
AccountID
RelatedAccountID

And in this table list all avvounts that are related. Then you can use this to display a list of related accounts for any given AccountID.

You will need to considerhow records are added to this table, as the relationship is two-way i.e. account 100 is related to account 120, AND account 120 is related to account 100 - two lines need to be added, unless you do some fiddling to make the table work both directions, which just seems wrong to me.

SeeThru
Synergy Connections Ltd - Telemarketing Services