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
Result required: Should show me ABC as "in Process"Code: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")));
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.