Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    29

    Unanswered: SQL or VBA?? please advise...

    I'm writing my SELECT statement for a query. I want the query to show multiple colums, filtering through a subquery on 3 columns first. The first subquery runs beautifully. When I add the second, I get no results. The query runs, but produces an empty datasheet. The query is generated by a button on a form, based on values input on the form.

    Am I better to evaluate these parameters through VBA, maybe a lost focus event? Or am I just nesting my subqueries wrong?

    Any advice is greatly appreciated. Thanks for the guidance.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    My guess would be the SQL syntax is off. It would help to see the working query, the non-working one, and perhaps what the data looks like.
    Paul

  3. #3
    Join Date
    Feb 2009
    Posts
    29
    Paul, have I mentioned how much I appreciate your advice? You've made me feel somewhat silly when you point out my mistakes, but I truly appreciate all your help.

    Table 1:tblProducts
    ProductID ProductName Type
    1 LillyVee T-Shirt
    2 Kate Jeans
    3 Levi Jeans
    4 Lulu Skirt
    5 KandyKane T-Shirt

    Table 2:tblDetails
    ProductID Colour Care Quantity
    1 Red Cold 12
    1 Blue Cold 6
    2 Blue Warm 7
    2 White Warm 14
    3 Gray Warm 12
    4 Black Cold 3
    4 Pink Warm 10
    5 Orange Hot 8
    5 Red Hot 6

    SELECT tblProducts.ProductName, tblProducts.Type, tblDetails.Colour, tblDetails.Car, tblDetails.Quantity
    FROM tblProducts INNER JOIN tblDetails ON tblProducts.ProductID=tblDetails.ProductID
    WHERE tblProducts.ProductID IN (SELECT ProductID From tblProducts Where tblPurpose.Type=[Forms]![frmClient]![subOrder]![ctrType])

    This works. However, I need to also filter the results based on Care and Quantity. The first filter is Type, followed by Quantity, and then Care. So the Type subquery should be the innermost. But when I add the other 2, nothing works. I was using (SELECT ProductID From tblDetails Where tblDetails.Quantity<=[Forms]![frmClient]![subOrder]![ctrQuantity] In (SELECT...))

    Does this help?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is there really a tblPurpose? If so, what's in it? I'm actually thinking that's a typo, because that query wouldn't run. Does this not work for the first?

    SELECT tblProducts.ProductName, tblProducts.Type, tblDetails.Colour, tblDetails.Car, tblDetails.Quantity
    FROM tblProducts INNER JOIN tblDetails ON tblProducts.ProductID=tblDetails.ProductID
    WHERE tblProducts.Type=[Forms]![frmClient]![subOrder]![ctrType])
    Paul

  5. #5
    Join Date
    Feb 2009
    Posts
    29
    My apologies, it should have read tblProducts, not Purpose. The problem I'm running into is that I want the query to search in a certain sequence...first find the matching types, then the qualifying quantity, then the colour. There are also other fields to further narrow the search, but these 3 are the primaries. I figured subqueries were the best way to make sure that the query looks for these things first before looking at everything else. Does that make sense? Is there a better way to do it?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Subqueries are less efficient than joins or where clauses. Did what I posted work?
    Paul

  7. #7
    Join Date
    Feb 2009
    Posts
    29
    What you posted did work, but so did what I had when I searched for Type. How do I get the Where clause to search in a priority sequence?

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    How about

    ...
    WHERE tblProducts.Type=[Forms]![frmClient]![subOrder]![ctrType] AND SecondField = SecondFormReference

    I'm not sure where you're going with "priority sequence". Conceptually, saying something must meet condition A, then B, then C is no different than saying it must meet all three, and the order is more or less irrelevant. Unless I'm having an early morning brain cramp.
    Paul

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In general, the IN operator is less efficient to most alternatives that are semantically the same. Also, it can get you into trouble - note that if ProductID could be NULL (I doubt it could be) then the two statements are ever so slightly different.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2009
    Posts
    29
    I'm still fairly new to database systems, only been working with them since December. So forgive me if I seem way off track.

    When using the Where conditions, some of them work and others don't. It may be that the query is too complex, which is why I started with subqueries. Obviously my table is much larger than the sample I gave you, and I need the query to be as specific as possible when searching the products. It often is required to search 6 or 7 criteria. Some are required fields, others are optional to help narrow the search.

    But now I'm having problems getting all my Where conditions to work.

  11. #11
    Join Date
    Feb 2009
    Posts
    29
    Okay, I found the problem with my Where conditions. The query seems to be working now. One last question...where would I use the MAX function? In a subquery, in my SELECT statement, in a GROUP BY, or ORDER BY?

    Many thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •