Report transition from Access to Crystal (multiple queries) I have just started a new job where I am responsible for building and improving reports.

There is one report I am trying to move from Access to Crystal.  The data is stored in an application called Acctivate.  The previous person who held my job built the report with a series of queries that built multiple tables, and then a final query which aggregated the information from the tables built by the first five queries.

The PTB want the report in Crystal so the management for each client can call up their info and run the report, rather than have to call me and have me do it.  

What is the best approach to this multi-level query (currently make-table) that will let me move the report out of Access?
Given that you have a SQL Server backend you have a few options depending how much freedom you have to add objects to the application db or on the server.

If you are not permitted to add objects to the native schema i would request a small report db on the same server where you can create views or stored procs that reference the source database schema using 3 part names.

If you can re-write the query and deploy it as a view that might be a good option.  If the query is too complex to be rewritten as a single query then you could reproduce the behavior of "make tables" with temorary #tables and deploy it as a stored procedure.

If you arent permitted to create objects on the server at all i would rewrite the query using #tables and embed the whole thing in the report query.

Views and SP's get better performance as the number of users/hits increase compared to embedded queries.