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  

Lookup...



 
 
Thread Tools Display Modes
  #1  
Old February 5th, 2010, 12:18 PM posted to microsoft.public.access.tablesdbdesign
Sam
external usenet poster
 
Posts: 855
Default Lookup...

I'm having a challenge in desgining a database for a project. I have not
created any tables for this project and have a general design question.

I am trying to create a table where bank fees can be captured:
1. There are 100 business locations.
2. Each location have various banks unique to it (some have 3 banks, some
have 5 banks).

How can I design a table that when a user is keying in a bank fee
transaction for location A, only the banks associated to location A will be
available in the for selection?

Any advice is greatly apprecaited!
  #2  
Old February 5th, 2010, 03:58 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Lookup...

It is excellent that you are asking these kinds of questions before creating
tables. I wish that more people did.

Seems to me that you need a table of BusinessLocations and another table of
Banks. As a BusinessLocation can use more than one Bank. That would be the
one-to-many relationship that you ideally see in a relational database.

However something tells me that a Bank could also be related to many
BusinessLocations. When you combine that with a BusinessLocation using more
than one Bank, you have a Many-to-Many relationship between those two tables.
This is not good.

To break up the M-M relationship, you need a third table known as a bridging
or linking table named something like BL2Bank. It would contain the Primary
Key data from a BusinessLocation and the PK data from a Bank. That way you
can tell which Banks a BusinessLocation uses AND which BusinessLocations use
a Bank.

After that you would create a Form based on the BusinessLocation and on it
have a subform based on Banks. They would be linked via the BL2Bank table.
Now when you show a certain BusinessLocation in the form, it's Banks will
show up in the Subform.

As you are keying in Fees based on th bank, you may need yet another table
of Fees linked to the Banks table. In that case you may need a sub-subform on
the Banks subform discussed above.

Ouch. I think that my head just exploded! ;-)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Sam" wrote:

I'm having a challenge in desgining a database for a project. I have not
created any tables for this project and have a general design question.

I am trying to create a table where bank fees can be captured:
1. There are 100 business locations.
2. Each location have various banks unique to it (some have 3 banks, some
have 5 banks).

How can I design a table that when a user is keying in a bank fee
transaction for location A, only the banks associated to location A will be
available in the for selection?

Any advice is greatly apprecaited!

  #3  
Old February 5th, 2010, 04:04 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Lookup...

You need two tables, one for Location and another for Banks.

tblLocation
LocationID (primary key, or PK)
LocationName
etc.

tblBank
BankID (PK)
LocationID
BankName
Address
etc.

This assumes a bank can be associated with only one location.

Presumably there is another table for the fees you describe. I can't tell if
it is associated with a bank or a location.

Create a relationship (Tools Relationships) between the two tables via the
BankID fields.

First you will need to create a form based on tblLocation with a subform
based on tblBank to enter Location and Bank information. The linking field
of the subform control (the "box" on the Location form that contains the
subform) is LocationID. This will set up locations and their associated
banks.

I assume you Use a query based on tblLocation, with the fields LocationID and
LocationName as the Row Source for a Location combo box (cboLocation) on your
form (frmMain).

On the form for entering fees, create a query based on tblBank, with the
fields BankID and BankName, as the Row Source of a Bank combo box (cboBank).
For the criteria for BankID (in Query design view) you could use:

Forms!frmMain!cboLocation

In the After Update event of cboLocation:

Me.cboBank.Requery

Some basics of how to work with relational databases may be of help. Here
are some links. IMHO Crystal's tutorial is as good a place as any to get
started.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Sam wrote:
I'm having a challenge in desgining a database for a project. I have not
created any tables for this project and have a general design question.

I am trying to create a table where bank fees can be captured:
1. There are 100 business locations.
2. Each location have various banks unique to it (some have 3 banks, some
have 5 banks).

How can I design a table that when a user is keying in a bank fee
transaction for location A, only the banks associated to location A will be
available in the for selection?

Any advice is greatly apprecaited!


--
Message posted via http://www.accessmonster.com

 




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 01:58 PM.


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