MS Access Mapping Multiple Fields From One Table to Multiple Fields In Another Table

Question

In my Access database I have a parent table called Unit that has two fields called unitNumber and floor. In a child table called Lease I used the Lookup Wizard to create a foreign key called unitIDFK linked back to the Unit table and included the two fields unitNumber and floor from the Unit table. I need help with issues:

1. When I go to Datasheet View and add data to the Lease table fields, the foreign key unitIDFK has a drop down selection box (list box or combo box?) that displays two columns of data which are the unitNumber and floor fields from the Unit table. The data in both columns are selectable as one row of data, yet only the value in the unitNumber in the first column is populated into the foreign key field. How do I get the value in the floor in the second column to also populate into a field in the Lease table?

2. Same problem when I make the Lease table into a data entry form. I assume that fixing the first problem will also solve the problem of getting both the unitNumber and floor to display in the form.

I have spent the better part of today googling my question and watching lots of videos on creating relationships and editing properties in the Design View as well as searching through the archives here at Stack Overflow without any luck. Help please.

Thanks and RegardsUnit and Lease Tables Relationship

Answer 1

in relational databases like access the data need to be organized as a set of normalized tables. with a few assumptions and ignoring some columns this is what your table structure needs to look like:

Note the emphasis on letting access provide a primary key. To make it simple the primary key is a number the database designer should never touch and the end user should never see. Even if buildingID, floor, and UnitID form a natural 3 part key use a separate access key as your database will be busted if some manager says eek we can't have a unit 13. Note the data for a lease is in 3 separate tables in this simple example. Even the database designer doesn't want to tab between tables to enter each lease manually. The end user will kindly refuse. Instead provide a user friendly interface. The user interface is called a form. In particular we need a form designed for data entry as opposed to search. Please follow along by creating the tables above and adding the 1 to many relationships using the relationships tool. If you create the relationships first access will know how to do a better job when we create the data entry forms. Next select the lease table under tables and click create then form on the ribbon. Do the same for the unit table and the building table in that order. Access will make you a nice set of starter data entry forms.

Unit Form:

I left the primary key on the lease form to show it, but I deleted it on the unit form. the person doing data entry never has to see the tables let alone the primary keys. Note how because we already established the relationships between tables access automatically included the lease form as a subform on the unit form (boo and yah).
What happens next depends on your actual project and end user. For instance I used the building form to add a couple buildings cleveryly named building 1 -4. then I right clicked on the building foreign key textbox on the unit form and selected change to combo box. (replacing the foreign key with a user friendly name). You can look combo boxes up but in short I left the data source as unitTable.BuildingIDFK and set the rowsource to buildings.accessprovidedID, buildings.buildingname with column widths 0,1. Anyway default access forms are not what I would call super intuitive. So practice and you will understand much better. Delete the id columns. Enter some data in the various forms and see what happens in the corresponding tables. Delete the table data, find the form record selectors. After seeing how the forms work and how they could be more user friendly you will have a much better idea of how to proceed.