Share via

Save As Query

  • Article

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...

Comments

  • Anonymous
    May 04, 2006
    Slightly (ok, completley!) off topic, but,... Why does Access put so many brackets into queries that it generates? :)
  • Anonymous
    May 04, 2006
    Because we love brackets. They are aesthetically pleasing.

    Seriously, from what I understand (I'm not the filtering/query King, but I know him) this is to facilitate parsing and comparing strings. Depending on some operations, things are bracketed to enforce a uniform representation of things (instead of considering how some things could be bracketed or not, we settle on bracketed because it always works. If a column had a space in it, we couldn't remove the bracket, but we can always have it there). I also believe that bracketing is also required by some of the expression evaluation, if you use that.

    That said, it's funny that you mentioned this, because we've been discussing exactly how to improve this going forward, but it's hard to give any generic veredict because our main concern is to avoid breaking solutions out there (which is much worse than having a query with superfluous brackets that works fine). If you know of some specific scenarios, feel free to send it my way.
  • Anonymous
    May 04, 2006
    so back on topic :-)

    looks like you have eliminated another set of functionality I have in my form extention class :-)

    while my implementation does not save a query I do store all the column, filtering and sorting stuff so people can see they data that way again and also apply it to reports.

    now back off topic again ... does the 2007 runtime save user's datasheet column settings?  That was what inspired me to write my extension classes, I also use them to track record creator, date, last editor and edit date which if I remember correctly you guys and gals are also building into 2007 for us ... or was that only if you are using Share Point?

    Steve
  • Anonymous
    May 04, 2006
    Hurray - less work you have to do.

    You need SharePoint to be able to track creator/date/etc automatically. What we do allow in Access 2007 without SharePoint is to track the history of a field (known as the AppendOnly property).
  • Anonymous
    May 05, 2006
    "Because we love brackets. They are aesthetically pleasing." - now that's a sig-file candidate if ever I saw one! ;)

    Back on topic - I do like the look of this particular bit of new functionality, can't wait to see it in action and determine if there are any benefits of it for me!