Display only one value from a field

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. 

Answer 1

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...

Answer 2

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"

Answer 3

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?

Answer 4

Why would you have 'Completed' as an Urgency when you have 'Open' to indicate if completed?

---

Build a little, test a little

Answer 5

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. 

Answer 6

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. 

Answer 7

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...

Answer 8

I get a syntax error in FROM clause. What's this?

Answer 9

I looked at it again and found a missing ) when I typed everything in. 

Answer 10

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

Answer 11

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.