Linking Tables in a Query
IIHE  |  Education  |  Getting Started With Microsoft Access  |  Linking Tables in a Query

A query can get data from more than one table at a time. When you first design a query, you can add multiple tables to the query in the Show Table dialog by simply double clicking on the tables that you want to work with. (To add tables to a query that you have already designed, choose Show Table from the Query menu.) Start a new query by clicking on the New button on the Query tab:

For this example, choose HFHS Panel and HFHS Panel Admission by double clicking on their names:

We need to tie the data from the two tables together so that we can get related data from the tables. For these databases, the best field to link is the MRN. To link the MRN, drag the Medical Record Number in HFHS Panel to the MRN in the HFHS Panel Admissions:

You've now set a relationship between the field in the two databases. You control how the tables are linked by double clicking on the line connecting the two tables:

For our example, we want to display all the patients that where admitted to the hospital and had office visits. So, in this case, we want the default setting where only the records that are in both databases are displayed. If you picked the second or third option, then there would be an arrow at the end of the line connecting the databases.Click on the OK button.

Choose the fields for the query as shown below. Remember, you add a field to the query by double clicking on the name of the field. Also notice that all the fields are from the HFHS Panel Admissions table. This won't normally be the case:

What we are doing here is using the Joined Tables to limit the records to those patients that have visited the clinics. Since we didn't choose any of the fields from HFHS Panel, none of the information about the clinic visits will be displayed. Now let's limit the records displayed to patients that have been to East Jefferson and have a hospital admission between January and June 1998. Set the Criteria to:

Field	Criteria
Site Cd	76
ADM_DT	between 1/1/98 and 6/30/98

Finally, set the sort order for the MRN to Ascending by double clicking in the Sort row under MRN. Your query should look like:

That's it. The query is finished. Click the Run button located on the toolbar to look at the results:

Don't forget to save your query.

 

Creating a Make-Table Query  |  Getting Started With Microsoft Access  |  Importing Data From Excel