DataBase - Lookup...
Asked By Sam
05-Feb-10 06:18 AM
I am 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!
Database
(1)
Bontrager
(1)
Fees
(1)
Jerry Whittle replied to Sam
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 is 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.
Query showing unexpected results DataBase I've looked through messages on query and report and still can't find an statement here. - - Jerry Whittle - MS Access MVP 2007 - 2009 Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder SQL - SELECT Attendees.CompanyName, Registration.RegistrationFee, Payments.PaymentAmount FROM Attendees INNER JOIN (Registration payment yet, just does not show up at all. So, my record count of Registration Fees / Company Names is wrong. - - Mabyn Try changing INNER JOIN to LEFT JOIN. If that doesn RIGHT JOIN. - - Jerry Whittle - MS Access MVP 2007 - 2009 Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder keywords: Query, showing, unexpected, results description: I've looked through messages on query
Error msg when convertint a select query in to an update query DataBase I am trying to update a field in a table with the first corresponding value Novice R1 7 strut 8 Beginner The following select query displays the desired results SELECT Fees.Event, Fees.EsortKey, First(Fees.LsortKey) AS NewKey FROM Fees GROUP BY Fees.Event, Fees.EsortKey, Fees.[Contest ID] HAVING (((Fees.[Contest ID]) = 30)); But when I change to an update query
Run report in one database from another database DataBase Is there a way to run a report that was built in one database from another database? I have created a fairly complex report in one database that the users of another database need to view, however, there is other functionality in the database with the report that these users should not have access to. I don't want
Link to Database - Password DataBase HI, I have two database in two different folder. One (Database A) of the folder contains all the tables only. Other one is where I use the database (Database B) everyday and the database is linking the other database that has all the tables only. I set the Database B linking to Database A