Lookup...

[Sam]

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!

 

[Jerry Whittle]

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! ;-)

 

[BruceM via AccessMonster.com]

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/accessjunkie/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