locked
product list price problem RRS feed

  • Question

  • ok i have this 2 tables regarding purchase order
    http://img200.imageshack.us/img200/5315/relation.jpg

    so it happen that the customer order specific product each time from different supplier so when making product list it will appear the entire purchase order that he but it from them but what he really want is the last one he deal with to buy this product to be as a product price list

    so this is the SQL Statment

    SELECT [FACTORY PURCHASE ORDER].CODE, [FACTORY PURCHASE ORDER].PRODUCT, [FACTORY PURCHASE ORDER].[UNIT PRICE]
    FROM [FACTORY PURCHASES DETAILS] INNER JOIN [FACTORY PURCHASE ORDER] ON [FACTORY PURCHASES DETAILS].[ID] = [FACTORY PURCHASE ORDER].[SUPPLIERS REL]
    ORDER BY [FACTORY PURCHASE ORDER].code , [FACTORY PURCHASES DETAILS].DATE DESC
    so here it order the list but the problem it will give him all the suppliers and he just need the last one he deal with for all product regarding the date as i order it
    so this what i get ( i put a photo for only one product)
    http://img148.imageshack.us/img148/7766/sampleh.png
    so in the report only the first one have to appeat while the other must not as this the last deal with it

    maybe i dont ask properly but hope u understand

    thanks
    I am A Medical Doctor
    Wednesday, July 22, 2009 5:14 PM

Answers

  • You can try this (not sure will work in Access). Note it may still return multiple rows if the max date for products is the same:

    SELECT O.CODE, 
           O.PRODUCT, 
           O.[UNIT PRICE]
    FROM [FACTORY PURCHASES DETAILS] AS D INNER JOIN [FACTORY PURCHASE ORDER] AS O ON D.[ID] = O.[SUPPLIERS REL] WHERE D.DATE = (SELECT MAX(D1.DATE) FROM [FACTORY PURCHASES DETAILS] AS D1 INNER JOIN [FACTORY PURCHASE ORDER] AS O1 ON D1.[ID] = O1.[SUPPLIERS REL] WHERE O1.[CODE] = O.[CODE]);

    Plamen Ratchev
    Wednesday, July 22, 2009 6:11 PM

All replies

  • SELECT [FACTORY PURCHASE ORDER].CODE, [FACTORY PURCHASE ORDER].PRODUCT, [FACTORY PURCHASE ORDER].[UNIT PRICE],[FACTORY PURCHASES DETAILS].DATE
    FROM [FACTORY PURCHASES DETAILS]
    INNER JOIN [FACTORY PURCHASE ORDER] ON [FACTORY PURCHASES DETAILS].[ID] = [FACTORY PURCHASE ORDER].[SUPPLIERS REL]
    inner join (select ID,max(DAte) from [FACTORY PURCHASES DETAILS] group by ID)t on t.ID =  [FACTORY PURCHASES DETAILS].ID
    ORDER BY [FACTORY PURCHASE ORDER].code , [FACTORY PURCHASES DETAILS].DATE DESC
    Wednesday, July 22, 2009 5:21 PM
  • Here is one method using the ranking functions in SQL Server 2005/2008:

    SELECT code, product, unit_price
    FROM (
    SELECT O.CODE, 
           O.PRODUCT, 
           O.[UNIT PRICE] AS unit_price,
           ROW_NUMBER() OVER(PARTITION BY O.CODE
                             ORDER BY D.DATE DESC) AS rk
    FROM [FACTORY PURCHASES DETAILS] AS D
    INNER JOIN [FACTORY PURCHASE ORDER] AS O
       ON D.[ID] = O.[SUPPLIERS REL]) AS T
    WHERE rk = 1;

    Plamen Ratchev
    Wednesday, July 22, 2009 5:22 PM
  • NOTE THAT I AM USING MICROSOFT ACCESS AND NOT SQL SERVER

    I am A Medical Doctor
    Wednesday, July 22, 2009 5:24 PM
  • maybe it is correct but it gives me this msgbox

    http://img200.imageshack.us/img200/3861/errortgd.jpg

    then it point here

    http://img50.imageshack.us/img50/254/point.jpg

    I am A Medical Doctor
    Wednesday, July 22, 2009 5:31 PM
  • SELECT [FACTORY PURCHASE ORDER].CODE, [FACTORY PURCHASE ORDER].PRODUCT, [FACTORY PURCHASE ORDER].[UNIT PRICE],[FACTORY PURCHASES DETAILS].DATE
    FROM [FACTORY PURCHASES DETAILS]
    INNER JOIN [FACTORY PURCHASE ORDER] ON [FACTORY PURCHASES DETAILS].[ID] = [FACTORY PURCHASE ORDER].[SUPPLIERS REL]
    inner join (select ID,max(DAte) from [FACTORY PURCHASES DETAILS] group by ID) as t on t.ID =  [FACTORY PURCHASES DETAILS].ID
    ORDER BY [FACTORY PURCHASE ORDER].code , [FACTORY PURCHASES DETAILS].DATE DESC


    try this. Sorry, I don't have access database to test this. But it must work, becausae there are not any special features.only basic inner join with a subquery only.
    Wednesday, July 22, 2009 5:44 PM
  • sorry but it also give me the same error
    I am A Medical Doctor
    Wednesday, July 22, 2009 5:55 PM
  • NOTE THAT MY PROBLEM IS ABOUT THAT RESULT IS MORE THE ONE ORDER FOR A PRODUCT PURCHASED AND YES IT IS MORE THAN ONE BUT WHEN I WANT A LIST PRICE IT SUPPOSE TO GIVE ME ONLY ONE ORDER FOR EACH PRODUCT WHIH IS THE LAST ORDER I MAKE TO THIS PRODUCT SO IT IS THE CURRENT PRICE AND THE CURRENT COMPANY
    I am A Medical Doctor
    Wednesday, July 22, 2009 6:02 PM
  • You can try this (not sure will work in Access). Note it may still return multiple rows if the max date for products is the same:

    SELECT O.CODE, 
           O.PRODUCT, 
           O.[UNIT PRICE]
    FROM [FACTORY PURCHASES DETAILS] AS D INNER JOIN [FACTORY PURCHASE ORDER] AS O ON D.[ID] = O.[SUPPLIERS REL] WHERE D.DATE = (SELECT MAX(D1.DATE) FROM [FACTORY PURCHASES DETAILS] AS D1 INNER JOIN [FACTORY PURCHASE ORDER] AS O1 ON D1.[ID] = O1.[SUPPLIERS REL] WHERE O1.[CODE] = O.[CODE]);

    Plamen Ratchev
    Wednesday, July 22, 2009 6:11 PM
  • Actually I understood t he problem, But i am not good enough to write query in access without testing. What i gave is Sql Version.
    In Access, I think there is a problem, we cannot do multiple inner joins in a single query, i think we need to use paranthesis somewhere.

    ANyhow, hopefully, some access specialists will solve your problem.


    This problem is same kind of the problem i answered in below sql thread.
    http://forums.asp.net/p/1430956/3208018.aspx
    Wednesday, July 22, 2009 6:11 PM