Filtering

[llilek20]

Hi,
I was wondering if anyone can help me figure out how to perform the following function in access.
Example:I have two columns ID and Field number. Each Id might have several Field numbers. I need a way to group ID and be able to filter out Field number 99, so that that same individual (ID)who has 99 and 5 field number won't show up in my results.

ID Field number

123 1
123 2
123 99
456 1
456 3
965 2
965 99

What do I need to do in order not to see ID 123 and 965 in my query results?

Thanks :banghead:

 

[plog]

You need a subquery to identify all those ID numbers with Field Number=99:

SELECT ID FROM YourTableNameHere WHERE [Field Number]=99

Save that query. For now, let's call it '_sub1'. Then you build a new query using YourTableNameHere and _sub1. You JOIN the two data sources by ID their fields and then change the JOIN by right clicking on it and selecting the option that says something like 'Show all records from YourTableNameHere'. Next, bring down all the fields from YourTableNameHere as well as the ID field from '_sub1'. Underneath the _sub1.ID field you put "Is Null". Run that and you have your data.

 

[llilek20]

Thank you so much for your help !