Filtering query based on multiple rows values

[GeorgeB]

I think it probably simple but I can't think out of the box to find the solution




the data below is a sample of the table I have,

Contract Number	Section Number	Order Number	Order Status
20000	300	1	Withdrawn
20000	300	2	Withdrawn
20000	300	3	Completed
20000	300	4	Completed
20000	300	5	Approved
20000	300	6	Completed
20000	566	1	Approved
20000	322	1	Approved

what I am trying to achieve is extracting only the "section Number"s where all the "order status" are "completed" otherwise I don't want to know about it.

I tried group by "Completed" it didn't work

Thanks in advance

[June7]

Maybe just:

SELECT * FROM tablename WHERE [Order Status] = "Completed";

or

SELECT DISTINCT [Section Number] FROM tablename WHERE [Order Status] = "Completed";

or

SELECT [Section Number] FROM tablename WHERE [Order Status] = "Completed" GROUP BY [Section Number];

[GeorgeB]

Thanks June7 for your reply,
I tried all the three steps however it sill doesn't work.

For Eg: "Section Number" 300 still appear will not all the order status completed.

to elaborate in the example Section number 300 contains 6 orders.
I only want it to appear in my query if all the order from 1:6 have the field of order status completed

Once again thank you

[June7]

That means record selection is dependent on values in other records of the same table. That requires subquery or domain aggregate function (DLookup, DCount, etc).

Are section numbers used with multiple contracts? If not, try:

SELECT * FROM table WHERE SectionNumber Not In (SELECT DISTINCT SectionNumber FROM table WHERE OrderStatus <> "Completed");

[GeorgeB]

Thanks once again June7,
the Section Numbers are unique,

I tried the code you gave me but it causes Access to crash every time (the database got 85000+ row)
any thoughts

[June7]

I tested a similar query on a table of 67,000 records. The query opens but doesn't want to go to last record. It's just way too slow. When I changed the criteria so there were fewer records returned, it worked.

However, a table of 19,000 records works just fine.

Seems Access is choking on the Not qualifier. Alternatives:

1. Build an aggregate query

SELECT SectionNumber, Sum(IIf(OrderStatus<>"Completed",1,0)) FROM table AS CountNotComp GROUP BY SectionNumber;

Then join that query to the table and apply filter criteria of CountNotComp Is 0

SELECT table.*, CountNotComp
FROM Query1 INNER JOIN table ON Query1.SectionNumber = table.SectionNumber
WHERE (((Query1.CountNotComp)=0));

2. Try domain aggregate:

SELECT * FROM table WHERE DCount("OrderStatus","table","OrderStatus<>'Comple ted' AND SectionNumber='" & [SectionNumber] & "'") = 0;

[Dal Jeanis]

Hmmmm. You want only the section numbers where every Order Status = Complete"

SELECT
   Q1.Cont,
   Q1.Sect,
   T1.[Order Number] AS OrdNo,
   T1.[Order Status] As OrdStat
FROM 
   (SELECT
      T1.[Contract Number] As Cont,
      T1.[Section Number] As Sect,
      Sum(1) AS NbrOrders,
      Sum(IIF(T1.[Order Status] ="Completed",1,0) AS NbrCompleted
   From 
      MyTable AS T1
   GROUP BY
      T1.[Contract Number] As Cont,
      T1.[Section Number] As Sect
   ) AS Q1
   INNER JOIN 
   MyTable AS T2
   ON  T2.[Contract Number] = Q1.Cont
   AND T2.[Section Number] = Q1.Sect
WHERE Q1.NbrOrders = Q1.NbrCompleted;

Of course, replace MyTable with your table name, and fix any other table name issues you find.