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’

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

Yes, you understood it perfectly!

Two WHERE's. That did it!

Thanks soooo much.

'Rosie'