Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    10

    Unanswered: Access sub-select question

    NewBe Access Question

    Hi folks,

    I am having trouble building a query that retrieves what I need. The table I query is pre-filled with rows of records both empty and filled that I will continue to fill or empty from a form. Two columns in the table are completely pre-filled, the ID column and another, which has three different text categories. I need to sub-select an entire group of rows with one kind of text entry and then select the lowest ID value in that group who’s columns are otherwise empty.

    The table might look like:

    ID CAT INFO2 INFO3 Etc.
    1 aaaa
    2 aaaa something
    3 aaaa
    4 bbbb
    5 bbbb something
    6 bbbb
    7 cccc
    8 cccc
    9 cccc something

    So, if Row ID 4 was the MIN ID of CAT bbbb who’s columns were otherwise empty, I would like to query it and fill the other columns.

    SELECT *
    FROM Tbl
    WHERE ID = (SELECT MIN(ID) FROM Tbl)

    The above returns the lowest ID row in the entire table, of course. Not what I wanted.


    SELECT *
    FROM Tbl
    WHERE ID = (SELECT MIN(ID) FROM Tbl)
    AND Cat = 'bbbb'
    AND Info2 IS NULL

    This returns a “No records returned” message. Of course! I presume it does so because the lowest ID for an empty bbbb category is not the lowest ID in the table.

    Any help would be greatly appreciated.

    ‘Rosie’

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    I hope I understood this correctly.

    To return the lowest ID for a certain category has a null value do the following.

    SELECT *
    FROM Tbl
    WHERE ID = (SELECT MIN(ID) FROM Tbl WHERE Cat = 'bbbb'
    AND Info2 IS NULL)

    Note that there is another where statement in the sub-query

    Machado

  3. #3
    Join Date
    Feb 2003
    Posts
    10

    Thanks

    Yes, you understood it perfectly!

    Two WHERE's. That did it!

    Thanks soooo much.

    'Rosie'

Posting Permissions

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