Using Data from Access in Excel

Shopping cart

0 Product(s) in cart

Total $0.00

» Checkout

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

• Export or Import data from Access for Excel
• Open Excel from Access
• Open Access data from Excel

Export/Import Access Data

1. Make sure fields you select are numeric by choosing design view and changing field type to number:
   

2. Select FILE-SAVE AS/Export,
   
   

3. Specify Excel format in "Save as type" box at bottom of screen and EXPORT.
   
   

4. Open the Excel file, select the desired data. (Note if numeric fields are left aligned, not right, the Access fields are "text" not "number" and will not allow you to run the macros. Go back to Access and change the field layout to numeric):
   
   

5. Run the desired chart:
   
   

Using QI Macros In Microsoft Access

1. Once you have a database, you can analyze the database by linking Excel and using the QI Macros.
   
   

2. The database might look like the following:
   
   

3. To use the QI Macros, you have to ensure that the data type of numeric fields is number, not text. Use View-Table Design to change the data type of all numeric fields to 'number'.
   
   

4. Then, select the table, query, form, or report you want to save and load into Excel. From the Access menu select Tools-OfficeLinks-Analyze It With MS Excel.
   
   

5. This will open Excel and create a .XLS file, which you can chart and graph using the QI Macros.
   
   

   ---

   Using Microsoft Access Data from Excel

   1. To link to Access data and get information, you'll need to select Tools-Add-ins and check AccessLinks Addin and MS Query Add-in. If they aren't visible, choose browse and look in Library. If they still aren't visible, you'll need to copy them in from your installation CD.
      
      

   2. Then, from anywhere on your worksheet, you can select Data-Get External Data
      
      

   3. This will start MS Query. Select MS Access Database.
      
      

   4. Then select the database you want to use.
      
      

   5. Then, using the Query Wizard, click on the > sign to select the fields you want.
      
      

   6. Then the Query Wizard will let you filter any fields, or just click Next.
      
      

   7. You can change the sort order, or just click Next:
      
      

   8. Then, click Finish to complete the query:
      
      

   9. Excel will ask you about getting the data and where to put it on the spreadsheet:
      
      

   10. Excel may prompt you again to select a database:
       
       

   11. Excel will then import the data.
       
       

   12. You can then select Data-Refresh Data to update the data as required.
       
       

   13. Excel will go to the Access database and update the spreadsheet.
       
       

Download the FREE 30-day Evaluation copy of the QI Macros Excel SPC Software for Six Sigma

Download
QI Macros
30-Day Trial Now!

Or Buy It Now!