Before transferring data from excel to an existing table datasheet in access, make sure that

After establishing the proper data structure for importing data from Excel, the next step is to import the actual data. A process using append and update queries can help you combine multiple Excel worksheets into one table in Access and update the data appropriately.

Last month, we imported the first month of data from an Excel workbook into a table called “importJan.” Before importing additional months from the Excel workbook, we need to update the January records so they have an actual order date. The original worksheet only included a column (Dy) indicating on which day of the month an order was placed.

Open the importJan table in Design view. Add a field called “DateOrder” with a data type of Date/Time. Save the table and close it. Since we will eventually append more months to this table, rename the table to “importOrders.” To do this, right-click on the name in the navigation pane and choose Rename from the shortcut menu. Type in the new name and press Enter.

ADJUST IMPORTED DATA

Create a new query based on the importOrders table. Add the Dy field to the grid. In the second column of the grid, enter CalcDate: DateSerial(2018,1,[Dy]) to create a calculated field. CalcDate is the new field name, and the DateSerial function creates a date when given a numeric year, month, and day. We know the data came from January 2018, so those arguments are coded directly into the function, and the Dy field is used to get the specific day. Switch to Datasheet view to verify that the calculated date is correct (see Figure 1).

Now change the query type. Go back to Design view. In the Query Type group on the Design tab, select Update. Add the DateOrder field to the grid. In the Update To row for DateOrder, add the expression we just verified: DateSerial(2018,1,[Dy]). Save the query as “qUp_Import_DateOrder.” Run it. A message will appear asking if you want to update 6 rows. Click Yes. Open the importOrders table to verify that the data has been updated.

IMPORT NEXT SHEET

Now we’ll import the next sheet in the Excel workbook. In the Import & Link group on the External Data tab, choose Excel. (If that isn’t visible on the ribbon, you may need to click on the New Data Source drop-down button and select From File, Excel.) This launches the Get External Data wizard. Browse to the file you want to import, EXCEL_Orders_2018_Colored.xlsx, and click Open.

In the wizard dialog, choose “Append a copy of the records to the table” and then select the importOrders table from the drop-down. Click OK. The next dialog box displays the list of worksheets available to import. Choose the “Feb” worksheet and click Next. The next dialog will show a preview of the data and a checkbox for “First Row Contains Column Headings.” It should already be selected, so click Next. The last dialog box will have a field indicating the data should be imported to the importOrders table. Click Finish. A new dialog will ask if you want to save the import steps. We don’t want to, so leave it unchecked and then click Close. Open importOrders to verify that the February data has been added.

MODIFY UPDATE QUERY

Like with the January data, the OrderDate needs to be updated for the newly imported data. Open qUp_Import_DateOrder in Design view. Since this data is for February, change the Update To expression under DateOrder to DateSerial(2018,2,[Dy]).

The first time we ran this query, all the DateOrder fields needed to be filled. Now we only want to change the new records, so enter Is Null in the DateOrder’s criteria. Now only blank values in that field will be updated.

Figure 2 shows the modified query in Design view. Save the query and run it. Open the importOrders table to verify that everything is correct.

REPEAT

For each remaining sheet in the workbook, repeat the import process and update the order date. First, import and append the worksheet data to the importOrders table. Second, modify the DateSerial expression in the Update query to reflect the appropriate month, then run it. Since some months are missing in the Excel workbook, be sure to match the numeric month to the sheet name when updating the DateSerial expression. When all the worksheets are imported, you should have 368 records in the table.

Now that all the data is imported into Access, it needs to be shuffled to the proper tables. We’ll do that next month.

Download the files for this month: SF1905_AppendUpdateImportData

SF SAYS

After importing the first sheet in an Excel workbook, append additional sheets to the same table and use an Update query to calculate values.

Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. Visit www.MSAccessGurus.com for information. Share your perspective! Add your comments about the article below.

You may also like

How many Access tables can be imported into Excel at a time?

How does Excel treat criteria entered on the same row?

Which error code indicates that the formula contains an unrecognized entry?

How does the subtotal command format the list data?