Hello,

I have a query with the following information.

Project # Work Status SheetID
1 Complete 1
1 Complete 2
2 Complete 3
2 Open 4
2 WIP 5
3 Complete 6
3 New 7

Is there any way to create a column in the query that would give me a Project Status. I would like the results to look like this.

Project # Work Status Project Status SheetID
1 Complete Complete 1
1 Complete Complete 2
2 Complete WIP 3
2 Open WIP 4
2 WIP WIP 5
3 Complete WIP 6
3 Complete WIP 7


I want the Project Status only to equal complete if each work status is complete.


I'm open for a different approach.

3 Complete WIP 7

How does the above happen when the original said "3 New 7"?

That is a mistake...it should have said New, which I want to equal WIP.

Sorry for the confusion.

This post has been edited by deckerf: Jun 4 2012, 07:04 AM

That makes sense.

Are the Sheet IDs sequential?

Are there any other Work Status not shown?

Is there a distinct order of Work Status that must be followed in a certain order or can there be backtracking?

You say that what's below is the results of a query. Please provide the SQL statement for that query. Also list any primary key field names for any tables involved.

Tables

Projects (Table)
PKProject - AutoNumber
ProjectName - Text

Status (Table)
PKStatus - AutoNumber PK
Status - text

Sheets (Table)
SheetID - AutoNumber PK
FKProject - Int
FKStatus - Int

I'm tracking some other fields in the Sheet table, but its not needed for status update. Each Project can have multiple sheets so I want to use a query to look at all of the sheets for one project and if all of the status for that project are Complete I want to create a Project Status that is complete. If any of the sheets for that project are not equal to Complete, then I want the Project Status to be WIP. Project Status can only equal Complete or WIP. Status table contains 5 to 6 different types of status, but to make it easy I'm really focused on one out come. One Project and all of its sheets must be equal to complete to have a Project Status complete.

Hope this is helpful.

I think that I can help. Please provide me with the SQL statement for the query that you have now. Also provide the name of the query in case I find it easier to build a query upon a query.

Here's something that might work. Change the Deckerf's to the name of the query.

SELECT Deckerf.[Project #], Deckerf.[Work Status], DeckGB.[Project Status], Deckerf.SheetID
FROM Deckerf, (SELECT [Project #], IIf(Min([Work Status])=Max([Work Status]),"Complete","WIP") AS [Project Status]
FROM Deckerf GROUP BY [Project #]) AS DeckGB
WHERE (((Deckerf.[Project #])=[DeckGB].[Project #]));

The possible problem is if a Project # could have something like only Open's or WIP's without a Complete.