Results 1 to 12 of 12
  1. #1
    Join Date
    May 2003
    Posts
    70

    Unanswered: Select certain items

    Hi,

    I have a table with the following fields: KitID, InventoryID, Qty.
    This is the child table containing all the items that are in a kit.
    I want to select only the KitIDs that contain both 6340 and 7845 items in it.
    I only want ones that have both of these items, not either one.

    Thanks,

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yeah ... So?

    Try writing the query to do it for you ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    There are probably a few ways to do this.
    Maybe something like...

    SELECT KITID
    FROM YOURTABLE
    WHERE KITID IN
    (SELECT KITID
    FROM YOURTABLE
    WHERE INVENTORYID=6340)
    AND INVENTORYID=7485

    edit: There I go doing it for you after Mike's already told you to at least try it yourself
    Inspiration Through Fermentation

  4. #4
    Join Date
    May 2003
    Posts
    70
    Thanks and way to get your post count up mike, LOL.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by vbgladiator
    Thanks and way to get your post count up mike, LOL.
    Don't need to ... Posts like this usually get my silence (no reply) ... I'm bored today and felt like being a bit snarky ... Besides, folks around like to help out usually after at least a go at it from the poster ... We really don't like to hand solutions out on a silver platter here ... Be thankful that Teddy didn't get to you first ... He's a bit more blunt about this than I ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    May 2003
    Posts
    70
    I have tried for a while before posting here.
    The solution posted does not work.
    I can if it's either 1 or the other but not both.

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What about it doesn't work?

    I designed it after my own BOM table, and it works fine for me.

    I assume you had to change the query to contain you real table/column
    info. Post the sql that you tried.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by M Owen
    Don't need to ... Posts like this usually get my silence (no reply) ... I'm bored today and felt like being a bit snarky ... Besides, folks around like to help out usually after at least a go at it from the poster ... We really don't like to hand solutions out on a silver platter here ... Be thankful that Teddy didn't get to you first ... He's a bit more blunt about this than I ...
    hah... and you guys are pushovers compared the folks in the SQL Server forum. I exhaust ALL sources (including three weeks of meditation) before I dare post a well thought out question there.
    Inspiration Through Fermentation

  9. #9
    Join Date
    May 2003
    Posts
    70
    SELECT KITID
    FROM tblkitdetails
    WHERE KITID IN
    (SELECT KITID
    FROM tblkitdetails
    WHERE INVENTORYID=6340
    AND INVENTORYID=7485)

    This returns nothing when in fact i know for sure that certain kitids have both of these items.

    When changed to or from and in teh where clause, it returns all that have either 1 or both.

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Don't know what to tell you. I'm stumped.

    I use this on my table, and get the 3 rows I expect to see
    (same concept, different field names)

    SELECT BOD_INDEX
    FROM BOM_DETAIL
    WHERE (BOD_INDEX IN
    (SELECT BOD_INDEX
    FROM BOM_DETAIL
    WHERE (RAW = 'masf'))) AND (RAW = '10f')
    Inspiration Through Fermentation

  11. #11
    Join Date
    May 2003
    Posts
    70
    it's ok. thanks for the quick replies.

  12. #12
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I see it...

    You're closing paren ) is misplaced

    SELECT KITID
    FROM tblkitdetails
    WHERE KITID IN
    (SELECT KITID
    FROM tblkitdetails
    WHERE INVENTORYID=6340)
    AND INVENTORYID=7485
    Inspiration Through Fermentation

Posting Permissions

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