Using the database attached to Lecture 2, let's say you are interested (let's say as the business owner) to see how old the books are that people order (going by their publication date).
First, you'd need to create a query that would convert the order year into a decade and count the orders:
SELECT books.title, CInt(Left([publication_year],3))*10 AS DECADE, order_lines.quantity
FROM books INNER JOIN order_lines ON books.isbn = order_lines.isbn;
...The function "CInt() converts text values into integers (year is stored as a text value).
Anyhow, for participation credit, cut and paste the above SQL into a query in the Lecture 2 database. Name the query BOOK_DECADE.
Then, create the cross tab query following these steps:
in the queries TAB click: New then Cross Tab query wizard then OK
In "View", click "Queries" and then Select query BOOK_DECADE and click NEXT
Add the TITLE to "Selected Field and click NEXT
Highlight DECADE and click NEXT
UNCLICK "Yes, include row sums"
Click Quantity under "Fields:" and click "Sum" under "Functions:" and click NEXT
Then click FINISH
When you are done, cut and paste the SQL (for this query) and paste into a word document.
Attachment:- WEEK2_ACCESS2000.zip