I need Access/Azure advice

I don't know much about how access queries data i.e. where the selection/filtering is done.  With a SQL solution one typically creates your command and sends the command to be executed on the server and the server returns the results of the command.  What I probably would do in your shoes is get visual studio community edition and build a Lightswitch application.

On re-reading your question I see that you have stored procedures already, so you simply have to within access or a web application, set up the parameters for the query, invoke the stored procedure, collect and view the results.

"all the data has to come over before the queries restrict things"

You are quite correct, M$Access.Classic only supports a Client side cursor - no way around it.

M$ is heavily committed to upgrading all its database functionality to SQL Server in the long run. As a consequence, my most humble recommendation is you bite the bullet and get up to speed with SQL Server Stored Procedures as best you can.

There is a wealth of FREE courses available on youtube / the internet as well as expert assistance. If you have the money, buy a video course. Or, if you have the time enroll into a teacher led course at your local community college or accelerated bootcamp.

Transact SQL is a bit intimidating at first. But, with due diligence and patience you will have it down quickly enough.

Cheers!

"all the data has to come over before the queries restrict things"

 You are quite correct, M$Access.Classic only supports a Client side cursor - no way around it.

It depends what you mean by "all" the data.  Access attempts to make EVERY query a pass-through query.  That means the query along with its selection criteria is sent to the server and Access only gets back the selected data.  So if your query selects a single record, that is all that SQL server returns.  If your query has no selection criteria then you will get the entire table or the full joined results so you have control over how much data is actually retrieved.  There are things you can do to defeat Access and prevent it from sending the query to the server for processing.  For example, joining a SQL Server table to a local Jet/ACE table will result in Access requesting that all rows from the server side table be returned and Access will perform the join locally.  The other big problem is using User Defined Functions or VBA functions which have no SQL Server equivalent.   Since SQL Server doesn't know anything about VBA function, Access again has to request all data from the server and perform the query locally where it can use VBA to run the functions.

Even though Access attempts to make all your queries "pass through",  it chops them up and returns chunks of data at a time.  The process can be slow so the fewer records you return at one time the better.  All of my update forms have selection criteria to limit the main form records to only one record.  Reports should be bound to local "pass-through" queries or stored procedures.  Use views whenever possible to force joins to be done on the server.  

Using good client/server techniques, Access apps will be fine against databases stored on your LAN.  Once the database is in the cloud, all bets are off because the inherent speed of the internet is at best about 10% of the speed of a good LAN.  Microsoft wasted a lot of time developing web apps when they would have done us a better service by optimizing the Access interaction with SQL Server/Azure in the cloud.

People who take an Access app designed in the old school methodology of filtering and convert it to SQL server are always surprised by how slow the app becomes.  Access is tightly integrated with Jet/ACE but not with other RDBMS so you need to change your techniques for creating forms and large batch processes.  But, apps developed using good techniques will work well whether the BE is SQL Server or Jet/ACS as long as the BE is on the same LAN as the FE.

The only viable solution to share Access apps across a WAN is to use Citrix or RDP.  You can host these yourself or use a third-party service.  It gets expensive fast though and I don't know the current prices but once you get more than about 10 users, you probably are better off hosting Citrix yourself.

Yes remote app would work..

Citrix is based on RDP so yes, it would work and might be less expensive.  I'm not sure what Citrix brings to the table above and beyond RDP but it might be the number of concurrent users it supports.