Create a query that would convert the order year into a decade and count the orders

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.

Attached file(s):

WEEK2_ACCESS2000.mdb  View File