Re: FLASH: OT: Access SQL question

[Cheri Harder]

I got this to work:


select ItemMasterID, count(ItemID) from inventory group by ItemMasterID


Query Results


 ItemMasterID   Expr1001

 1   96

 2   226

 4   2

 15   76


but not this:


select ItemMasterID, count(distinct ItemID) from inventory group by

ItemMasterID


so it's the distinct it doesn't like there.


This also works:


select itemid, count(itemid), count(itemmasterid) from inventory group by

itemid, itemmasterid


but I don't think it's the query you really want.


HTH?  Sounds like a "Tally" would be nice, but don't think it's available

with Access?  I'm just learning the limitations of Access, too!


~~~~Cheri Harder~~~~~

charderawsolution [dot] com

Advantage Web Solution

www.awsolution.com

----- Original Message -----

From: "Helen Triolo" <

designeri-technica [dot] com

>

To: <

flasherchinwag [dot] com

>

Sent: Thursday, June 22, 2000 5:01 PM

Subject: FLASH: OT: Access SQL question



> Sorry for the OT, though it's tangentially related (query for an asp

> called by Flash).  If I have two fields x and y, and there are multiple

> values of y for every value of x, what's the right Access query to get

> the count of y's for each x?  I tried

>

> select x, count(distinct y)

> from table

> group by x

>

> but Access won't run this query -- gives me an error.  Anyone know?

>

> Thanks,

> Helen




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