There are many Ms Access Query to choose from that will manage your database reporting requirements but there are a handful of lesser known types of queries that you may not be aware of.
One in particular is a query called ‘Pass Through Query‘ as the following short video tutorial quickly explains…
Putting aside for the heavy American accent, it clearly explains the use for this type of query and the power behind how a server based process like SQL Server will take the work load and return the results back to your Microsoft Access database.
Microsoft Access Database: Pass Through Queries
There are a few caveats to know. Firstly, as the video mentions, you will need to create a DSN (ODBC) connection to your external database (which doesn’t have to be just SQL Server).
The other thing to note, is you can only create this type of query using the SQL View mode and not the built-in QBE grid (query by example grid) where most queries are designed.
The best approach therefore, would be to start to build a normal SELECT query using the QBE grid and then switch to the mode (command) Pass-Through which will automatically switch view to the SQL View and continue editing.
Also, note that Ms Access Query uses it’s own SQL language though a very minor variant to T-SQL (known as Access SQL) and some convention change may need to be edited namely replacing the underscore and/or using a dot separator (you will need to research and review this possible convention change first to be sure of your options).
This is just one of the many different types of queries you can utilise and the trick will be to study most to know how to best plan and build these reports.
Take a look at one of my eBooks on A Guide to More Ms Access Query – Releasing the Power of Access which will expand your knowledge base. All my eBooks can also be bundled together and have a 30 day money back policy plus email support too – give it a try!