Microsoft Access is a relational database system. That simply means that in the one file you can have multiple tables, forms, queries, and reports. It is pretty much like when you were back in junior high school and you kept all of your notes in a three-ringed binder with different sections for each of your individual classes. Access has a navigation pane on the left side that acts as a separator of each type of object.
.
You can have many different tables in the same file; but if you need to get information from more than one table at the same time, you need to create a join between two tables. A join is simply a way to travel between the two tables. Think of the join line as a road used to travel between the two tables. While driving, you need to find a common intersection to be able to go onto the other road. Well, in joining tables, you need to have a common field to create the join. A common field is nothing more than the same field in the two separate tables. Let’s take a look at this. We have two tables that we are going to use as an example. We want to be able to get a list of all of the departments and the employees that are in those departments. To get to the relationship window, you go to the Database Tools ribbon and click on Relationships.
You may need to select the tables that you want to join. That brings up the dialog box that allows you to select the two tables that you need to join. Now simply select your tables.
.
Once you have selected your tables, close the Show Table dialog box. You may need to resize the table information so that you can see the fields in each table. For this example, we are using the Department table and the Employees table.
Now comes the easiest part! Simply drag the field of DeptCode from the tblDepartments where it is the primary key field over the field of DeptCode in the tblEmployees where it is the foreign key field. Now what comes up is this dialog box.
Put a checkmark in Enforce Referential Integrity and I always like to Cascade Update Related Fields and now simply click the Create command button. You have now just created the join between these two tables and anytime you use these two tables in any query, the join line will be there.
All that is left to do is to close out of the Relationship window by clicking the close button and now you can create your queries pulling any information from either table.
These tables will stay joined until you decide to delete this relationship. Creating join lines in Microsoft Access really is this simple!
To hear about the latest Office 2010 news, blogs, and training, subscribe to our newsletter. Click here to subscribe.
Learn more about this and other features in our Introduction to Microsoft Access 2010 class!
I have created a db to manage a Columbarium, i.e. a depsitory for urns. The Columbarium has 160 “niches”, each “niche” has space for two urns. A person can purchase either one or both spaces in a “niche”. I have a single-table db. This keeps the data input simple, i.e., a single screen accommodates the input whether a person buys a single space or both spaces in a “niche”. this also enables me to create a split-screen form whereby I can see every transaction that has occurred on a given Niche. Ultimately, someone’s ashes will be in an urn and “deposited” in a space of some “niche”. It is exactly what I want. But, it also creates a problem because it only records what has been purchased. If someone wants to know what is available, we have to back into that question by looking at a report of what has been sold and say “if it isn’t listed, then it is available.”
Now my question. Can data keyed into an input screen deposit that specific data simultaneously into two tables. For example, we input onto the screen that a certain “niche”, say # 6, space “a” has been purchased. The data is stored onto my primary table. But, can niche 6, space “a” also simultaneously be stored on a table that is preloaded with all niches and spaces?
I realize this is a very convoluted description and questions and appreciate any time you have to address it.
July 5th, 2012 at 1:55 pm