Results 1 to 5 of 5
  1. #1
    dododo is offline Novice
    Windows Vista Access 2003
    Join Date
    Jun 2011
    Posts
    26

    Angry How to get product lowest price and product name?

    Hi everyone,

    I want to write sql in access to get lowest price and product name.

    for example, I have tblproduct(ProductID,ProductName,Price)

    ProductID ProductName Price
    001 A 10
    002 B 20
    003 C 7

    I want to get result like this 003 C 7

    I already tried this code: select productid,productname, min(price)
    from tblproduct
    group by prodcutid,productname

    It didn't get only lowest price but it display all data.

    Please help me....

  2. #2
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Sort price ascending and query to return TOP 1 .
    SELECT TOP 1 productid,productname, price from tblproduct ORDER BY tblproduct.Price;

  3. #3
    dododo is offline Novice
    Windows Vista Access 2003
    Join Date
    Jun 2011
    Posts
    26
    Quote Originally Posted by amrut View Post
    Sort price ascending and query to return TOP 1 .
    SELECT TOP 1 productid,productname, price from tblproduct ORDER BY tblproduct.Price;
    I don't understand your answer.

    Could you kindly write full code for me? Thanks

  4. #4
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    -Go to Create > Query Design
    -Add the tblProducts to the Query
    -Close the dialogue box
    -Double click productid,productname, price or drag them to query grid
    -Sort price Ascending
    -In the query's property sheet, change the "Top Values " from All to 1
    Run the query and it should return a single row.

  5. #5
    dododo is offline Novice
    Windows Vista Access 2003
    Join Date
    Jun 2011
    Posts
    26
    Thank you so much for your help. Now I can do that.

    Quote Originally Posted by amrut View Post
    -Go to Create > Query Design
    -Add the tblProducts to the Query
    -Close the dialogue box
    -Double click productid,productname, price or drag them to query grid
    -Sort price Ascending
    -In the query's property sheet, change the "Top Values " from All to 1
    Run the query and it should return a single row.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Product Database
    By W-technologies in forum Database Design
    Replies: 2
    Last Post: 03-14-2013, 08:31 PM
  2. Replies: 3
    Last Post: 02-26-2013, 05:07 AM
  3. product hierarchy
    By Lata in forum Access
    Replies: 13
    Last Post: 08-03-2011, 02:56 AM
  4. Total by product
    By Alexpi in forum Queries
    Replies: 1
    Last Post: 05-24-2011, 03:19 PM
  5. Replies: 1
    Last Post: 10-06-2009, 02:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums