Help with subquery

• Jennifer-238180

  Valued Member

  Points: 50

  More actions

  June 9, 2005 at 2:24 pm

  #102583

  Hello,

  I need help buiding a query:

  I have Multiple single items with different statuses (one item can have several diff. statuses).

  The items and statuses are in different tables.

  I just want to have a recordset with each unique item and latest status. Here's what I have now.

  1st Query:

  //Get all individual items based on ItemField

  "SELECT DISTINCT Table1.ItemField FROM Table1 WHERE

  Table1.CustomerID=" + CustomerID

  Loop through each record to get latest status:

  2nd Query:

  "SELECT TOP 1 * FROM Table1 INNER JOIN Table2 ON Table1.DataID = Table2.DataID INNER JOIN

  Table3 ON Table2.Status = Table3.StatusID

  WHERE Table1.CustomerID= CustomerID AND Table1.Item= " + oDR["ItemField"] (Table1.ItemField from first dataset) + " ORDER BY Table2.StatusChangeDate DESC";

  I want to be able to put these 2 queries together so that I don't have to use more resources looping through the recordset.

  Thanks,

  Jennifer

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  June 9, 2005 at 2:27 pm

  #564389

  Please do not cross post, we monitor all the boards.

  Finish this thread HERE