Save As Query

There is a new functionality in Access 2007 that (as it happens a lot) is very low profile. Imagine that you are using a table and you hid some columns, applied filters and customized it to only show a very specific dataset.

As I don't need to tell you, this is the kind of stuff that takes time to do, and when you're done you don't want to do it again. Imagine that now you want to expose this specific dataset to a form or report.

If you created a query based on this table in previous versions of Access you would see the full table dataset. All your customizations would be lost or not applied by default. Argh.

What we've done is that you can now save the table as a query (File menu, Save As, select the type as query and type a name). The query will be created so that it matches the exact data set you have: columns that are hidden will not be included in the query and the filter will be made part of the query itself.

For example, let's say you have a table with two columns (Field1 and Field2) and you filtered so Field1 = "a" and hid Field2 and now saved it as a query. The query's SQL would look like this:

SELECT Table.Field1

FROM [Table]

WHERE (((Table.Field1)="a"));

Now you can create a new form or reports based off the query and re-use the customization you did over and over again. Pretty neat.

It's worth noticing that we made use of this idea of folding the filter into the query itself in other scenarios. But that's a story for another time...