[AccessD] A2000: Am I blind? (swe)

Hello Steve,

I first discovered this a few years ago.  Because the SQL window in
Access is not a great text editor, we tend not to do much SQL editing
in there, is is also very easy to embed an existing query into an
Access QBE window.  However in the MS SQL environment, I have tended
to nest my queries like this without thinking about it.  I do not
think that it makes the query run much faster, but it does wrap it all
in one package.

Another aspect of laying out the queries in SQL is that the editor
encourages me to comment as I build the SQL string, this makes it
easier for me to later on figure out what I was doing.

Once you get used to embedding them, they can sometimes make us a
little lazy, i.e., we want some data, we just grab it in a nested
query and tie it in.  It may be that sometimes there is a more
efficient way to achieve that data!

Going back to your original question though, you mentioned that you
wished to avoid two queries.  Was the two queries you were referring
to the Count and the Group By?

I am presuming that you were asking the question as a best practice /
idiological aspect rather than a pure performance point.  What I am
curious about now is, which format do you think is more efficient,
Count and Group By in one query, or Select Distinct in one and count
in another?

Thanks for the interesting question.

Mark





On Sun, 13 Feb 2005 08:25:46 -0600, Steve Erbach <erbachs at gmail.com> wrote:
> Gustav,
> 
> Interesting. I've used subqueries many times as Criteria, but never in
> the FROM clause. It works.
> 
> My curiosity is aroused by what I found in the MSDN help files. Namely
> that T-SQL has a COUNT DISTINCT command. Of course, that doesn't
> translate to Access except that one does the kind of thing you
> suggested.
> 
> Thank you.
> 
> Steve Erbach
> Neenah, WI
> 
> 
> On Sat, 12 Feb 2005 14:23:04 +0100, Gustav Brock <Gustav at cactus.dk> wrote:
> > Hi Steve
> >
> > You can use a subquery as a subtable:
> >
> >   SELECT DISTINCT
> >     State
> >   FROM
> >     Addresses
> >
> > Then:
> >
> >   SELECT
> >     COUNT(*) As StateCount
> >   FROM
> >       [SELECT DISTINCT
> >         State
> >       FROM
> >         Addresses]. AS
> >     T;
> >
> > /gustav
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>