Reference no: EM13949709

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