Re: Simple count query not so simple...

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Chris E. (ChrisE_at_discussions.microsoft.com)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 09:13:05 -0700

Cool it seems to be working but what do i do with this line?

> PIVOT fieldName IN( null )
What is the fieldName? and what do i put in place of null?

Thanks for your help.

Chris Ehmen

"Michel Walsh" wrote:

> Hi,
>
>
> TRANSFORM COUNT(fieldName) As TheValue
> SELECT groupName, COUNT(TheValue) As DistinctCount, COUNT(*) As TotalCount
> FROM myTable
> GROUP BY groupName
> PIVOT fieldName IN( null )
>
>
> would return, per groupName, the number of distinct values of fieldName, and
> the number of records.
>
>
> Alternatively, you can use
>
> SELECT COUNT(*)
> FROM ( SELECT DISTINCT fieldName FROM myTable)
>
>
> to get the total number of distinct values of fieldName (no group).
>
>
>
> Hoping it may help,
> Vanderghast, Access MVP
>
>
>
> "Chris E." <ChrisE@discussions.microsoft.com> wrote in message
> news:40336592-F82A-42F4-AD0C-587164B45C98@microsoft.com...
> > Let's say I want to count the number of order lines for every order. So I
> > have an order that has 6 lines on it. I want to count the order as a
> > unique
> > identity but count all six lines so the query would return
> >
> > countofORDERS = 1
> > countofLINES = 6
> >
> > but what is happening is this
> >
> > countofORDERS = 6
> > countofLINES = 6
> >
> > It's counting the order # 6 times even though it's the same number.
> > is there such thing as a count distinct or something similiar? I could
> > not
> > find one.
>
>
>

Relevant Pages

  • Re: Field propagation
    ... in a query or as a filter like: WHERE FieldName Not Is ... Vanderghast, Access MVP ... > How do I keep an empty field from propagating to my form. ...
    (microsoft.public.access.formscoding)
  • Re: Simple count query not so simple...
    ... SELECT groupName, COUNTAs DistinctCount, COUNTAs TotalCount ... PIVOT fieldName IN ...
    (microsoft.public.access.queries)
  • Re: Simple count query not so simple...
    ... >> PIVOT fieldName IN ... >> Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Suppress "0" from printing on a report
    ... If that is the only information, you can remove the record with a WHERE clause, but that eliminates the whole record, that is why this is only applicable if this field is the only information to be printed in the 'row': WHERE fieldname 0 ... Vanderghast, Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: Keep trailing space in field
    ... < SQL statement...> ... WHERE FieldName Like ... <MS ACCESS MVP> ... Then let the query replace that $ with the space character: ...
    (microsoft.public.access.forms)