Converting MS Access SQL Query into Power Automate
HI Folks -
My "processing" tool for my SharePoint List is MS Access. Within MS Access I also have a few daily reports that go out based on SQL queries I have established. In an effort to try and consolidate these types of notifcations to Power Automation, I'm looking for suggestions. While the query itself isn't complex, I have no idea on how to begin building a flow that would yeild me the same results.
Just for visibility, here is the logic:
[A1].[GovernanceStatus] AS [PFC Status],
[I1].[AssetID]+"-"+[A1].[AssetAlias] AS [Asset],
[I1].[GovernanceStatus] AS [PFI Status],
[I1].[InvestmentID]+"-"+[I1].[InvestmentAlias] AS [Investment],
[I1].[BusinessOwner] AS [R&D Unit],
FROM [rdInvestments] I1 INNER JOIN [rdAssets] A1 ON [A1].[AssetID] = [I1].[AssetID]
WHERE [I1].[AssetId] IN (
FROM [rdInvestments] I2 INNER JOIN [rdAssets] A2 ON [A2].[AssetId] = [I2].[AssetID]
WHERE [A2].GovernanceStatus IN ('Active','Support','On hold')
GROUP BY I2.AssetID
HAVING SUM(IIF(LeadIndication <> 'FALSE', 1, 0)) = 0
This is certainly something more complex than an ODATA filter query could handle. Would an option be populating a SharePoint List with the results of the above query and then just use Power Automate to pull directly from the table (i.e. not neding any complex logic)?
But I was hoping to build all of this logic out in flow itself, removing the MS Access piece all together. But understand if that's not doable, just need some guidance. Thank you, all!