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