Re: FLASH: OT: Access SQL question

[Helen Triolo]

OK, it's still OT, but I have to post this answer sent offlist by Gary

DeJarnett because it works so beautifully and I know that at least one

other person on the list will find it useful.  I was looking for an SQL

query that would run in Access and provide a count of how many unique

values of Field2 there are for each value of Field1 in a table.  Many

thanks, Gary.


This will do the job (the key to it all is to use a subquery to gather

up the unique rows, then count them with the outer query):


SELECT [_Alias].UniqueRows, Count(*) AS CountOfUniqueField2s

FROM [SELECT Table1.Field1 as UniqueRows, Table1.Field2,

Count(Table1.Field2) AS LineCount

FROM Table1

GROUP BY Table1.Field1, Table1.Field2]. AS [_Alias]

GROUP BY [_Alias].UniqueRows;


For this example data:


  Field1=1 Field2=2

  Field1=1 Field2=5

  Field1=1 Field2=5

  Field1=1 Field2=3


  Field1=2 Field2=7

  Field1=2 Field2=7

  Field1=2 Field2=5


The query returns:


UniqueRows      CountOfUniqueField2s

    1                3

    2                2


----------------------------------------

 helen triolo  �  http://i-technica.com

designeri-technica [dot] com

 � 301-424-6037

----------------------------------------


flasher is generously supported by...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     flashforward2000 and the Flash(tm) Film Festival

 July 24-26, 2000, NEW YORK CITY, Hammerstein Ballroom

                  www.flashforward2000.com

   Produced by United Digital Artists and lynda.com

 Sponsored by Macromedia, Adobe Systems, Fusion, Inc, AtomFilms,

           shockwave.com and Electric Rain.

 1.877.4.FLASH.4  or (1.805.640.6679 outside the US and Canada)

 Register before June 30 and save $200!!-- www.flashforward2000.com

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To unsubscribe or change your list settings go to

http://www.chinwag.com/flasher or email

helpchinwag [dot] com