Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DCount (Access 2000)

    i want to arrange my list of products not in alphabetical order, but in order depending on the number of the orders given for this product. Could somebody help me ?I use a copy of the Nortwind database shipped with Access.
    My query is something lke that :
    SELECT products.Productid, products.Name, orders.orderid
    FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid;
    I want to use the DCount function in order to arrange the products in the order of the frequency of their selection.Is it possible to do it ?
    I am looking forward eagerly for the possible replies.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (Access 2000)

    Do you need the query to be editable, or is it just for display purposes?

  3. #3
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (Access 2000)

    Thank you for the reply.Actually i need it for both purposes.In the one case i need it to build a report for display purposes and in the other case i need it to build a form for editing purposes
    Canett

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (Access 2000)

    Here is a simple version for use as row source of a combo box or list box, or for a report. It is not editable:

    SELECT Products.ProductID, Products.ProductName
    FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
    GROUP BY Products.ProductID, Products.ProductName
    ORDER BY Count(*) DESC

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (Access 2000)

    And here is an editable version. It will probably be slower:

    SELECT Products.ProductID, Products.ProductName
    FROM Products
    ORDER BY Val(Nz(DCount("*","[Order Details]","ProductID=" & [ProductID]),0)) DESC

  6. #6
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DCount (Access 2000)

    Thank you so much.I have copied into the query grid,and works excellent,it shows in descending order the products by the number of the their selection!
    Why cant i do it in an sql form? When i try to copy your code and write for example strsql = "..... my sql is not accepted and is turned red?
    It especially stops at "[order details]"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DCount (Access 2000)

    The version using DCount contains strings within quotes. The string strSQL is also surrounded by quotes. This causes a conflict - SQL cannot determine where strSQL ends. One solution is to use single quotes <code>'</code> instead of double quotes <code>"</code> within strSQL:
    <code>
    strSQL = "SELECT Products.ProductID, Products.ProductName " & _
    "FROM Products ORDER BY " & _
    "Val(Nz(DCount('*','[Order Details]','ProductID=' & [ProductID]),0)) DESC"
    </code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •