Query Assistance - New User

[Hvns]

Hello everyone,

So it has been many years since i have cracked open access and have had to dive in to pull out some information needed to get me out of a bind.

I have a query that gives individual record information for projects. Each project has many lines and each line returns a value - Incomplete or Incomplete... See the example below for reference of the query that provides the base data (Example 1).

I am needing a query to return the results shown like Example 2 below - Letting me know if a project has the parts needed. Basically if all lines for a project are status complete return 1 line showing Complete, If any a single line of a project shows incomplete then return incomplete.

A project consists of Project+Line+Step = Project information. (5555.001.100 = 1 project / 4444.001.100 = 1 project / etc...)

Thank yo in advance for any help you can provide with this.

Example1 :

Project	Line	Step	Part#	Qty Needed	Qty Filled	Status
5555	001	100	T18R0	6	7	Complete
5555	001	100	A8897	5	5	Complete
4444	001	100	Z89763	5	5	Complete
4444	001	100	Z89769	10	5	Incomplete
4444	001	200	J5598	10	10	Complete
4444	001	200	J8799	2	2	Complete

Example 2:

Project	Line	Step	Status
5555	001	100	Complete
4444	001	100	Incomplete
4444	001	200	Complete

[June7]

Here is one way:

SELECT Project, Line, Step, IIf([StatusCount]=0,"Complete","Incomplete") AS Status
FROM (SELECT Project, Line, Step, Sum(IIf([Status]="Incomplete",1,0)) AS StatusCount
FROM table1
GROUP BY Project, Line, Step) AS Query1;