MS Access 2007 - query problem

Barti

Hi, I'm quite new to Databases in general and SQL so I'm not exactly sure how to handle this query I've made.

My code is as follows:

[SIZE=1]SELECT CUST_NAME, MAX(AMOUNT) AS TOTAL_PURCHASES, YEAR

FROM (
          SELECT C.CUST_NAME AS CUST_NAME, SUM(O.QUANTITY) AS QUANTITY,
                     SUM(B.PRICE*O.QUANTITY) AS AMOUNT, YEAR(O.DATE) AS YEAR

          FROM CUSTOMERS AS C, ORDERS AS O, BOOKS AS B

          WHERE C.CUST_ID = O.CUSTOMER_ID AND O.BOOK_ID = B.ISBN

          GROUP BY YEAR(O.DATE), C.CUST_NAME
     )

GROUP BY CUST_NAME, YEAR

ORDER BY YEAR;[/SIZE]

What I want to do is end up with a table with three rows and three columns. The columns shall be CUST_NAME, TOTAL_PURCHASES, YEAR. The idea of the query is to obtain the three highest paying customers per year. By excluding the CUST_NAME in the first SELECT and the last GROUP BY I can get the top three purchases per customer per year but that leaves me without the company's name which is what I need.

I appreciate any help anyone can provide on this.
Thanks.

akamossie

looking into the code, it looks fine, as i have not seen anything mentioned about company's name.
its a little confusing here, like you have three columns and rows whhere you have three attributes, is the company name in another table, are your trying to join them together??

I am a little rusty with DB haven't used in years, but what i see you are missing to join a table assuming there is one for company's name!!

Barti

Sorry, I realised only now that I said company name rather than customer name. What I want to end up with is the customer name and the amount they purchased per year.

I mixed up company and customer because my database is based on me being a warehouse and other companies buying from me, so are therefore my customers.

By only doing the inner SELECT I get the following output table:



So now I was just wondering how to get from that down to only the top purchaser for each year?