cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simms7400
Post Prodigy
Post Prodigy

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:

 

SELECT
	[A1].[GovernanceStatus] AS [PFC Status],
	[I1].[AssetID]+"-"+[A1].[AssetAlias] AS [Asset],
	[I1].[GovernanceStatus] AS [PFI Status],
	[I1].[InvestmentID]+"-"+[I1].[InvestmentAlias] AS [Investment],
	[I1].[Investment_ShortName],
	[I1].[LeadIndication],
	[I1].[BusinessOwner] AS [R&D Unit],
	[I1].[Wave],
	[I1].[Prioritization]
FROM [rdInvestments] I1 INNER JOIN [rdAssets] A1 ON [A1].[AssetID] = [I1].[AssetID]
WHERE [I1].[AssetId] IN (
	SELECT [I2].[AssetID]
	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!

0 REPLIES 0

Helpful resources

Announcements
Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,248)