In my alteryx workflow, I have three tables. I have tried joining the three tables like how it was deigned in MS Access DB but no luck. I would like to join three data set with sql query copied from MS Access SQL view window. Is it possible to run copied query in alteryx with existing table.
It's been forever since I've used Access but maybe this is a useful post https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Access-SQL-conversions/td-p/642034
Maybe @GoldenDesign04 or @DanielG could provide more guidance?
@troy_mech
I am not SQL expert but I have heard that Access Query and SQL Query are not using the same syntax entirely.
For instance, MS Access requires parentheses around each join, which is not the case of SQL Query.
So you need to make sure the Access Query you made in Access will work as SQL query as well.
Here is the converter, havenot test it, but may help.
https://www.codeproject.com/Articles/5246516/MS-Access-Queries-to-SQL-Server-Converter
Post the SQL and I can take a look. Similar to the post that @joshuaburkhow linked in his comment, I am sure it can be translated rather easily.
You are correct that the SQL is slightly different in Access than it is in Alteryx.
I didnt check out @Qiu 's suggestion which might also work, but depending on the complexity of your query (or the # of queries you need to convert) might not be worth the trouble of building that out.
MS Access is like the mafia. I try to leave it in my past, but it keeps pulling me back in... 😀
I agree with @DanielG the SQL can be converted.
It varies wildly on what needs to be changed in the syntax from server to server. In Oracle, the schema and tables, in IBM Netezza the encapsulation is quite different and on depending on the syntax the server is
Best to see the query so we can walk you through what syntax might be the bothersome issue.
I have been converting too many MS Access queries as of late. It seems like I can't get myself out of it. But my troubles can be your gain, so @troy_mech add the additional detail so we can help!
Hi @GoldenDesign04 I'm trying to convert MS Access query to alteryx workflow.
SELECT FinalData.*, Client.[ID Name], Client.Type, Client.Collateral, Client.[Payment No] AS Payment, IIf([Group Relationship]="Y","Y","N") AS [Group Rel], RSDBdata.Business, RSDBdata.Manager, RSDBdata.business_type_2 AS [BusinessType 2],
FROM (FinalData LEFT JOIN RSDBdata ON (FinalData.code = RSDBdata.[Sort Code1]) AND (FinalData.refer_ID = RSDBdata.refer_ID_no)) LEFT JOIN Client ON (FinalData.refer_ID = Client.ReferID) AND (FinalData.code = Client.[Sort code]) AND (FinalData.ID_number = Client.CustID)
WHERE (((FinalData.ID_number) Is Not Null));
Does this do what you need?
Hopefully I got it at least close to correct for you. It is 9:45 on a Monday morning, so I cant fully guarantee my results. 🙂
Good luck.