Counting distinct in access RRS feed

  • Question

  • Hello all,
    I am trying to write a query in MS Access into order to create a report in Crystal report. I have this problem where I need to sort the 4 books in the tables below according to the highest quantity of book sold.In this scenario,
    Book C would be 1st in the table with a total quantity of 8.
    How do I write a query in access to perform such operation. I have to use DISTINCT to get each book and also count the quantity(e.g. Book C:5+8).
    But it seems that I can't use Count and distinct together in Access.

    Title      SalesNo Quantity
    BookA  2            2
    BookB  3            1
    BookC  4            5
    BookC  5            3
    BookD  6            1

    Hope u all can help!
    Sunday, June 3, 2007 1:40 PM

All replies

  • Distinct works on combination of all the fields included into your query. If you have Title and SalesNo, then you will get multiple distinct rows for the combination of the values in those two fields. If you need to calculate total for the Tile only, then you need to select and group based ONLY on that field. Another way is to leave it as is and then group the data inside of the report. In this case you will get details and will be able to group based on specific fields without using query.
    Monday, June 4, 2007 11:10 AM