Using Export and Analyzing It with Microsoft Office Excel
Now that you have tried to import data, assume you need to go the other way and send data to Excel. This method has far fewer steps than the import method, but before attempting to export data, you need to answer a few questions. First, find out how many rows are being exported. If there are more than 65,536 rows, you need to break the table up into pieces before exporting it. Next, consider the importance of the data format. If the data must be in a particular format, you can export the data, have an Excel report read it, and present it in another sheet. Another option is to use VBA to automate Excel. In this case, neither of these is a problem.
What if you need to put date information into the amortization table and email it to someone as an Excel worksheet? Sure, you could copy and paste from Access into Excel, format it, put in the formulas, etc.—or you could take advantage of the power of a query in the database. Where possible, I attempt to draw comparisons between how you would handle something in Excel versus Access.
In Excel, to add a payment date field that refers to the payment number, write a formula and copy it down to the end of your data. As new rows are added, continue to copy down the formulas. There is nothing stopping you from putting different formulas into every cell or not putting formulas in some cells. This can lead to problems with consistency.
If you contrast that with the Access solution, you see how ...