SQL problem
-
Hi, I have a table consisting of an ID, a Date and a StatusCode. Basically it holds a log of when the status of things ('Defects') identified by the ID changed. (The ID is NOT unique in this table). I am trying to develop an SQL statement that will return the ID and Date for records that have most recently been changed to a certain status. I am using MS Access. The Query below will return the date that each Defect's status was last changed: SELECT DefectNumber, Max(DateTime) FROM MAIN_Status GROUP BY DefectNumber I want to be able to discard records from the results of this that are not currently "issued". NOTE: Using a WHERE StatusCode = "ISS" clause does not do what is required - it will give me the most recent date that each Defect has been issued. After a defect has been issued it can then be changed to completed and thus is no longer relevant. I hope this makes sense... Any suggestions at all would be great. Thanks, Alex
-
Hi, I have a table consisting of an ID, a Date and a StatusCode. Basically it holds a log of when the status of things ('Defects') identified by the ID changed. (The ID is NOT unique in this table). I am trying to develop an SQL statement that will return the ID and Date for records that have most recently been changed to a certain status. I am using MS Access. The Query below will return the date that each Defect's status was last changed: SELECT DefectNumber, Max(DateTime) FROM MAIN_Status GROUP BY DefectNumber I want to be able to discard records from the results of this that are not currently "issued". NOTE: Using a WHERE StatusCode = "ISS" clause does not do what is required - it will give me the most recent date that each Defect has been issued. After a defect has been issued it can then be changed to completed and thus is no longer relevant. I hope this makes sense... Any suggestions at all would be great. Thanks, Alex
If I understand what you are asking, I think what you need to do is have a WHERE clause with an IN statement SELECT DefectNumber, Max(DateTime) FROM MAIN_Status WHERE ID not IN (SELECT ID FROM MAIN_Status where sts = 'completed) GROUP BY DefectNumber
-
If I understand what you are asking, I think what you need to do is have a WHERE clause with an IN statement SELECT DefectNumber, Max(DateTime) FROM MAIN_Status WHERE ID not IN (SELECT ID FROM MAIN_Status where sts = 'completed) GROUP BY DefectNumber
Would this not just retrieve the Date for when each defect was last issued? What I require is a list of defects for which the last action was to issue them, and i require the Date at which this took place. I imagine this is hard to understand without a more verbose description of the problem. Perhaps explaining an analogous situation could help: A table of employee details as follows: DepartmentCode Name Salary 1 Alex 40000 1 Kevin 30000 1 Mark 20000 2 Bruce 50000 2 John 60000 3 Michael 70000 Say I want to find the employees in each Department with the highest salary. I could do: SELECT DepartmentCode, MAX(Salary) FROM employees GROUP BY DepartmentCode to get a table with DepartmentCode and Salary, but how can i also get the Name of the highest paid person? The Name is analogous to my StatusCode, if i can get the StatusCode in the results i can use perhaps a HAVING clause to eliminate the ones that are not "ISS". I hope this makes things clearer Thanks for your help :) ~Alex