I am going to use the ArcGIS platform as a data source and visualization platform by using the Operations dashboard. But still, I have a concern that can I use complex queries and get all the questions that I need.
eg: I may need to have a unique field ID and sum of other numeric fields as I do in MS Access.
ArcGIS is not an RDBMS, it can be used to communicate with an RDBMS (SQL Server, Oracle, Postgres, etc.).
You will need to have the data published to ArcGIS Enterprise for the Ops Dashboard.