Subquery frustration

[nathansav]

Hi

Firstly a bit of background into the madness of my table. I had a sharepoint site linked to an access database for reporting. This SP site created workflows and tracked statuses, however for just over a day our SP server got full, so the new list items for tracking couldnt be created. As a backup plan, i ran an append query to track what the status was twice a day. The workflows are now ok, so need to condense my results.

For example, i have, the following for an ID of 1

Query4Defect_IDDefect StatusBU_DATE1327/02/20141328/02/20141303/03/20141303/03/20141804/03/20141805/03/20141706/03/20141710/03/20141711/03/20141713/03/20141713/03/2014

So defect 1, goes from status 3 to 8 then to 7.

I have the following to work out my table structure so far, the issue is getting the previous status.

SELECT MAIN.Defect_ID, MAIN.[Defect Status], Min(MAIN.BU_DATE) AS MinOfBU_DATE, (select top 1 t1.[defect status] from tbl_status_phase_change as t1 where t1.defect_id=main.defect_ID and t1.bu_date<(select min(t2.BU_date) from tbl_status_phase_change as t2 where t2.defect_Id=main.defect_ID and t2.[defect status]=main.[defect status]) order by t1.BU_date DESC) AS STATUS_FROM
FROM tbl_Status_Phase_Change AS MAIN
GROUP BY MAIN.Defect_ID, MAIN.[Defect Status]
HAVING (((MAIN.Defect_ID) Is Not Null))
ORDER BY MAIN.Defect_ID, Min(MAIN.BU_DATE);

However i get the error At most one record can be returned from this query, however, when i sort ascending its fine.

Many thanks,

 

[nathansav]

Attached an excel version of the table

 

[plog]

Can you post sample starting data and then what data should be returned from your query based on that sample starting data?

 

[nathansav]

Hi,

I have attached some sample data, and the query i use, apologies i thought that would have been enough. The query is off that data, so taking the 2 into access would have shown.

Here is the data on the left, output on the right

Thanks

 

[plog]

The StatusFrom has no data in it, so I have no idea how that's working. However I am able to build a query to generate what results you want from your starting data:

SELECT Defect_ID, "" AS StatusFrom, [Defect Status] AS StatusTo, MIN(BU_DATE) AS DateIN
FROM YourTableNameHere
GROUP BY Defect_ID, [Defect Status];

If that's doesn't work for all cases, post which cases it fails for and what data should be returned.

 

[nathansav]

Apologies, but have you read my post????? I have that, there is no data for status_from which is why i am using a subquery to find the status before it was at the current status

 

[plog]

I read the part of your post that didn't look like a jumbled mess. Then I asked for data to see what you needed. Then, based on that data I created a query to get you from point A to point B. Which worked perfectly for the sample you gave me.

If it fails in other cases, you need to provide me with better sample data. My solution worked perfectly for what you gave me. Show me (using data) where and how it fails.

You can't tell me you need to go from Los Angeles to New York and then get mad when we arrive in Manhattan without seeing Disney World. You didn't tell me that was part of the trip.

 

[nathansav]

If you take out the bit in bold, the subquery, then you are left with your query.

The jumbled mess is the SQL )

(select top 1 t1.[defect status] from tbl_status_phase_change as t1 where t1.defect_id=main.defect_ID and t1.bu_date<(select min(t2.BU_date) from tbl_status_phase_change as t2 where t2.defect_Id=main.defect_ID and t2.[defect status]=main.[defect status]) order by t1.BU_date DESC) AS STATUS_FROM


You shouldnt really be answering a subquery post if you dont understand what a subquery is. You wouldnt offer to fly the plane if you only knew how to drive a boat.

 

[plog]

Deal. Good luck.