To import data from access into an excel data model, use the

            You can create a data model from external relational data in Excel. Data models in Excel let you store multiple tables of information. They can then be used a data source for PivotTables, PivotCharts, and Power View reports.

            The choice to add a selected table to a data model occurs when manually creating a PivotTable in Excel. This lets you use a relational data source in Excel. Starting in Excel 2016, Excel automatically creates relationships between tables in the data model when your analysis requires linking two or more tables together. However, you can also manually create relationships between the data within the table fields in Excel. This lets you relate them as needed for your reports. The tables saved into the data model within an Excel workbook can be tables within Excel worksheets or external data tables.

            This lesson shows how to create a data model from external relational data in Excel, like a Microsoft Access database. If you import multiple tables from an existing relational data source, the data model is created automatically.

            To import multiple tables from an external relational data source, click the “Data” tab within the Ribbon. Then click the desired button within the “Get External Data” button group. For example, to add tables from an Access database to the data model in Excel, click the “From Access” button in the “Get External Data” button group on the “Data” tab within the Ribbon. In this case, the “Select Data Source” dialog box appears. Use this dialog box to navigate to and then select the desired Access database file to open. Then click the “Open” button to continue.

Create a Data Model from External Relational Data in Excel – Instructions: A picture of a user selecting the external relational data tables to add to the data model in an Excel workbook by using the “Select Table” dialog box.

            Next, the “Select Table” dialog box appears. To enable the selection of multiple tables from the database file, check the “Enable selection of multiple tables” checkbox at the top of this dialog box. Then check the checkboxes next to the tables to add to the data model in the Excel workbook. Then click the “OK” button to continue.

            The “Import Data” dialog box then appears in Excel. Choose the option you prefer within the “Select how you want to view this data in your workbook” section. Then choose where to place the imported data by selecting an option within the “Where do you want to put the data?” section. Depending on the choice made in the first section, not every option is necessarily available in the latter section. After deciding where to place the type of data to import, click the “OK” button to finish.

            At that point, Excel creates the data connection and imports the necessary data into the data model within Excel. It also creates any visual representation of the data you imported and places it into the selected location.

Create a Data Model from External Relational Data in Excel: Instructions

1. To import multiple tables from an external relational data source, click the “Data” tab in the Ribbon.
2. Then click the button associated with the data type to import in the “Get External Data” button group.
3. The “Select Data Source” dialog box then appears.
4. Use this dialog box to navigate to and then select the desired database file to open.
5. Click the “Open” button to continue.
6. To enable the selection of multiple tables from the database file, check the “Enable selection of multiple tables” checkbox at the top of the “Select Table” dialog box that appears.
7. Check the checkboxes next to the tables to add to the data model.
8. Click the “OK” button to continue.
9. The “Import Data” dialog box then appears within Excel.
10. Choose the preferred option within the “Select how you want to view this data in your workbook” section.
11. Then choose where to place the imported data by selecting an option within the “Where do you want to put the data?” section.
12. Depending on the choice made in the first section, not every option is necessarily available in the second section.
13. Click the “OK” button to finish.

Create a Data Model from External Relational Data in Excel: Video Lesson

            The following video lesson, titled “Creating a Data Model from External Relational Data,” shows how to create a data model from external relational data in Excel. This video lesson is from our complete Excel tutorial, titled “Mastering Excel Made Easy v.2016-2013.”

{"appState":{"pageLoadApiCallsStatus":true},"articleState":{"article":{"headers":{"creationTime":"2019-01-29T19:13:10+00:00","modifiedTime":"2019-01-29T19:13:10+00:00","timestamp":"2022-02-24T17:05:04+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"//dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Software","_links":{"self":"//dummies-api.dummies.com/v2/categories/33618"},"slug":"software","categoryId":33618},{"name":"Microsoft Products","_links":{"self":"//dummies-api.dummies.com/v2/categories/33642"},"slug":"microsoft-products","categoryId":33642},{"name":"Excel","_links":{"self":"//dummies-api.dummies.com/v2/categories/33644"},"slug":"excel","categoryId":33644}],"title":"How to Query an Access Database Tables from Excel 2019","strippedTitle":"how to query an access database tables from excel 2019","slug":"how-to-query-an-access-database-tables-from-excel-2019","canonicalUrl":"","seo":{"metaDescription":"","noIndex":0,"noFollow":0},"content":"To make an external data <a href=\"//www.dummies.com/software/microsoft-office/6-common-microsoft-access-queries/\">query to an Microsoft Access database table</a>, you click Data→  Get Data→  From Database→  From Microsoft Access Database on the Excel Ribbon or press Alt+APNDC. Excel opens the Import Data dialog box, where you select the name of the Access database (using an *.mdb file extension) and then click the Import button.\r\n\r\nAfter Excel establishes a connection with the Access database file you select in the Import Data dialog box, the Navigator dialog box opens. The Navigator dialog box is divided into two panes: Selection on the left and Preview on the right. When you click the name of a data table or query in the Selection Pane, Excel displays a portion of the Access data in the Preview pane on the right. To import multiple (related) data tables from the selected Access database, select the Enable Multiple Items check box. Excel then displays check boxes before the name of each table in the database. After you select the check boxes for all the tables you want to import, you have a choice of options:\r\n<ul>\r\n \t<li><strong>Load</strong> button to import the Access file data from the item(s) selected in the Navigator directly into the current worksheet starting at the cell cursor’s current position</li>\r\n \t<li><strong>Load To</strong> option on the Load button’s drop-down menu to open the Import Data dialog box where you can how you want to view the imported Access data (as a worksheet data Table, PivotTable, PivotChart, or just data connection without importing any data) and where to import the Access data (existing or new worksheet) as well as whether or not to add the Access data to the worksheet’s Data Model</li>\r\n \t<li><strong>Transform Data</strong> button to display the Access data table(s) in the Excel Power Query Editor where you can further query and transform the data before importing into the current Excel worksheet with its Close & Load or Close & Load To option</li>\r\n</ul>\r\n[caption id=\"attachment_259711\" align=\"aligncenter\" width=\"535\"]<img class=\"wp-image-259711 size-full\" src=\"//sg.cdnki.com/to-import-data-from-access-into-an-excel-data-model-use-the---aHR0cHM6Ly93d3cuZHVtbWllcy5jb20vd3AtY29udGVudC91cGxvYWRzL2V4Y2VsLTIwMTktcXVlcnktYWNjZXNzLmpwZw==.webp\" alt=\"using Excel to query Access\" width=\"535\" height=\"426\" /> Using the Navigator to select which data tables and queries from the Northwind Access database to import into the current Excel worksheet.[/caption]\r\n\r\n \r\n\r\nWhen you select the Load To option to specify how and where to import the Access data, the Import Data dialog box contains the following option buttons:\r\n<ul>\r\n \t<li><strong>Table</strong> option button to have the data in the Access data table(s) imported into an Excel data table in either the current or new worksheet — see the “Existing Worksheet” and “New Worksheet” bullets that follow. Note that when you import more than one data table, the Existing Worksheet option is no longer available, and the data from each imported data table will be imported to a separate new worksheet in the current workbook.</li>\r\n \t<li><strong>PivotTable Report</strong> option button (the default) to have the data in the Access data table(s) imported into a new pivot table that you can construct with the Access data.</li>\r\n \t<li><strong>PivotChart</strong> option button to have the data in the Access data table(s) imported into a new pivot table with an embedded pivot chart that you can construct with the Access data.</li>\r\n \t<li><strong>Only Create Connection</strong> option button to create a connection to the Access database table(s) that you can use later to actually import its data.</li>\r\n \t<li><strong>Existing Worksheet</strong> option button to have the data in the Access data table(s) imported into the current worksheet starting at the current cell address listed in the text box below.</li>\r\n \t<li><strong>New Worksheet</strong> option button to have the data in the Access data table(s) imported into new sheet(s) that’s added to the end of the sheets already in the workbook.</li>\r\n \t<li><strong>Add This Data to the Data Model</strong> check box to add the imported data in the Access data table(s) to the Data Model already defined in the Excel workbook using relatable, key fields.</li>\r\n \t<li><strong>Properties</strong> drop-down button to open the drop-down menu with the Import Relationships Between Tables check box (selected by default) and Properties item. Deselect the check box to prevent Excel from recognizing the relationship established between the data tables in Access. Click the Properties button to open the Connection Properties dialog box, where you can modify all sorts of connection properties, including when the Access data’s refreshed in the Excel worksheet and how the connection is made.</li>\r\n</ul>\r\nThe image below shows you a new Northwind Customer Orders workbook after importing both the Customers and Orders data tables from the sample Northwind Access database as new data tables on separate worksheets. When the two data tables were imported, Excel automatically added two new worksheets (Sheet2 and Sheet3) to the workbook, while at the same time importing the Customers data table to Sheet2 (which I renamed Customers) and the Orders data table to Sheet3 (renamed Orders). Sheet1 (which was blank) was deleted prior to taking the screenshot.\r\n\r\n[caption id=\"attachment_259712\" align=\"aligncenter\" width=\"535\"]<img class=\"wp-image-259712 size-full\" src=\"//sg.cdnki.com/to-import-data-from-access-into-an-excel-data-model-use-the---aHR0cHM6Ly93d3cuZHVtbWllcy5jb20vd3AtY29udGVudC91cGxvYWRzL2V4Y2VsLTIwMTktaW1wb3J0LWFjY2Vzcy1kYXRhLmpwZw==.webp\" alt=\"import Access data to Excel\" width=\"535\" height=\"400\" /> Customers worksheet with the data imported from the Access Customers data table in the sample Northwind database.[/caption]\r\n\r\nThe following image shows same new workbook, this time with the Orders worksheet selected and the Manage Relationships dialog box open (by clicking the Relationships button on the Data tab or pressing Alt+AA). When Excel imported these two data tables, it automatically picked up on and retained the original relationship between them in the Northwind database, where the CustomerID field is the primary key field in the Customers data table and a foreign key field in the Orders data table.\r\n\r\n[caption id=\"attachment_259713\" align=\"aligncenter\" width=\"535\"]<img class=\"wp-image-259713 size-full\" src=\"//sg.cdnki.com/to-import-data-from-access-into-an-excel-data-model-use-the---aHR0cHM6Ly93d3cuZHVtbWllcy5jb20vd3AtY29udGVudC91cGxvYWRzL2V4Y2VsLTIwMTktdGFibGUtcmVsYXRpb25zaGlwLmpwZw==.webp\" alt=\"retaining original table relationship after data import Excel\" width=\"535\" height=\"400\" /> Orders worksheet with the data imported from the Orders data table in the sample Northwind database showing the relationship with the Customers table.[/caption]\r\n\r\n \r\n\r\nAfter importing the external data into one of your Excel worksheets, you can then use the <a href=\"//www.dummies.com/software/microsoft-office/excel/using-excel-2019-filters/\">Filter buttons</a> attached to the various fields to sort the data and filter the data.\r\n<p class=\"article-tips remember\">After you import data from an external source, such as a Microsoft Access database, into a worksheet, Excel automatically displays a Queries & Connections task pane with two tabs: Queries, which displays the source(s) of the data imported into the current workbook, and Connections, which displays their connection to the workbook Data Model (and to each other if there are multiple sources and they are related to each other). If this task pane is not currently displayed in the current worksheet, click Data→  Queries & Connections (or press Alt+AO) to redisplay it.</p>\r\n<p class=\"article-tips tip\">Excel keeps a list of all the external data sources and data queries you make to the current workbook so that you can reuse them to import updated data from another database or web page. To quickly reconnect with a data source, click the Recent Sources button on the Data tab (Alt+PR) to open the Recent Sources dialog box where you click the name of the external file before you select the Connect button. To reuse a query, click the Existing Connections button on the Data tab (Alt+AX) to open the Existing Connections dialog box to access this list and then click the name of the query to repeat before you click the Open button.</p>","description":"To make an external data <a href=\"//www.dummies.com/software/microsoft-office/6-common-microsoft-access-queries/\">query to an Microsoft Access database table</a>, you click Data→  Get Data→  From Database→  From Microsoft Access Database on the Excel Ribbon or press Alt+APNDC. Excel opens the Import Data dialog box, where you select the name of the Access database (using an *.mdb file extension) and then click the Import button.\r\n\r\nAfter Excel establishes a connection with the Access database file you select in the Import Data dialog box, the Navigator dialog box opens. The Navigator dialog box is divided into two panes: Selection on the left and Preview on the right. When you click the name of a data table or query in the Selection Pane, Excel displays a portion of the Access data in the Preview pane on the right. To import multiple (related) data tables from the selected Access database, select the Enable Multiple Items check box. Excel then displays check boxes before the name of each table in the database. After you select the check boxes for all the tables you want to import, you have a choice of options:\r\n<ul>\r\n \t<li><strong>Load</strong> button to import the Access file data from the item(s) selected in the Navigator directly into the current worksheet starting at the cell cursor’s current position</li>\r\n \t<li><strong>Load To</strong> option on the Load button’s drop-down menu to open the Import Data dialog box where you can how you want to view the imported Access data (as a worksheet data Table, PivotTable, PivotChart, or just data connection without importing any data) and where to import the Access data (existing or new worksheet) as well as whether or not to add the Access data to the worksheet’s Data Model</li>\r\n \t<li><strong>Transform Data</strong> button to display the Access data table(s) in the Excel Power Query Editor where you can further query and transform the data before importing into the current Excel worksheet with its Close & Load or Close & Load To option</li>\r\n</ul>\r\n[caption id=\"attachment_259711\" align=\"aligncenter\" width=\"535\"]<img class=\"wp-image-259711 size-full\" src=\"//www.dummies.com/wp-content/uploads/excel-2019-query-access.jpg\" alt=\"using Excel to query Access\" width=\"535\" height=\"426\" /> Using the Navigator to select which data tables and queries from the Northwind Access database to import into the current Excel worksheet.[/caption]\r\n\r\n \r\n\r\nWhen you select the Load To option to specify how and where to import the Access data, the Import Data dialog box contains the following option buttons:\r\n<ul>\r\n \t<li><strong>Table</strong> option button to have the data in the Access data table(s) imported into an Excel data table in either the current or new worksheet — see the “Existing Worksheet” and “New Worksheet” bullets that follow. Note that when you import more than one data table, the Existing Worksheet option is no longer available, and the data from each imported data table will be imported to a separate new worksheet in the current workbook.</li>\r\n \t<li><strong>PivotTable Report</strong> option button (the default) to have the data in the Access data table(s) imported into a new pivot table that you can construct with the Access data.</li>\r\n \t<li><strong>PivotChart</strong> option button to have the data in the Access data table(s) imported into a new pivot table with an embedded pivot chart that you can construct with the Access data.</li>\r\n \t<li><strong>Only Create Connection</strong> option button to create a connection to the Access database table(s) that you can use later to actually import its data.</li>\r\n \t<li><strong>Existing Worksheet</strong> option button to have the data in the Access data table(s) imported into the current worksheet starting at the current cell address listed in the text box below.</li>\r\n \t<li><strong>New Worksheet</strong> option button to have the data in the Access data table(s) imported into new sheet(s) that’s added to the end of the sheets already in the workbook.</li>\r\n \t<li><strong>Add This Data to the Data Model</strong> check box to add the imported data in the Access data table(s) to the Data Model already defined in the Excel workbook using relatable, key fields.</li>\r\n \t<li><strong>Properties</strong> drop-down button to open the drop-down menu with the Import Relationships Between Tables check box (selected by default) and Properties item. Deselect the check box to prevent Excel from recognizing the relationship established between the data tables in Access. Click the Properties button to open the Connection Properties dialog box, where you can modify all sorts of connection properties, including when the Access data’s refreshed in the Excel worksheet and how the connection is made.</li>\r\n</ul>\r\nThe image below shows you a new Northwind Customer Orders workbook after importing both the Customers and Orders data tables from the sample Northwind Access database as new data tables on separate worksheets. When the two data tables were imported, Excel automatically added two new worksheets (Sheet2 and Sheet3) to the workbook, while at the same time importing the Customers data table to Sheet2 (which I renamed Customers) and the Orders data table to Sheet3 (renamed Orders). Sheet1 (which was blank) was deleted prior to taking the screenshot.\r\n\r\n[caption id=\"attachment_259712\" align=\"aligncenter\" width=\"535\"]<img class=\"wp-image-259712 size-full\" src=\"//www.dummies.com/wp-content/uploads/excel-2019-import-access-data.jpg\" alt=\"import Access data to Excel\" width=\"535\" height=\"400\" /> Customers worksheet with the data imported from the Access Customers data table in the sample Northwind database.[/caption]\r\n\r\nThe following image shows same new workbook, this time with the Orders worksheet selected and the Manage Relationships dialog box open (by clicking the Relationships button on the Data tab or pressing Alt+AA). When Excel imported these two data tables, it automatically picked up on and retained the original relationship between them in the Northwind database, where the CustomerID field is the primary key field in the Customers data table and a foreign key field in the Orders data table.\r\n\r\n[caption id=\"attachment_259713\" align=\"aligncenter\" width=\"535\"]<img class=\"wp-image-259713 size-full\" src=\"//www.dummies.com/wp-content/uploads/excel-2019-table-relationship.jpg\" alt=\"retaining original table relationship after data import Excel\" width=\"535\" height=\"400\" /> Orders worksheet with the data imported from the Orders data table in the sample Northwind database showing the relationship with the Customers table.[/caption]\r\n\r\n \r\n\r\nAfter importing the external data into one of your Excel worksheets, you can then use the <a href=\"//www.dummies.com/software/microsoft-office/excel/using-excel-2019-filters/\">Filter buttons</a> attached to the various fields to sort the data and filter the data.\r\n<p class=\"article-tips remember\">After you import data from an external source, such as a Microsoft Access database, into a worksheet, Excel automatically displays a Queries & Connections task pane with two tabs: Queries, which displays the source(s) of the data imported into the current workbook, and Connections, which displays their connection to the workbook Data Model (and to each other if there are multiple sources and they are related to each other). If this task pane is not currently displayed in the current worksheet, click Data→  Queries & Connections (or press Alt+AO) to redisplay it.</p>\r\n<p class=\"article-tips tip\">Excel keeps a list of all the external data sources and data queries you make to the current workbook so that you can reuse them to import updated data from another database or web page. To quickly reconnect with a data source, click the Recent Sources button on the Data tab (Alt+PR) to open the Recent Sources dialog box where you click the name of the external file before you select the Connect button. To reuse a query, click the Existing Connections button on the Data tab (Alt+AX) to open the Existing Connections dialog box to access this list and then click the name of the query to repeat before you click the Open button.</p>","blurb":"","authors":[{"authorId":9027,"name":"Greg Harvey","slug":"greg-harvey","description":"Greg Harvey, PhD, was president of Mind Over Media and a highly skilled instructor. He wrote computer books for more than 20 years, and his long list of best sellers included all editions of Excel For Dummies, Excel All-in-One For Dummies, and Excel Workbook For Dummies. He died in 2020.","_links":{"self":"//dummies-api.dummies.com/v2/authors/9027"}}],"primaryCategoryTaxonomy":{"categoryId":33644,"title":"Excel","slug":"excel","_links":{"self":"//dummies-api.dummies.com/v2/categories/33644"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":259741,"title":"How to Use the Excel 2019 Solver","slug":"how-to-use-the-excel-2019-solver","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/259741"}},{"articleId":259735,"title":"How to Password-Protect Your Excel 2019 File","slug":"how-to-password-protect-your-excel-2019-file","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/259735"}},{"articleId":259729,"title":"How to Create Forecast Worksheets in Excel 2019","slug":"how-to-create-forecast-worksheets-in-excel-2019","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/259729"}},{"articleId":259724,"title":"How to Use the 3D Map Feature in Excel 2019","slug":"how-to-use-the-3d-map-feature-in-excel-2019","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/259724"}},{"articleId":259717,"title":"Transforming a Data Query in the Power Query Editor in Excel 2019","slug":"transforming-a-data-query-in-the-power-query-editor-in-excel-2019","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/259717"}}],"fromCategory":[{"articleId":288828,"title":"Excel 2021 All-in-One For Dummies Cheat Sheet","slug":"excel-2021-all-in-one-for-dummies-cheat-sheet","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/288828"}},{"articleId":265521,"title":"How to Use the XLOOKUP Function in Excel 2016","slug":"how-to-use-the-xlookup-function-in-excel-2016","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/265521"}},{"articleId":263475,"title":"Notes and File Sharing features in Excel 2016 Update","slug":"notes-and-coauthoring-features-in-excel-2016-update","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/263475"}},{"articleId":263466,"title":"New Chart & Graphics features on Excel 2016 update","slug":"new-chart-graphics-features-on-excel-2016-update","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/263466"}},{"articleId":263453,"title":"New Formulas and Functions in Excel 2016","slug":"how-to-use-new-formula-and-functions-in-excel-2016-update","categoryList":["technology","software","microsoft-products","excel"],"_links":{"self":"//dummies-api.dummies.com/v2/articles/263453"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281708,"slug":"excel-2019-all-in-one-for-dummies","isbn":"9781119517948","categoryList":["technology","software","microsoft-products","excel"],"amazon":{"default":"//www.amazon.com/gp/product/111951794X/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"//www.amazon.ca/gp/product/111951794X/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"//www.tkqlhce.com/click-9208661-13710633?url=//www.chapters.indigo.ca/en-ca/books/product/111951794X-item.html&cjsku=978111945484","gb":"//www.amazon.co.uk/gp/product/111951794X/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"//www.amazon.de/gp/product/111951794X/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"//www.dummies.com/wp-content/uploads/excel-2019-all-in-one-for-dummies-cover-9781119517948-203x255.jpg","width":203,"height":255},"title":"Excel 2019 All-in-One For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"\n <p><b data-author-id=\"9027\">Greg Harvey, PhD,</b> is a veteran computer educator dating back to the days of DOS and Lotus 1-2-3. He has taught spreadsheet and database management courses at Golden Gate University and written dozens of books, including many in the <i>For Dummies</i> series. </p>","authors":[{"authorId":9027,"name":"Greg Harvey","slug":"greg-harvey","description":"Greg Harvey, PhD, was president of Mind Over Media and a highly skilled instructor. He wrote computer books for more than 20 years, and his long list of best sellers included all editions of Excel For Dummies, Excel All-in-One For Dummies, and Excel Workbook For Dummies. He died in 2020.","_links":{"self":"//dummies-api.dummies.com/v2/authors/9027"}}],"_links":{"self":"//dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;software&quot;,&quot;microsoft-products&quot;,&quot;excel&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119517948&quot;]}]\" id=\"du-slot-6217bac0b662e\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;software&quot;,&quot;microsoft-products&quot;,&quot;excel&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119517948&quot;]}]\" id=\"du-slot-6217bac0b6fc6\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0}},"primaryLearningPath":"Advance","lifeExpectancy":null,"lifeExpectancySetFrom":null,"dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":259710},"articleLoadedStatus":"success"},"listState":{"list":{},"objectTitle":"","status":"initial","pageType":null,"objectId":null,"page":1,"sortField":"time","sortOrder":1,"categoriesIds":[],"articleTypes":[],"filterData":{},"filterDataLoadedStatus":"initial","pageSize":10},"adsState":{"pageScripts":{"headers":{"timestamp":"2022-05-27T12:59:08+00:00"},"adsId":0,"data":{"scripts":[{"pages":["all"],"location":"header","script":"<!--Optimizely Script-->\r\n<script src=\"//cdn.optimizely.com/js/10563184655.js\"></script>","enabled":false},{"pages":["all"],"location":"header","script":"<!-- comScore Tag -->\r\n<script>var _comscore = _comscore || [];_comscore.push({ c1: \"2\", c2: \"15097263\" });(function() {var s = document.createElement(\"script\"), el = document.getElementsByTagName(\"script\")[0]; s.async = true;s.src = (document.location.protocol == \"\" ? \"//sb\" : \"//b\") + \".scorecardresearch.com/beacon.js\";el.parentNode.insertBefore(s, el);})();</script><noscript><img src=\"//sb.scorecardresearch.com/p?c1=2&c2=15097263&cv=2.0&cj=1\" /></noscript>\r\n<!-- / comScore Tag -->","enabled":true},{"pages":["all"],"location":"footer","script":"<!--BEGIN QUALTRICS WEBSITE FEEDBACK SNIPPET-->\r\n<script type='text/javascript'>\r\n(function(){var g=function(e,h,f,g){\r\nthis.get=function(a){for(var a=a+\"=\",c=document.cookie.split(\";\"),b=0,e=c.length;b<e;b++){for(var d=c[b];\" \"==d.charAt(0);)d=d.substring(1,d.length);if(0==d.indexOf(a))return d.substring(a.length,d.length)}return null};\r\nthis.set=function(a,c){var b=\"\",b=new Date;b.setTime(b.getTime()+6048E5);b=\"; expires=\"+b.toGMTString();document.cookie=a+\"=\"+c+b+\"; path=/; \"};\r\nthis.check=function(){var a=this.get(f);if(a)a=a.split(\":\");else if(100!=e)\"v\"==h&&(e=Math.random()>=e/100?0:100),a=[h,e,0],this.set(f,a.join(\":\"));else return!0;var c=a[1];if(100==c)return!0;switch(a[0]){case \"v\":return!1;case \"r\":return c=a[2]%Math.floor(100/c),a[2]++,this.set(f,a.join(\":\")),!c}return!0};\r\nthis.go=function(){if(this.check()){var a=document.createElement(\"script\");a.type=\"text/javascript\";a.src=g;document.body&&document.body.appendChild(a)}};\r\nthis.start=function(){var t=this;\"complete\"!==document.readyState?window.addEventListener?window.addEventListener(\"load\",function(){t.go()},!1):window.attachEvent&&window.attachEvent(\"onload\",function(){t.go()}):t.go()};};\r\ntry{(new g(100,\"r\",\"QSI_S_ZN_5o5yqpvMVjgDOuN\",\"//zn5o5yqpvmvjgdoun-wiley.siteintercept.qualtrics.com/SIE/?Q_ZID=ZN_5o5yqpvMVjgDOuN\")).start()}catch(i){}})();\r\n</script><div id='ZN_5o5yqpvMVjgDOuN'><!--DO NOT REMOVE-CONTENTS PLACED HERE--></div>\r\n<!--END WEBSITE FEEDBACK SNIPPET-->","enabled":false},{"pages":["all"],"location":"header","script":"<!-- Hotjar Tracking Code for //www.dummies.com -->\r\n<script>\r\n (function(h,o,t,j,a,r){\r\n h.hj=h.hj||function(){(h.hj.q=h.hj.q||[]).push(arguments)};\r\n h._hjSettings={hjid:257151,hjsv:6};\r\n a=o.getElementsByTagName('head')[0];\r\n r=o.createElement('script');r.async=1;\r\n r.src=t+h._hjSettings.hjid+j+h._hjSettings.hjsv;\r\n a.appendChild(r);\r\n })(window,document,'//static.hotjar.com/c/hotjar-','.js?sv=');\r\n</script>","enabled":false},{"pages":["article"],"location":"header","script":"<!-- //Connect Container: dummies --> <script src=\"//get.s-onetag.com/bffe21a1-6bb8-4928-9449-7beadb468dae/tag.min.js\" async defer></script>","enabled":true},{"pages":["homepage"],"location":"header","script":"<meta name=\"facebook-domain-verification\" content=\"irk8y0irxf718trg3uwwuexg6xpva0\" />","enabled":true},{"pages":["homepage","article","category","search"],"location":"footer","script":"<!-- Facebook Pixel Code -->\r\n<noscript>\r\n<img height=\"1\" width=\"1\" src=\"//www.facebook.com/tr?id=256338321977984&ev=PageView&noscript=1\"/>\r\n</noscript>\r\n<!-- End Facebook Pixel Code -->","enabled":true}]}},"pageScriptsLoadedStatus":"success"},"searchState":{"searchList":[],"searchStatus":"initial","relatedArticlesList":[],"relatedArticlesStatus":"initial"},"routeState":{"name":"Article4","path":"/article/technology/software/microsoft-products/excel/how-to-query-an-access-database-tables-from-excel-2019-259710/","hash":"","query":{},"params":{"category1":"technology","category2":"software","category3":"microsoft-products","category4":"excel","article":"how-to-query-an-access-database-tables-from-excel-2019-259710"},"fullPath":"/article/technology/software/microsoft-products/excel/how-to-query-an-access-database-tables-from-excel-2019-259710/","meta":{"routeType":"article","breadcrumbInfo":{"suffix":"Articles","baseRoute":"/category/articles"},"prerenderWithAsyncData":true},"from":{"name":null,"path":"/","hash":"","query":{},"params":{},"fullPath":"/","meta":{}}},"dropsState":{"submitEmailResponse":false,"status":"initial"},"sfmcState":{"newsletterSignupStatus":"initial"}}

To make an external data query to an Microsoft Access database table, you click Data→  Get Data→  From Database→  From Microsoft Access Database on the Excel Ribbon or press Alt+APNDC. Excel opens the Import Data dialog box, where you select the name of the Access database (using an *.mdb file extension) and then click the Import button.

After Excel establishes a connection with the Access database file you select in the Import Data dialog box, the Navigator dialog box opens. The Navigator dialog box is divided into two panes: Selection on the left and Preview on the right. When you click the name of a data table or query in the Selection Pane, Excel displays a portion of the Access data in the Preview pane on the right. To import multiple (related) data tables from the selected Access database, select the Enable Multiple Items check box. Excel then displays check boxes before the name of each table in the database. After you select the check boxes for all the tables you want to import, you have a choice of options:

• Load button to import the Access file data from the item(s) selected in the Navigator directly into the current worksheet starting at the cell cursor’s current position
• Load To option on the Load button’s drop-down menu to open the Import Data dialog box where you can how you want to view the imported Access data (as a worksheet data Table, PivotTable, PivotChart, or just data connection without importing any data) and where to import the Access data (existing or new worksheet) as well as whether or not to add the Access data to the worksheet’s Data Model
• Transform Data button to display the Access data table(s) in the Excel Power Query Editor where you can further query and transform the data before importing into the current Excel worksheet with its Close & Load or Close & Load To option

Using the Navigator to select which data tables and queries from the Northwind Access database to import into the current Excel worksheet.

When you select the Load To option to specify how and where to import the Access data, the Import Data dialog box contains the following option buttons:

• Table option button to have the data in the Access data table(s) imported into an Excel data table in either the current or new worksheet — see the “Existing Worksheet” and “New Worksheet” bullets that follow. Note that when you import more than one data table, the Existing Worksheet option is no longer available, and the data from each imported data table will be imported to a separate new worksheet in the current workbook.
• PivotTable Report option button (the default) to have the data in the Access data table(s) imported into a new pivot table that you can construct with the Access data.
• PivotChart option button to have the data in the Access data table(s) imported into a new pivot table with an embedded pivot chart that you can construct with the Access data.
• Only Create Connection option button to create a connection to the Access database table(s) that you can use later to actually import its data.
• Existing Worksheet option button to have the data in the Access data table(s) imported into the current worksheet starting at the current cell address listed in the text box below.
• New Worksheet option button to have the data in the Access data table(s) imported into new sheet(s) that’s added to the end of the sheets already in the workbook.
• Add This Data to the Data Model check box to add the imported data in the Access data table(s) to the Data Model already defined in the Excel workbook using relatable, key fields.
• Properties drop-down button to open the drop-down menu with the Import Relationships Between Tables check box (selected by default) and Properties item. Deselect the check box to prevent Excel from recognizing the relationship established between the data tables in Access. Click the Properties button to open the Connection Properties dialog box, where you can modify all sorts of connection properties, including when the Access data’s refreshed in the Excel worksheet and how the connection is made.

The image below shows you a new Northwind Customer Orders workbook after importing both the Customers and Orders data tables from the sample Northwind Access database as new data tables on separate worksheets. When the two data tables were imported, Excel automatically added two new worksheets (Sheet2 and Sheet3) to the workbook, while at the same time importing the Customers data table to Sheet2 (which I renamed Customers) and the Orders data table to Sheet3 (renamed Orders). Sheet1 (which was blank) was deleted prior to taking the screenshot.

Customers worksheet with the data imported from the Access Customers data table in the sample Northwind database.

The following image shows same new workbook, this time with the Orders worksheet selected and the Manage Relationships dialog box open (by clicking the Relationships button on the Data tab or pressing Alt+AA). When Excel imported these two data tables, it automatically picked up on and retained the original relationship between them in the Northwind database, where the CustomerID field is the primary key field in the Customers data table and a foreign key field in the Orders data table.

Orders worksheet with the data imported from the Orders data table in the sample Northwind database showing the relationship with the Customers table.

After importing the external data into one of your Excel worksheets, you can then use the Filter buttons attached to the various fields to sort the data and filter the data.

After you import data from an external source, such as a Microsoft Access database, into a worksheet, Excel automatically displays a Queries & Connections task pane with two tabs: Queries, which displays the source(s) of the data imported into the current workbook, and Connections, which displays their connection to the workbook Data Model (and to each other if there are multiple sources and they are related to each other). If this task pane is not currently displayed in the current worksheet, click Data→  Queries & Connections (or press Alt+AO) to redisplay it.

Excel keeps a list of all the external data sources and data queries you make to the current workbook so that you can reuse them to import updated data from another database or web page. To quickly reconnect with a data source, click the Recent Sources button on the Data tab (Alt+PR) to open the Recent Sources dialog box where you click the name of the external file before you select the Connect button. To reuse a query, click the Existing Connections button on the Data tab (Alt+AX) to open the Existing Connections dialog box to access this list and then click the name of the query to repeat before you click the Open button.