Correlated Subquery

[M_S_Jones]

Morning all,

I've read several threads on this forum about correlated subqueries and the problems that others have had whilst creating them, but none of what I've read has seemed to help. In this query, piece number is basically an ID for a piece of fabric. Each piece of fabric can be using in any number of stock transactions. Currently I'm running two queries which between them return a list of transactions for each piece number, along with a field "Warping_Diff", which is the difference in days between the current date and a date specified by the user, at the beginning of the first query.

The records returned from these two queries contain all transactions for all piece numbers within a date range also specified by the user. This is what I want to happen, with one small change: I want only one record for each piece number, in particular, I want the record with the lowest value in the "Warping_Diff" field for each piece number. I've written the correlated subquery below, based on a very similar scenario that I saw in an SQL book, but it doesn't return any results. When I wrote this query in SQL view in query builder and then ran it, query builder changed zqryWarping_Data2 to "a", so I attempted to execute the query in VBA by storing it as a string, but this generated an error message saying that an SQL statement had been expected.

Here's the query:

SELECT PieceNo, Warp_Stock_Transaction_ID, Warping_Diff 
FROM zqryWarping_Data2 AS a 
WHERE (SELECT min(Warping_Diff) FROM zqryWarping_Data2 as b WHERE a.PieceNo = b.PieceNo);

Any help would be greatly appreciated.

Thanks,

Matt

 

[M_S_Jones]

Oh, just realised why, I was using "DoCmd.RunSQL" to call the query (which wasn't an action query.) Ignore this thread then!

Matt

 

[M_S_Jones]

Right, I've got the query working without generating an error now, but the results aren't as I expected, all matches are returned, rather than just the record with the lowest "Warping_Diff" for each piece number. Any ideas?

 

[doco]

SELECT 
      PieceNo
    , Warp_Stock_Transaction_ID
    , MIN( Warping_Diff ) as min_diff
FROM 
    zqryWarping_Data2
ORDER BY
      PieceNo
    , Warp_Stock_Treansaction_ID

This is making the assumption that zqryWarping_Data2 is a query showing the differences already(?)
OR, one I don't have a warm and fuzzy feeling about without actually being able to test or see the data

SELECT 
      a.PieceNo
    , a.Warp_Stock_Transaction_ID
    , ( select
            min( b.Warping_Diff ) as diff
        from
            zqryWarping_Data2 b
        where
            b.PieceNo= a.PieceNo) as warp_diff
FROM 
    zqryWarping_Data2 a

used wrong key - assuming piece_no is the key

 

[M_S_Jones]

Thanks for the queries. I've tried them in my form but the first generates an error and the second just returns all of the results, like the original query does. Your assumption was correct, zqryWarping_Data2 shows the differences already. The two halves of the query I posted on here originally work in isolation, that is to say that the first half returns all of the records when running without the WHERE clause, and the second half returns the record with the lowest "Warping_Diff" for any given piece number, when that piece number is specified in the query. It's just when I put them together that the second half is basically ignored; although if I make a syntax error in the second half it is still detected. Any other ideas?

Thanks

 

[M_S_Jones]

Got it! It needed more criteria, here's the working code:

SELECT PieceNo, Warp_Stock_Transaction_ID, Warping_Diff 
FROM zqryWarping_Data2 AS a 
WHERE Warping_Diff = (SELECT min(Warping_Diff) FROM zqryWarping_Data2 AS b WHERE a.PieceNo = b.PieceNo);

 

[doco]

Good Job!

I see the difference being Warping_Diff not PieceNo. Guess I should have coffee earlier

 

[M_S_Jones]

Haha Thanks for taking the time to offer help.