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

Mark,

I have to confess that I've resisted writing much SQL from scratch
because of all the blankety-blank typing one has to do! Then there are
the inevitable spelling mistakes. I don't mind using the View and
Sproc designer in Enterprise Manager because that's what it looks like
while designing an Access ADP. I have used visual query tools for so
long -- indeed, since 1985 and Paradox for DOS -- that that's the way
I think. With a complex query I'd be sure to mess up the JOINs.

The two queries I created to get the count were SELECT DISTINCT and
then COUNT. Regarding performance or best practice, the tables are so
small that it never occured to me to test their performance. The
largest table is maybe 2000 records. Maybe later I'll look at running
these queries multiple times in a loop and time them. Would that be
what you're looking after?

Steve Erbach
Neenah, WI


On Mon, 14 Feb 2005 11:37:55 +0000, Mark Breen <marklbreen at gmail.com> wrote:
> 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