Match same field in same column

[marlic_gayo]

i have following data

Company name	Financial period	description	status
ABC	2020	Project Handed over	Closed
XYZ	2021	Just started	In-Process
XYZ	2021	First obj done	In-Process
XYZ	2021	Project Handed over	Closed
ABC	2022	Just Started	In-Process

I have a master query(Main) which includes log entries of for each company, single company has multiple entries for example 1. when project started 2. step one 3 step two 4 completion.
Each entry has a field(status) which can either be selected (In-process or closed). so intuitively after multiple log entries only the recent entry (project handed over) should show status=closed while all other interim entries show status= in process.
So for any company which is not completed should not have any entry with status=closed. so basically i want to show the companies that are still in-process.

I have been running the following sql query

SELECT [Master Query].[Action Date], *FROM [Master Query]
WHERE ((([Master Query].Status)="In-Process") AND (([Master Query].[Company Name]) Not In (SELECT [Company Name] From [Master Query] where Status= "Closed")));

Result required: Should show me ABC as "in Process"
Results obtained: No company "in process"

This query returns me only those entries that do not have a subsequent status=closed entry. the code was working just fine for the whole year. Now since we have moved into new financial year, the code cannot accurately give results, because there will be again multiple entries for that same company where status=in process but since they have previous status=closed entry, the above code skips those companies.

I have another field [Master Query].[Financial Period] which can work as a differentiator, so i want to modify the above query so that it only matches status (In process or closed) of the same company and OF THE SAME YEAR.
So what i actually want the code to is the following:
Show me companies that have status=in process but if they have status=closed, do not show me those. do this for each financial periods do not run this over different financial periods

i have tried my best to explain my case. Please help me with this.

[June7]

With the given sample, exactly what do you want for output? Only the second ABC record?

[marlic_gayo]

Exactly yes, using the financial period as differentiator.

[June7]

Consider:

SELECT * FROM [TableName]
WHERE Status="In-Process" AND [Company Name] Not In (SELECT [Company Name] From TableName AS T WHERE Status = "Closed" AND T.[Financial Period] = TableName.[Financial Period]);

Strongly advise not to use spaces in naming convention.

[Minty]

Using the date as a grouping field, something like

SELECT [Company name],	Max([Financial period]), [description], [status] 
FROM [Master Query]
WHERE Status = "In-Process"
Group by [Company name], [description], [status]

This assumes [Financial Period] is a number not text.

Spaces in fields names should be avoided as it means you have to enclose them in [ ] all the time.

[marlic_gayo]

Hi noted for future.
The query still returns the same results.

[marlic_gayo]

Hi unfortunately its text field

[marlic_gayo]

Consider:

SELECT * FROM [TableName]
WHERE Status="In-Process" AND [Company Name] Not In (SELECT [Company Name] From TableName AS T WHERE Status = "Closed" AND T.[Financial Period] = TableName.[Financial Period]);

Strongly advise not to use spaces in naming convention.

Thanks Works like charm

[CJ_London]

given your data example this query

SELECT A.[Company name], A.[Financial period], Max(A.status) AS MaxOfstatus
FROM Table1 AS A LEFT JOIN Table1 AS B ON (A.[Financial period] = B.[Financial period]) AND (A.[Company name] = B.[Company name])
GROUP BY A.[Company name], A.[Financial period]
HAVING (((Max(A.status))="in-process") AND ((Min(B.status))<>"closed"));

returns

Company name	Financial period	MaxOfstatus
ABC	2022	In-Process

substitute your query name for the table name


However I suspect you are suffering from Excelitis, (using multiple steps when only one is required) query probably needs to be done as part of your original query or from it's data source. I note the example data does not include the field Action Date for example - if it did, you could simply look at the latest date per company or latest date per company and period

[orange]

You might also consider giving each Project a unique identifier.
I agree with :
---NOT using a naming convention that allows embedded spaces in object names,
---date/timestamp on status/action date.