Have you ever needed to write a SQL query that you just
couldn't get right? Come to find out Access is pretty good
at it. Here's how to let it do the work for you.
Fire off a copy off Access and load the database (or
link to the tables) that you want to query.
Next go to the 'Queries' tab and design a new query.
Now create the query you want using the Access
visual tools. It can be as advanced as you want...
you can even draw relationships between tables, etc.
Once your query is created, simply run it and check the
data to be sure it's what you were expecting. Once
you've got the query correct, click on 'View' and select
'SQL View' (or right click and select it from the
All you need to do now is copy and paste the SQL statement
into your ASP script. If the query needs to take any
parameters you'll need to script those in, but for
getting the basic structure of the query down (especially
on those tricky joins) Access can make things much easier
Note: For those of you using VID 6
Enterprise Edition, it will do the same thing if you've
got the database tools installed. Simply create a new
query and design and test it just like you would in Access.
Once you're done, click the 'SQL' icon on the 'Query'
toolbar to get a look at the SQL it's written.
Thanks to David Mason for giving me the kick in the butt
that I needed to finally get this written.