Query to filter data

[frankt68]

Hi!
I use Access 2010 and have very little experience with it.

I have a table with the following fields:

Group_ID, Item_ID, Item_name, Item_price, Price_ranking, Selection (see Example1.jpg)

The records are sorted by Group_ID and Price_ranking, where number 1 represents the lowest price. Each Group_ID may be associated with one or more Item_ID. For each Group_ID, there should be a selection of Item with the lowest price (Price_Ranking = 1), but in certain cases the Items Price_ranking different from 1 could be selected (this is OK).


Each Group_ID should have at least one selected Item, but there could be two or none selected.

I'd like to use a query to filter out all Group_id that do not have any marked selection. How can I do that?

 

[arnelgp]

hello sir, what again is your requirement.

 

[frankt68]

hello sir, what again is your requirement.

With regard to what I wrote in my thread, I'd like to use a query to filter out all Group_id that do not have any marked selection.

 

[arnelgp]

select * from yourTable Where Not ([Selection])

 

[frankt68]

select * from yourTable Where Not ([Selection])

Thanks, but it doesn't do the trick because it shows all records without selection (see example2.jpg). I'll try to explain.

If you compare example1.jpg and example2.jpg you can see that there are several Items with a Grup_ID 1A.01.AB.09.001 and different Price_Ranking. There is a selection for that Group_ID and Item Price_ranking 1 (see exapmple1.jpg) and therefore, I don't want that Group_ID to be shown in my query.
Actually, none of the records in example2.jpg should appear in that query because they all have a selection for their Group_ID's at Price_ranking 1.

I would like the query to show only those Group_ID's with no selection for any Item_ID and Price_Ranking. So for example if there would not be a selection for Group_ID 1A.01.AB.09.001 and Item Price_Ranking 1, then the Group_ID 1A.01.AB.09.001 should be shown in a query.

Any suggestion?

 

[arnelgp]

select * from yourTable Where group_id not in (select group_id from yourTable Where [selection]=True)

or

select * from yourTable Where (group_id & price_ranking) not in (select (group_id & price_ranking) from yourTable Where [selection]=True)

 

[frankt68]

select * from yourTable Where group_id not in (select group_id from yourTable Where [selection]=True)

or

select * from yourTable Where (group_id & price_ranking) not in (select (group_id & price_ranking) from yourTable Where [selection]=True)

Sorry for late replay. I tried both suggestions and the first one is the right one for me. Thanks.