database
import and analysis of data
creating meaningful queries
To interpret the data accurately and to identify some significant trends I will need to extract some useful information. To do this I will need to design some meaningful queries that will produce clear and accurate results. The first thing I had to do was think of some relevant questions to ask the database. I thought of the following seven questions:
- What CD was the most popular with men?
- What Artist sold the most records overall?
- Which record sold the most over the summer period in 2005?
- What was the best seller Christmas week 2005?
- Where in the country were the most CDs brought last year?
- Who ordered the most CDs - men or women?
- In what month were the most CDs sold?
Now the hard bit is changing these questions into SQL queries to search the database. Thankfully Access makes this fairly easy. Below I will explain how I created a query for each question.
What CD was the most popular with men?
In this query I will be searching the database strictly for data about men. To do this I will require the field 'NewCust_Title' to be searched with the criteria "Mr". The main subject of this query will be the amount of CDs. Therefore I need to total the amount sold by each different CD and have Access show the title of the CD next to the total. So I will require the use of the 'CD_Title' field twice. The first time it will be grouped the second time it will be used to count the number sold. Below is this query in design view:
And below are the results. They are shown here just to give you an idea of the output; they will be interpreted later:
As you can see the results are very clear and will be easy to interpret, for the rest of the queries I will just be focusing on the design so I will not be showing the results of the query when run in Access.
Which Artist sold the most records overall?
For this query I will be counting the number of orders received for each artist. Therefore I will be using the 'Order Number' field to count the total of received orders and I will be using the 'Artist' field to be grouped. Below is a picture of this query in design view:
Click here to view the results of this query when run in Access.
What record sold the most over the summer period in 2005?
In this query I will be using some slightly more complicated criteria. This is due to the fact that I need to zoom in on only a relatively small selection of the entire data. I only require to examine the orders received in the summer months - June, July and August. Therefore I needed to use the 'Order Date' field with the following logical operators: "Between" and "And". I have also used multiple sorting in this query, this is shown clearly below:
I also needed to count the number of orders recieved for each particular CD, so I used the 'Order Number' field to count the totals and the 'CD_Title' field to group the results.
What was the best seller Christmas week 2005?
To find out what was the best selling Christmas record I used the same fields that I used in the previous query. Except this time, the date searched for in the 'Order Date' criteria box was the week leading upto Christmas (w/c 19/12/2005).
Above is a screenshot of "qryXmas_best_seller" in design view.
Where in the country were the most CDs brought last year?
This query is much more simple than the previous two. It does make use of both of the related entities though. The 'County' field is used to group the data and the 'CD Number' field is used to count the amount of records sold in each county.
Above is a screendump of this query in design view.
Who ordered the most CDs - men or women?
This query only makes use of the Order table. It uses the NewCust_Title field to group the data and the Order number field to count it. Below is what this query looks like in design view:
In what month were the most CDs sold?
The results from this final query will allow me to determine which month was the most successful sales wise and will help me to discover any significant trends later. For this query I used both the CD entity and the Order entity. From the CD entity I used the CD Number to be counted, and I used the order date to be grouped and to be sorted into ascending, I also used the month function, with month number and month name.
Above is a screenshot of the final query.
Click next to continue...