Data imported from Excel often contains records that should be in more than one table. To import it properly, the proper data structure needs to be established, and then data will be put where it belongs.
Figure 1 shows an Excel worksheet containing data for products ordered in April 2018. Each month is on a different sheet within the workbook. Importing the data from this workbook into Access will make reporting easier, allow for more flexibility, and make it simpler to correct or minimize mistakes in the data, such as misspelled names.
Since each sheet in the Excel workbook uses the same column headings, it might be tempting to simply create a table in Access containing the same fields-but the way data is organized in Excel isn't necessarily the best way to organize it in Access. Leaving it in one table might lead to challenges later. So let's look at the data.
The first column contains the day of the month that the order was taken, but the data isn't formatted as a date-it's simply a number. To get the actual date, you'd need to combine the value in column A, the sheet name (Apr), and the year referenced in the file name (2018).
Next come the customer code and customer name. Column D indicates the type of record for any customer that isn't an individual. Serial # identifies what product was purchased, Price indicates how much the customer paid, Mfg gives the date the product was manufactured, and SRP is the suggested retail price.
Already, we're starting to see potential issues with the data file. Columns C and D describe the customer, not the order. And, as you can see in rows 1 and 2, the name used for a particular customer isn't always consistent, while information isn't always added to Column D. The note in Column I also appears related to the customer rather than the order.
Data structure is an important element of database design. It refers to the collection of tables and fields that define how your data looks. Consider, for example, a simple ordering process where Customer ABC buys XYZ Product as part of Order 123. Although data may come in with all the detail for each of those items, customers, products, and orders are three very different objects. Unlike how they're organized in the Excel workbook, they should be separated into their own tables. Each object is in a table with a list of like objects, and each row, or record, contains a set of fields that describes the characteristics for that object.