Access 2002 Query Question

[Guest]

I searched through the questions and didn’t see one that is similar to the
question in mind. I have the following in a table:

ID Project Completed
1 ABC No
1 DEF No
1 GHI Yes
2
3 JKL Yes
3 MNO Yes
3 PQR Yes

My report need to show the following results
1. List the ID with any outstanding projects.
2. For any IDs without an assigned project, I need to show as “no projects
assigned†along with the ID number.
3. For any status under the ID numbers showing completed, I need to have the
ID showing as “no projects assignedâ€.

For example using the table above, the resulting report will be:

ID ProjectStatus CompletedStatus
1 ABC No
1 DEF No
2 No Projects Assigned
3 No Projects Assigned

The table currently has over 4000 records. If there is an easier way to
accomplish this other than running multiple queries, that would be great.
Thanks!

 

[John Spencer]

SELECT ID, Project, Completed
FROM YourTable
WHERE Completed = "NO"

UNION ALL

SELECT ID, "No Projects Assigned", Null
FROM YourTable
WHERE Not Exists (SELECT * FROM YourTable as Y WHERE Y.ID = YourTable.ID
and Complete = No)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Hi John,
Thank you! It is working great!

SELECT ID, Project, Completed
FROM YourTable
WHERE Completed = "NO"

UNION ALL

SELECT ID, "No Projects Assigned", Null
FROM YourTable
WHERE Not Exists (SELECT * FROM YourTable as Y WHERE Y.ID = YourTable.ID
and Complete = No)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================