Last month, each sheet in the workbook was appended to a table named importOrders. Then we used an update query to properly fill in the DateOrder field. Now it’s time to put the imported data into the appropriate places within the database—in other words, we’ll shuffle it to different tables depending on what the data describes. Figure 1 shows the relationships diagram with the import table and the three tables where data will go: Productz, Customerz, and Orderz. The fields are color-coded to their destination table.
CUSTOMERS
This month, we’ll focus on the customer-related data. This will be Code, Name, and Type. The customer code is unique for each customer. The primary key for the Customerz table is an AutoNumber called CustomerID, and the CustCode field has a unique index to prevent any duplication.
Make a new query based on the importOrders table. In Design view, add Code, Name, and ID to the query by design grid. Click the Totals icon to display the Total line in the grid. Using the drop-down menu, set the Total line to Group By for Code. For Name, set it to Max. Because information in Excel was entered each time an order was taken, sometimes the customer name was different for the same customer code. Using Max here eliminates duplicates and ensures that the field will contain at least one of the customer name values associated with the customer code.
Using Max for a field will work most of the time, but not always. For instance, “Allen Sistr” was a misspelling of the “Allen Sisters.” After the query is run and the customer records are created, it will be necessary to manually verify and correct these fields. To eliminate the problem moving forward in Access, forms can be designed so the customer name is picked from a list rather than entered manually.
For ID, set the Total to Count. This will show the number of orders that each customer has placed, which is interesting to see when you are viewing data. Switch to Datasheet view to see the query results.
There’s still one more customer-related field: Type. This field indicates the type of business if the customer is a company. Return to Design view and add Type to the grid. Choose Max in the Total row. The query now includes the three columns we will append to the Customerz table.
APPEND QUERY
In Design view, go to the Design ribbon and click Append to change the query type. This will open the Append dialog box. When prompted for the table to append to, choose Customerz. Access will add an Append To row in the query by design grid. When the field names in the destination table are the same as the fields used in the query, it will match the fields automatically. In this example, the names are different, so no value will appear in the Append To line.
Field names should be descriptive and shouldn’t be a term that could be misinterpreted. They also shouldn’t contain spaces or special characters, with the exception of the underscore. It’s also important to avoid using reserved words, such as “Name” and “Type.” Allen Browne provides an excellent reference to check for reserved words at bit.ly/2Yn6dbY.
In the Append To cell under Code, choose CustCode. Under Name, choose CustomerMain, and under Type, choose CustType. Leave ID blank as that field won’t be appended. There isn’t a place in the Customerz table to store the number of orders, and the value will change over time anyway. Save the query as “qApp_Customerz.”
Close the query and then reopen it in Design view. The ID field will be gone since it isn’t being appended anywhere. For now, though, let’s put the information in a note field. In a blank column, enter Count([ID]) & " order" & IIf( Count([ID]) <>1, "s", "") in the Field cell. Append it to NoteCust. Because no field alias was specified, Access uses “Expr1” for the calculated field name. You can change it to something else if you wish or leave it as is since only the data matters, not the calculated field name. Save the query again. Figure 2 shows the query by design grid.
Switch to Datasheet view for one last look at the data that will be added when the query is run, as shown in Figure 3. To execute the query and add the records, go to the Design or SQL view and click the Run icon. Access tells you that 301 records will be appended.
If you run the query again, you’ll get an error message and the records won’t be appended since the customer code has a unique index and all the codes are already there. Close the query and open the Customerz table. You can see the records have been added.
Download this month’s database file: SF1906_ShuffleData
SF SAYS
A unique index on a field will prevent duplicate values from going into a table.
June 2019
