FROM EXCEL TO ACCESS
One case where you would need a string of dates is when a business is on a 13-period year or a 4-4-5 calendar. This comes in handy whenever reporting periods for sales queries don’t use the traditional monthly dates. Figure 1 shows Excel data for a 4-4-5 reporting calendar. Rows 3-13 in column B contain formulas that add 1 to the value in the previous row’s End Date. For example, the formula in cell B3 is =C2+1; in cell B4, it’s =C3+1; and so forth. Thus, you only have to enter the beginning date in cell B2 and the end dates for each period in column C.
Once you have the dates in an Excel worksheet, you can easily import them into Access as a table to organize your reporting. Give the table a descriptive name, such as Reporting Period, and include it in your sales data query. Dates in the table can determine the “between” dates for your query. Use the Period ID to set the period. It takes three easy steps:
- Create the Excel file with the dates, and save it.
- Import the Excel file to an Access table.
- Use the table in various queries to set criteria for query data dates.
When you use this table, you wouldn’t link it to the other data tables after adding it to the query. Instead you would use the Start Date and End Date fields as criteria under the Date field of the detail data table source, such as Sales or Production Details. In other words, on the Criteria line for the Date field of the Sales data, you would enter: Between [ReportingPeriod]![Start Date] And [ReportingPeriod]![End Date].
When importing small tables from Excel, you can bypass the import steps with a simple shortcut: Highlight the data range in Excel, and copy it. In Access, without any objects open, press Crtl+V to paste the data. Access will create a new table named Sheet1, which you can then rename.
FROM ACCESS TO EXCEL
There are also times when it might be easier to go from Access to Excel. For instance, you might need to share data with someone who isn’t used to Access. In that case, you can export the data from a table using the Export Wizard. You can even save the steps if it’s a process you will do frequently.
As with the copy-and-paste shortcut above, a similar strategy works if you want to get a small table out of Access and into Excel. With the table open, use Ctrl+A to select all the data. Then press Ctrl+C to copy. Switch to a blank worksheet in Excel, and click on the cell where you want your data to begin. Press Ctrl+V to paste.
Another example of when you might go from Access to Excel is with reports. I have worked with people over the years who don’t like all the work it can take to get an Access report to look just right. Each month, they export the raw data to Excel in a basic format and then use a macro in Excel to format reports.
The process takes three easy steps:
- Export or copy and paste the data to Excel.
- Run your recorded Excel macro for formatting.
- Make any unique format changes for that period, then save the Excel file.
Recording a macro in Excel is easy. Start the macro recorder, complete the steps you would normally do to format the data, and then shut the recorder off. Give the macro an appropriate name you can execute each month, and that’s it! You have your reports in Excel.
The Record Macro button is located on the Developer Tab in Excel. You might need to activate it so that it gets displayed. Go to File, Options, Customize Ribbon. In the list of Main Tabs, click on Developer so that it’s checked. When you begin to record, be prepared to complete the information requested on the Record Macro Dialog (see Figure 2). You will want to give the macro a descriptive name and store it in the Personal Macro Workbook so you can use it in any workbook.
SF SAYS
Take advantage of software strengths when developing your database strategy. Access is great at push-button automation, but it can be time-consuming to create reports. When sharing data, other users might be more comfortable working in Excel.
Download this month’s database here: SF_JUL_2015.
July 2015
