querry help ...don't know how to word question

[keefgreen]

Greetings,
I'm a Tektip and Access newbie. This seemed like the best site I could find for experts in this area. Let me apologise if I am posting in the wrong spot (wasn't sure which access forum to use). If this has already answered I apologise as well. I spent much time searching for the answer. If it has please feel free to advise as to the correct location.

From the example below:

I am trying to create a query that displays records that have an I in the status column but that don't also have a corresponding same number with a different status code.

In the example below the result I am looking for would be: INV NUMBER 3 and 5 but I don't know the expression to create it.

Example table
INV NUMBER STATUS
------------ ------
1 I
1 S
2 I
2 S
3 I
4 I
4 S
5 I
6 I
6 S

Any advice would be greatly appreciated
thanks Keef

 

[PHV]

SELECT [INV NUMBER]
FROM [Example table] A
WHERE STATUS = 'I'
AND NOT EXISTS (SELECT * FROM [Example table] WHERE [INV NUMBER]=A.[INV NUMBER] AND STATUS<>'I')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[keefgreen]

PH

thanks for the lightning fast answer. Except being a newbie I'm not exactly sure how/where to implement this in my query.

Does this go in the criteria field when creating the query?



If it helps I am using Access 97 to querry an Foxpro database.

thanks
Keef

 

[Golom]

No. Start a new query and switch to SQL view. Paste PHV's code into the SQL pane. Make sure that the table and field names match those of your tables and fields. What PHV has given you is the whole query, not just a part of your existing query.