Results 1 to 13 of 13
  1. #1
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8

    "Cheapest supplier"query

    Good Afternoon
    I am not new to Access but in the past I mainly used it to prepare data from different sources and work on them with Excel.
    I am trying to create a query on a simple table that has 3 fields: "product code", "price" and "Supplier name"
    Both suppliers have the same products and all I need is a query that gives me the product, lower(st) price and the name of the supplier with this price.
    If I don't use the "supplier name" in the query I receive a list of mimimum prices, If I add the supplier the query looks same as the table (lists prises from both suppliers). Tried some sql and checked out all options in MS Access (that I could find) and still no idea.... It seems it must be a simple trick to do it but my knowledge of Access is too poor.
    Can anyone help?


    P.S. I really tried to find a post describing a similar problem but after 40 pages I gave up.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Post the sql code for the query you are working with.

  3. #3
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8
    After only replacing "group by" with "minimum" in a query "project view" it looks ilke this

    SELECT Pricelist.[Product code], Min(Pricelist.Price) AS MinimumOfPrice, Pricelist.Supplier
    FROM Pricelist
    GROUP BY Pricelist.[Product code], Pricelist.Supplier;


    I didn't save the codes for other tries over past two evnings. Tried tu build a clause with "clause creator but with the same result.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I mocked up a table with some sample data. You'll have to adjust table and field names.

    id ProdCode SupplierName Price
    1 abc Jones $230.00
    2 abc Smith $238.45
    3 xyz Jones $20.00
    4 xyz Smith $30.00
    5 acdb Jones $130.00
    6 acdb Smith $70.00


    Here's a first cut at a query to get suppliername, prodcode and min(Price) in ProdCode order.

    Code:
    Select suppliername
    , Prodcode
    , price from TestProdsTbl
    where price In 
    (
    SELECT  Min(TestProdsTbl.Price) AS MinOfPrice
    FROM TestProdsTbl
    GROUP BY TestProdsTbl.ProdCode)
    order by prodcode;
    Result:
    suppliername Prodcode price
    Jones abc $230.00
    Smith acdb $70.00
    Jones xyz $20.00

  5. #5
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8
    Many thanks. I will try later this evening (must return to my other duties now) and let you know how it works.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    On fly
    Code:
    SELECT pl.*
    FROM 
         PriceList pl INNER JOIN (SELECT pl0.ProdCode, MIN(pl0.Price) AS MinPrice FROM PriceList pl0 GROUP BY pl0.ProdCode) pmin ON pmin.ProdCode = pl.ProdCode AND pmin.MinPrice = pl.Price
    NB! In case several suppliers had price equal with minimal price for same product, there will be a row for every such supplier for this product.

  7. #7
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8
    I just had time and enough silence to sit and check. I imported some real data - 300 rows with prices from 3 suppliers. Named table and columns exactly as in your code. results were strange.
    In some cases it worked, in other some products were listed twice with two different prices and suppliers. I am sure the product codes are OK - with no "invisible signs" as they work when I make "cross query" with them. It must be Access, which is not able to perform this task...

  8. #8
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8
    SELECT pl.*
    FROM
    PriceList pl INNER JOIN (SELECT pl0.ProdCode, MIN(pl0.Price) AS MinPrice FROM PriceList pl0 GROUP BY pl0.ProdCode) pmin ON pmin.ProdCode = pl.ProdCode AND pmin.MinPrice = pl.Price

    I will try this code now :-)

  9. #9
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8
    Repeated post - can't delete it

  10. #10
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8
    Quote Originally Posted by ArviLaanemets View Post
    On fly
    Code:
    SELECT pl.*
    FROM 
         PriceList pl INNER JOIN (SELECT pl0.ProdCode, MIN(pl0.Price) AS MinPrice FROM PriceList pl0 GROUP BY pl0.ProdCode) pmin ON pmin.ProdCode = pl.ProdCode AND pmin.MinPrice = pl.Price

    NB! In case several suppliers had price equal with minimal price for same product, there will be a row for every such supplier for this product.
    It works!!! Thanks! Now I have to spend some time to understand what the code does.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by KubaG View Post
    It works!!! Thanks! Now I have to spend some time to understand what the code does.
    The subquery (the one in brackets) returns list of minimum prices for every product;
    The INNER JOIN query returns only those records from both parts of it (table Pricelist, and subquery), where join conditions are all true, i.e. all records from subquery, and those records from PriceList, where ProdCode and Price are same as ProdCode and MinPrice in subquery;
    As only wanted records from PriceList are returned, it is enough for simply to return Pricelist fields.

  12. #12
    KubaG is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2020
    Posts
    8
    Quote Originally Posted by ArviLaanemets View Post
    The subquery (the one in brackets) returns list of minimum prices for every product;
    The INNER JOIN query returns only those records from both parts of it (table Pricelist, and subquery), where join conditions are all true, i.e. all records from subquery, and those records from PriceList, where ProdCode and Price are same as ProdCode and MinPrice in subquery;
    As only wanted records from PriceList are returned, it is enough for simply to return Pricelist fields.
    Thanks a lot. If it works with big pricelists that we are using at work it'll save us hours of work. We have supplier pricelists changing every week so I want to place them as excel files on a server and then use a joint table to perform the query. Hope Access will survive...

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    In case the amount of data is too huge and query performance drops down, you can consider using SQL Server as back-end database. It has more advanced possibilities for queries (Using WITH clauses, having indexed views, etc.)

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

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 3
    Last Post: 06-06-2018, 08:26 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 1
    Last Post: 07-10-2015, 06:33 AM
  5. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08: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