A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Two tables



 
 
Thread Tools Display Modes
  #1  
Old August 16th, 2007, 12:10 AM posted to microsoft.public.access.tablesdbdesign
cbayardo
external usenet poster
 
Posts: 28
Default Two tables

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
  #2  
Old August 16th, 2007, 03:01 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Two tables

On Wed, 15 Aug 2007 16:10:00 -0700, cbayardo
wrote:

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]
  #3  
Old August 16th, 2007, 10:52 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Two tables

On 16 Aug, 00:10, cbayardo wrote:
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.

--


  #4  
Old August 16th, 2007, 01:41 PM posted to microsoft.public.access.tablesdbdesign
Jason Lepack
external usenet poster
 
Posts: 600
Default Two tables

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

On Aug 15, 7:10 pm, cbayardo
wrote:
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



  #5  
Old August 20th, 2007, 11:17 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Two tables

On 16 Aug, 13:41, Jason Lepack wrote:
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.

--


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.