none
Help with Select Query RRS feed

  • Question

  • Hello,

    I need some help with a query in a 2016 Access database on a Win7 pc. I have a table named tblDesigns. Here's what I need the query to do:
     > Where the values in the PT_Num field are the same and have a value in Desc field, the Status field for each record should indicate "D".
    >  Where the values in the PT_Num field are the same and some of those records have a value and some are null in Desc field, the Status field for each record should indicate "M". 
    >  Where the values in the PT_Num field are the same and all are null in Desc field, the Status field for each record should indicate "U".

    I provided examples of "what I have" currently in the tblDesigns table and "what I need" in my query.

    Hope this makes sense.

    Thanks for your help.
    Cheers, Kevin

    Wednesday, April 24, 2019 12:04 AM

Answers

  • Status: IIf(DCount("*","tblDesigns","PT_Num='" & [PT_Num] & "' AND Desc Is Not Null")>0,IIf(DCount("*","tblDesigns","PT_Num='" & [PT_Num] & "' AND Desc Is Null")>0,"M","D"),"U")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KevinATF Wednesday, April 24, 2019 5:02 PM
    Wednesday, April 24, 2019 8:47 AM

All replies

  • Status: IIf(DCount("*","tblDesigns","PT_Num='" & [PT_Num] & "' AND Desc Is Not Null")>0,IIf(DCount("*","tblDesigns","PT_Num='" & [PT_Num] & "' AND Desc Is Null")>0,"M","D"),"U")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KevinATF Wednesday, April 24, 2019 5:02 PM
    Wednesday, April 24, 2019 8:47 AM
  • You can use a subquery:

    
    
    SELECT     
        T1.ID,     
        T1.PT_Num,     
        T1.Desc,    
        T2.Status
    FROM
        YourTable AS T1 
    INNER JOIN
        (SELECT T.PT_Num, IIf(Count([Desc]),IIf(Count(*)=Count([Desc]),"D","M"),"U") AS Status
        FROM YourTable AS T
        GROUP BY T.FK) AS T2
        ON T1.PT_Num = T2.PT_Num
    ORDER BY
        T1.PT_Num;
    



    Gustav Brock


    Wednesday, April 24, 2019 12:03 PM
  • Gentlemen,

    Thank you both for your suggestions. Worked great!

    Cheers, Kevin

    Wednesday, April 24, 2019 5:03 PM