Two tables

[Guest]

Hi, I have 2 tables, Banks and Clients. Each Client can have more than one
bank. What I want to find out, is do I create another table that has the
relationships Client/Bank. In other words, Client A went to Bank X and Bank
Y. Client B to Bank Y and Bank Z and Bank U. The clients table will change,
but the Banks table should stay the same. Is this just a simple add a query
or just create a form?

Thanks

 

[John W. Vinson]

Hi, I have 2 tables, Banks and Clients. Each Client can have more than one
bank. What I want to find out, is do I create another table that has the
relationships Client/Bank. In other words, Client A went to Bank X and Bank
Y. Client B to Bank Y and Bank Z and Bank U. The clients table will change,
but the Banks table should stay the same. Is this just a simple add a query
or just create a form?

Thanks

You need to add a table and also create a form. The new table should have
fields for the ClientID (a link to the clients table's primary key) and for
the BankID (a link to the banks table primary key), and any additional fields
for information pertaining to *this* client and *this* bank.

You can enter data into this table by using a Subform of either the Clients
form or the Banks form, whichever is more convenient (you can even do both).
For example, if you're centering on the client, you could have a Clients form
for entering client-specific data, with a subform based on ClientBanks (or
whatever you call this new table). Use the ClientID as the subform's Master
and Child Link Fields to automatically fill in the clientID, and put a Combo
Box on the subform based on the Banks table.

John W. Vinson [MVP]

 

[Jamie Collins]

I have 2 tables, Banks and Clients. Each Client can have more than one
bank. What I want to find out, is do I create another table that has the
relationships Client/Bank.

Note that the relationship is usually between an account (check
account, savings account, mortgage account, loan account, etc) and one
or more legal persons (natural persons and organizations) who are the
holders of that account; the bank is an attribute of the account i.e.
no direct relationship between person and bank. While there's no
reason to abstract/simplify for your own purposes (and I don't know
what you purposes are) with a single Client/Bank relationship table
(a.k.a. junction table), if you ever need to model accounts then I
would advise you design them in now rather than have to retrofit them
later.

Jamie.

--

 

[Jason Lepack]

In my limited banking experience I don't have a relationship directly
with a bank.

I have multiple bank accounts, at multiple banks. My bank accounts
are related to the bank, I am related to the bank account.

I see the structure as such:

Clients:
client_id
other attributes of clients

Banks:
bank_id
other attributes of banks

Bank_Accounts:
bank_account_id
bank_id
client_id
other attributes of bank_accounts

Cheers,
Jason Lepack

 

[Jamie Collins]

In my limited banking experience I don't have a relationship directly
with a bank.

I have multiple bank accounts, at multiple banks. My bank accounts
are related to the bank, I am related to the bank account.

The person:bank relationship is indirect; there's no reason why the OP
can't abstract the relationship to make it direct. We have an app
where this is done i.e. models the relationship between client and
bank where account details are unknown, undisclosed, etc; we certainly
wouldn't want to be in the situation where this information is lost
because it cannot be entered into the database due to use making the
account entity a prerequisite ('update anomaly' normalization issue).

Jamie.

--