database
import and analysis of data
importation and append queries
Now that I had a fully customised database prepared and tested for the CD sales data set, it was time to import the data into it. I started this process by firstly clicking on the 'File' menu, 'Get External Data' and then selecting 'Import' from the list. A new window appeared asking me to select the file I would like to import - I then navigated to the directory where the data set was stored and clicked the 'Import' button.
The Import Text Wizard then begun and the first page analysed the data and suggested that it was in a 'delimited' format. This means that it uses characters (such as commas or tabs) to separate each field. I agreed with Access' assessment so I pressed the 'Next' button. The resulting page then asked me to choose the type of character used to 'delimit' (to split up) the fields. In the data set I have been given 'the comma' had been used so I selected this from the list. There was also an option to use the first row to define the field names, this was true of the data set so I also checked this box.
I then continued to the next page of the wizard and it asked me where I would like to store the data. I selected the 'In a new table' option and clicked the 'Next' button. The subsequent page then informed me that I was able to make some adjustments to the properties of each field I was importing. I decided that I would leave the fields as they were because they will be not be staying in the table for very long so there was no reason to create settings for them. The next page asked me whether I wanted to define a primary key or not. I did not want to create a primary key so I selected the 'No primary key' option and continued on to the next page of the wizard. This was the final page and it asked me to name the table I was creating. I called the table 'Data set' and finished the wizard. This table was then generated by Access.
To split this data up and to get it into the customised entities that I had already created, I had to create two append queries to send the data from the 'Data set' entity to the 'Order' and 'CD' entities. I did this by clicking on 'Create a query in design view'. This opened a new window which asked me to add tables to be available for use in the query, I selected just the Data set table.
I then added from that entity the CD Number, CD_Title and the Artist fields. I then clicked the Totals button in the query design toolbar and made sure that the query would group the fields together. I then changed the query into an append query by clicking on the Query menu and selecting Append Query from the list.
A new window appeared asking me to choose the table to append the data to. I selected the CD table and pressed OK. I then saved the query and named it appropriately. I then repeated the whole process but this time I included the following fields in the query: Order Number, CD Number, Order Date, NewCust_Title, First Name, Last Name, Address Line 1, Town, County, Post Code, Card Type, Card Number and Expiry Date. And this time I appended them to the Order table.
Now was time to run the queries. Below is what the finished queries look like in Access:
I then double-clicked on the 'Update CD Table REAL' query and the following window appeared:
I clicked Yes and another window popped up...
I clicked Yes once again and then Access appended the data from the Data set to the CD table. I then double-clicked on the 'Update Order Table REAL' query and the same two windows appeared except this time on the second box 5529 rows where appended.
All the data was imported without any errors.
Click next to continue...