Prev: Select, update query--same query, different result
Next: please explain [], . and !
From: Glenn on 12 Apr 2010 14:04
Hi,

I have the following fields (Acorn Catergory, CL Code).
I have a query that will group by Acorn Catergory then group by CL Code and
then count by CL Code.

This result shows me under each Acorn Catergory which CL Codes are there and
how many records that CL Code has entered for that Acorn Catergory. What I
want is to total the count of CL code under each catergory.

Please can you help
From: KARL DEWEY on 12 Apr 2010 14:37
Drop out the display for CL Code.

Are do you want both counts? For that you will either need a separate query
or DCount function.


--
Build a little, test a little.


"Glenn" wrote:

> Hi,
>
> I have the following fields (Acorn Catergory, CL Code).
> I have a query that will group by Acorn Catergory then group by CL Code and
> then count by CL Code.
>
> This result shows me under each Acorn Catergory which CL Codes are there and
> how many records that CL Code has entered for that Acorn Catergory. What I
> want is to total the count of CL code under each catergory.
>
> Please can you help
From: KARL DEWEY on 12 Apr 2010 15:47
Try this --
SELECT TableA.[Acorn Catergory], Count(TableA_1.[CL Code]) AS CountOfCLCode,
[TableA].[CL Code], Count(TableA.[CL Code]) AS CountOfCLcode1
FROM TableA INNER JOIN TableA AS TableA_1 ON TableA.[Acorn Catergory]=
TableA_1.[Acorn Catergory]
GROUP BY TableA.[Acorn Catergory], [TableA].[CL Code];

--
Build a little, test a little.


"KARL DEWEY" wrote:

> Drop out the display for CL Code.
>
> Are do you want both counts? For that you will either need a separate query
> or DCount function.
>
>
> --
> Build a little, test a little.
>
>
> "Glenn" wrote:
>
> > Hi,
> >
> > I have the following fields (Acorn Catergory, CL Code).
> > I have a query that will group by Acorn Catergory then group by CL Code and
> > then count by CL Code.
> >
> > This result shows me under each Acorn Catergory which CL Codes are there and
> > how many records that CL Code has entered for that Acorn Catergory. What I
> > want is to total the count of CL code under each catergory.
> >
> > Please can you help
 | 
Pages: 1
Prev: Select, update query--same query, different result
Next: please explain [], . and !