none
Display only one value from a field RRS feed

  • Question

  • I created a query from 2 tables(tblSIGNS and tblHISTORY) and did a calculated field based off the information from those tables. from tblSIGNS I am using SIGNID from tblHISTORY I am using SIGN_ID. They both have the same values, but SIGN_ID has multiple values being as this table is a work order table. So this query is to tell me if there is a open work order or not. My trouble is that I only want one of the SIGN_ID to link with SIGNID and the only one I want is if there is if there is an open work order. I have tried using SELECT DISTINCT and that didn't work for me. Such as in the picture of the table the first 2 are the same sign ids but i only want the second one to display. How do I do this? I also have the SQL pictured. 
    Monday, December 14, 2015 5:56 PM

Answers

  • I do have a date/time field. A record in tblHISTORY goes from High to Completed. The open is only on the query and not in the record. Urgency is coming from tblHISTORY. 

    Okay, thanks. So, using the date/time field and assuming you want the "last" status based on that field, you could try the following:

    SELECT T1.SIGNID, SQ.URGENCY, IIf(SQ.URGENCY="Completed","No","Yes") As Open FROM tblSIGNS AS T1 LEFT JOIN (SELECT T2.SIGN_ID, T2.URGENCY FROM tblHISTORY AS T2 INNER JOIN (SELECT T3.SIGN_ID, Max(T3.[DateTimeFieldName) AS LastStatus FROM tblHISTORY AS T3 GROUP BY T3.SIGN_ID) AS T4 ON T2.SIGN_ID=T4.SIGN_ID AND T2.[DateTimeFieldName]=T4.LastStatus) AS SQ ON T1.SIGNID=SQ.SIGN_ID

    (untested)

    Hope that helps...

    Monday, December 14, 2015 7:00 PM

All replies

  • Hi. You could try the following and see if it's any close:
    SELECT tblSIGNS.SIGNID, tblHISTORY.URGENCY, "Yes" As Open
    FROM tblSIGNS
    LEFT JOIN tblHISTORY
    ON tblSIGNS.SIGNID=tblHISTORY.SIGN_ID
    WHERE tblHISTORY.URGENCY In("High","Medium","Low")

    Hope that helps...

    Monday, December 14, 2015 6:07 PM
  • Looking at your sample data, this might also be the same:

    SELECT tblSIGNS.SIGNID, tblHISTORY.URGENCY, "Yes" As Open
    FROM tblSIGNS
    LEFT JOIN tblHISTORY
    ON tblSIGNS.SIGNID=tblHISTORY.SIGN_ID
    WHERE tblHISTORY.URGENCY<>"Completed"

    Monday, December 14, 2015 6:08 PM
  • Hmm, thinking about it more, I don't think I understood what you wanted. However, I have a question to help clarify it for me. How can a record in tblHISTORY go from Completed to High? In any case, do you have a Date/Time field to identify which record came first/last?
    Monday, December 14, 2015 6:18 PM
  • Why would you have 'Completed' as an Urgency when you have 'Open' to indicate if completed?

    Build a little, test a little

    Monday, December 14, 2015 6:36 PM
  • Okay I forgot to say that I also want to keep the sign ids that are completed if there is no open work order. Sorry i forgot to clarify that in my original question. 
    Monday, December 14, 2015 6:45 PM
  • I do have a date/time field. A record in tblHISTORY goes from High to Completed. The open is only on the query and not in the record. Urgency is coming from tblHISTORY. 
    Monday, December 14, 2015 6:50 PM
  • I do have a date/time field. A record in tblHISTORY goes from High to Completed. The open is only on the query and not in the record. Urgency is coming from tblHISTORY. 

    Okay, thanks. So, using the date/time field and assuming you want the "last" status based on that field, you could try the following:

    SELECT T1.SIGNID, SQ.URGENCY, IIf(SQ.URGENCY="Completed","No","Yes") As Open FROM tblSIGNS AS T1 LEFT JOIN (SELECT T2.SIGN_ID, T2.URGENCY FROM tblHISTORY AS T2 INNER JOIN (SELECT T3.SIGN_ID, Max(T3.[DateTimeFieldName) AS LastStatus FROM tblHISTORY AS T3 GROUP BY T3.SIGN_ID) AS T4 ON T2.SIGN_ID=T4.SIGN_ID AND T2.[DateTimeFieldName]=T4.LastStatus) AS SQ ON T1.SIGNID=SQ.SIGN_ID

    (untested)

    Hope that helps...

    Monday, December 14, 2015 7:00 PM
  • I get a syntax error in FROM clause. What's this?
    Monday, December 14, 2015 7:06 PM
  • I looked at it again and found a missing ) when I typed everything in. 
    Monday, December 14, 2015 7:09 PM
  • I see a missing closing bracket -----------------------------------!

      INNER JOIN (SELECT T3.SIGN_ID, Max(T3.[DateTimeFieldName) AS LastStatus

    To be --

      INNER JOIN (SELECT T3.SIGN_ID, Max(T3.[DateTimeFieldName])) AS LastStatus

       Also had a missing closing parenthesis --------------------------!


    Build a little, test a little






    Monday, December 14, 2015 7:11 PM
  • Hi. Sorry about the typos. Drawbacks for not being able to test the code without access to the same table structure. Glad to hear you found and fixed the errors though. Good luck with your project.
    Monday, December 14, 2015 7:23 PM