Suppressing multiple rows based on a field value in one row

[Mikelambnj]

I have a table that contains the fields “order number” and “qty”. There are multiple rows for each order number. How can I suppress all rows for an order number where quantity is 0 on one of the rows? Example: order # 123 has 4 rows. One row has a qty of 1, the second row has a qty of 2, the third row has a qty of 0, and the fourth row has a qty of 0. Since rows 3 and 4 have a qty of 0 I want to suppress all 4 rows in the query result.

 

[Gasman]

Where are you trying to filter them?

 

[Mikelambnj]

Where are you trying to filter them?

Trying to filter them using a query.

 

[arnelgp]

modify the Query and add a Criteria below the "qty" field while in Query design:

Field: qty
Table: the_table_name
...
...
Criteria: > 0

 

[Mikelambnj]

Won’t that just remove the rows with 0 And leave the rows where qty is greater than 0? If anyone of the qtys is 0 I need all the rows for that order number to be removed.

 

[Gasman]

Create a distinct query where qty is zero, you only need the order number.
Then create your query selecting all those not in the zero query. ?

 

[arnelgp]

you need to write a special function that will check in advance all the details of each invoice number if it contains at least a zero quantity.
you then plug this "special function" to your query to "filter" those invoices.
there would be two variation of this function. one that utilizes a Collection object, which is a little bit faster, but requires to reset the Collection everytime the query is run. You call the "reset" using a form.
the other variation is without the Collection object but will be very slow.

 

[Mikelambnj]

Create a distinct query where qty is zero, you only need the order number.
Then create your query selecting all those not in the zero query. ?

That is a great idea!! i will try that. Thanks so much!

 

[Mikelambnj]

you need to write a special function that will check in advance all the details of each invoice number if it contains at least a zero quantity.
you then plug this "special function" to your query to "filter" those invoices.
there would be two variation of this function. one that utilizes a Collection object, which is a little bit faster, but requires to reset the Collection everytime the query is run. You call the "reset" using a form.
the other variation is without the Collection object but will be very slow.

Thanks for your help! I’m setting this up for someone who is not Access savvy, so I think I will just have him run 2 queries: 1 a query that creates a table of distinct order numbers that have at least one zero qty, then a second query that gives the information needed, but removes the order numbers from the table created by the first query.

 

[The_Doc_Man]

Could do this with a sub-query.

SELECT ONZ.ORDERNUM, ONZ.QTY, other data FROM ORDERTABLE ONZ
WHERE ONZ.ORDERNUM NOT IN (SELECT OT.ORDERNUM FROM ORDERTABLE OT WHERE OT.QTY = 0 ) ;

Using ONZ and OT as "spot aliases" to keep the two sets of references to the same table from getting confused.

 

[Mikelambnj]

Could do this with a sub-query.

SELECT ONZ.ORDERNUM, ONZ.QTY, other data FROM ORDERTABLE ONZ
WHERE ONZ.ORDERNUM NOT IN (SELECT OT.ORDERNUM FROM ORDERTABLE OT WHERE OT.QTY = 0 ) ;

Using ONZ and OT as "spot aliases" to keep the two sets of references to the same table from getting confused.

Thanks! I’ll try it.