1 / 40

Integrating Access Data: Import, Link, and Export

Learn how to import data from another Access database, link to tables in another database, import data from a text file, and export Access data to a text file. Also, discover how to summarize data in a PivotTable and PivotChart.

johnlwilson
Download Presentation

Integrating Access Data: Import, Link, and Export

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Access 2010 Level 2 Unit 2 Advanced Reports, Access Tools, and Customizing Access Chapter 8 Integrating Access Data

  2. Integrating Access Data Quick Links to Presentation Contents • Import Data from Another Access Database • Link to a Table in Another Access Database • Import Data to Access from a Text File • Save and Repeat Import Specifications • CHECKPOINT 1 • Export Access Data to a Text File • Save and Repeat Export Specifications • Publish and View Database Objects as XPS Documents • Summarize Data in a PivotTable and PivotChart • CHECKPOINT 2

  3. Import Data from Another Access Database • Data stored in another Access database can be integrated into the active database by importing a copy of the source object. • You can choose to copy multiple objects as well as duplicate the relationships between tables.

  4. Import Data from Another Access Database…continued To import objects from an Access database: • Open the destination database. • Click the External Data tab. • Click the Import Access database button in the Import group. • At the Get External Data - Access Database dialog box, click the Browse button. • Navigate to the drive and/or folder. • Double-click the source file name. • Click OK. continues on next slide… Get External Data – Access Database dialog box

  5. Import Data from Another Access Database…continued • At the Import Objects dialog box, select the desired import object(s). • Click OK. • Click Close. Import Objects dialog box

  6. Import Data from Another Access Database…continued To display import options: • Display the Import Objects dialog box. • Click the Options button. import options

  7. Import Data from Another Access Database…continued • You can also copy an object by opening two different Access windows—one containing the source database and another containing the destination database.

  8. Link to a Table in Another Access Database To link to a table in another database: • Open the destination database. • Click the External Data tab. • Click the Import Access database button in the Import & Link group. • At the Get External Data - Access Database dialog box, click the Browse button. • Navigate to the drive and/or folder. • Double-click the source file name. • Click the Link to the data source by creating a linked table option. • Click OK. continues on next slide… Link to the data source by creating a linked table option

  9. Link to a Table in Another Access Database…continued • At the Link Tables dialog box, select the desired table(s). • Click OK. Link Tables dialog box

  10. Link to a Table in Another Access Database…continued • When two tables in different databases arelinked, the data resides in the source database. The table in the destination database is considered to be a copy. • Opening thelinked table in the destination database causes Access to update the it with the information from the source table.

  11. Link to a Table in Another Access Database…continued • In most cases, you will import data into Access from another Access database or from anexternal source. • If you know that the data will require frequent updates, you should link the data to the external data source so that you can enter changes in one central location. • For example, if you wanted to combine data from multiple databases to create a table called Inventory, you would first import the data from all of the different data sources and then link each table back to its source file.

  12. Link to a Table in Another Access Database…continued To refresh a link: • Click the External Data tab. • Click the Linked Table Manager button in the Import & Link group. • At the Linked Table Manager dialog box, click the linked table. • Click OK. • Navigate to the drive and/or folder. • Double-click the source database file name. • Click OK. • Click the Close button. Linked Table Manager dialog box

  13. Import Data to Access from a Text File • A text file is often used to exchange data between programs since the file format is recognized by most applications. • Text files contain no formatting. They consist of only letters, numbers, punctuation symbols, and a few control characters. • Delimited file format is a text file format that uses a tab character to separate fields. • Comma separated file format is text file format that uses a comma to separate fields.

  14. Import Data to Access from a Text File…continued To import data from a comma separated text file: • Click the External Data tab. • Click the Import text file button in the Import & Link group. • At the Get External Data - Text File dialog box, click the Browse button. • Navigate to the drive and/or folder. • Double-click the file name with the.csv extension. • Click OK. continues on next slide… Get External Data – Text File dialog box

  15. Import Data to Access from a Text File…continued • At the first Import Text Wizard dialog box, click Next. continues on next slide… first Import Text Wizard dialog box

  16. Import Data to Access from a Text File…continued • At the second Import Text Wizard dialog box, click the First Row Contains Field Names check box. • Click Next. continues on next slide… second Import Text Wizard dialog box

  17. Import Data to Access from a Text File…continued • At the third Import Text Wizard dialog box, choose the data type. • Click Next. continues on next slide… third Import Text Wizard dialog box

  18. Import Data to Access from a Text File…continued • At the fourth Import Text Wizard dialog box, choose the primary key field. • Click Next. • At the last Import Text Wizard dialog box, click Finish. fourth Import Text Wizard dialog box

  19. Save and Repeat Import Specifications To save import specifications: • At the last Get External Data dialog box, click the Save import steps check box. • If necessary, edit the name in the Save as text box. • Type a description in the Description text box. • Click the Save Import button. Save import steps check box

  20. Save and Repeat Import Specifications…continued To repeat the import process: • Click the External Data tab. • Click the Saved Imports button in the Import & Link group. • At the Manage Data Tasks dialog box, click the desired import name. • Click the Run button. Manage Data Tasks dialog box

  21. CHECKPOINT 1 • The Import Access database button is located in this tab. • Home • Create • External Data • Database Tools • This type of file is often used to exchange data between programs. • converted • data • encrypted • text Answer Answer Next Question Next Question • When you do this to a table, the source data does not reside in the destination database. • copy • paste • link • embed • The Saved Imports button is located in this tab. • Home • Create • External Data • Database Tools Answer Answer Next Question Next Slide

  22. Export Access Data to a Text File • The Export group in the External Data tab contains buttons you can use to export Access data from a table, query, form, or report to other applications such as Excel or Word. • If you want to work with Access data in a program that is not part of the Microsoft Office suite, click the More button in the Export group to see if a file format converter exists for the application you want to use.

  23. Export Access Data to a Text File…continued To export data as a text file: • Select the object in the Navigation pane. • Click the External Data tab. • Click the Export to text file button in the Export group. • At the Export - Text File dialog box, click the Browse button. • Navigate to the desired drive and/or folder. • Change the file name. • Click the Save button. • Click OK. continues on next slide… Export - Text File dialog box

  24. Export Access Data to a Text File…continued • At the first Export Text Wizard dialog box, click Next. continues on next slide… first Export Text Wizard dialog box

  25. Export Access Data to a Text File…continued • At the second Export Text Wizard dialog box, choose the delimiter character. • If appropriate, click the Include Field Names on First Row check box. • If appropriate, choose the Text Qualifier character. • Click Next. • At the last Export Text Wizard dialog box, change the Export to File path and/or name. • Click Finish. • Click the Close button. second Export Text Wizard dialog box

  26. Save and Repeat Export Specifications To save the export specifications: • At the last Export - Text File dialog box, click the Save export steps check box. • If necessary, edit the name in the Save as text box. • Type a description in the Description text box. • Click the Save Export button. Save export steps check box

  27. Save and Repeat Export Specifications…continued To repeat the export process: • Click the External Data tab. • Click the Saved Exports button in the Export group. • At the Manage Data Tasks dialog box, click the desired export name. • Click the Run button. Manage Data Tasks dialog box

  28. Publish and View Database Objects as XPS Documents To publish anobject as an XPS document: • Select the object in the Navigation pane. • Click the External Data tab. • Click the PDF or XPS button. • If necessary, navigate to the desired drive and/or folder. • Change the file name as needed. • Change the Save as type to XPS. • Click the Publish button. • Click the Close button. Publish button

  29. Publish and View Database Objects as XPS Documents…continued • XPS stands for XML Paper Specification, which is a fixed-layout format with all formatting preserved. When an XPS file is shared electronically, the recipients of the file see the original formatting and cannot easily change the data. • The Save as type option also includes PDF. PDF stands for Portable Document Format, another fixed-layout format that preserves original formatting for file-sharing purposes.

  30. Summarize Data in a PivotTable and PivotChart • A PivotTable is an interactive table that organizes and summarizes data based on fields you designate as the row and column headings. • PivotTables are useful management tools since you can analyze data in a variety of ways. You can filter a row, a column, or another filter field and instantly see the change in results.

  31. Summarize Data in a PivotTable and PivotChart…continued To create a PivotTable: • Open the table or query. • Click the View button arrow in Views group in the Home tab. • Click the PivotTable View option at the drop-down list. continues on next slide… PivotTable View option

  32. Summarize Data in a PivotTable and PivotChart…continued • Drag the fields from the PivotTable Field List box to the desired dimmed text locations. PivotTable Field List box

  33. Summarize Data in a PivotTable and PivotChart…continued • The main advantage to using a PivotTable is that it gives you the ability to analyze data for a variety of different scenarios. • Use the plus and minus symbols that display in a row or column heading to show or hide data. • Use the down-pointing arrow (called the filter arrow) that displays next to a field name to filter the PivotTable by one or more values in the field.

  34. Summarize Data in a PivotTable and PivotChart…continued • When you create a PivotTable in a query or table, the PivotTable settings are saved and become part of the table or query. • When you open a table or query in which you have created a PivotTable and then switch to PivotTable view, the table or query displays with the PivotTable settings you created. • If you want to view different fields or perform other functions in PivotTable view, you have to edit the settings.

  35. Summarize Data in a PivotTable and PivotChart…continued To create a PivotTable form: • Click the desired object in the Navigation pane. • Click the Create tab. • Click the More Forms button in the Forms group. • Click the PivotTable option at the drop-down list. • Click twice on the Field List button in the Show/Hide group in the PivotTable Tools Design tab. • Drag and drop the field(s). PivotTable option

  36. Summarize Data in a PivotTable and PivotChart…continued • A PivotChart performs the same function as a PivotTable. However,Access displays the source data in a graph instead of in a table or query.

  37. Summarize Data in a PivotTable and PivotChart…continued To create a PivotChart: • Open the table or query. • Click View button arrow in the Views group in the Home tab. • Click the PivotChart View option. PivotChart

  38. CHECKPOINT 2 • The Export to text file button is located in this tab. • Home • Create • External Data • Database Tools • This is an interactive table that organizes and summarizes data. • PivotSummarize • PivotTable • PivotChart • PivotData Answer Answer Next Question Next Question • This file format is a fixed-layout format with all formatting preserved. • XPS • PSX • XPD • XDF • This displays the source data in a graph. • PivotSummarize • PivotTable • PivotChart • PivotData Answer Answer Next Question Next Slide

  39. Integrating Access Data Summary of Presentation Concepts • Import data from another Access database • Link to a table in another Access database • Determine when to import versus link from external sources • Reset or refresh links using Linked Table Manager • Import data from a text file • Save import specifications • Export data in an Access table or query as a text file • Save and run export specifications • Save an object as an XPS document • Summarize data by using a PivotTable • Summarize data by using a PivotChart

More Related