Results 1 to 3 of 3
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    Query problem

    Hello guys,


    I need to delete those PO whose indicator for all items is 'L'. not only few item. In below example of 1 po only I cannot delete because 5 items has 'L' but 1 item is blank. I want to delete if indicator for all item has L. I grouped them all, how can i find PO that contains all item has 'L'

    DATE PO LOCATION TYPE COUNT_ITEMS INDICATOR
    20170316 4817558647 0003001063 DDP 5 L
    20170316 4817558647 0003001063 DDP 1 NULL


    SELECT

    t1.DATE,
    t1.PO,
    T1.LOCATION,
    t1.TYPE,
    count(T2.ITEM) AS COUNT_ITEMS
    T2.INDICATOR



    from PO t1


    INNER JOIN PODTL T2 ON (T1.ID=T2.ID AND T1.PO=T2.PO)


    WHERE T1.LOCATION='0003001063' AND t1.PO='4817558647'
    group by
    t1.DATE,
    t1.PO,
    T1.LOCATION,
    t1.TYPE,
    T2.INDICATOR

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a query to return total number of items and count of items where indicator=L and delete the POs where the two counts are equal.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    keviny04 is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2015
    Posts
    128
    I would create a custom function on the SQL Server to get a count of items that don't have "L" for a given PO:

    Code:
    CREATE FUNCTION GetCountOfNotL(@PO nvarchar(10))
    RETURNS int
    AS
    BEGIN
        DECLARE @ret int;
        SELECT @ret = COUNT(*) FROM PO WHERE PO=@PO AND CONCAT(INDICATOR, '') <> 'L';
        IF (@ret IS NULL) SET @ret = 0;
        RETURN @ret;
    END;
    This function returns the number of lines where INDICATOR is not L for a given PO. If this number is 0, that means the PO has all L's, and therefore can be deleted.

    I use CONCAT above to ensure I get a string value because your INDICATOR field could be Null.

    Then you just run a simple DELETE query to delete all the PO items whose above return value is zero:

    Code:
    DELETE FROM PO WHERE dbo.GetCountOfNotL(PO) = 0

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

Similar Threads

  1. Query problem
    By neilsolaris in forum Queries
    Replies: 12
    Last Post: 05-24-2020, 02:25 AM
  2. Update Query Problem (Not an Updateable Query)
    By McArthurGDM in forum Queries
    Replies: 6
    Last Post: 02-19-2015, 11:25 AM
  3. SQL query problem
    By jinz in forum Queries
    Replies: 1
    Last Post: 12-05-2012, 04:49 PM
  4. query problem i have a problem wi
    By maxx3 in forum Queries
    Replies: 0
    Last Post: 06-29-2009, 02:29 AM
  5. query problem
    By bhushan98 in forum Queries
    Replies: 1
    Last Post: 06-03-2009, 01:49 PM

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