Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

COUNT DISTINCT problem

128 views
Skip to first unread message

PatdeLux

unread,
Jan 14, 2008, 12:12:07 PM1/14/08
to
Hi all,

I have searched a lot about elegant workarounds to the lacks of
(COUNT(DISTINCT) in Access but haven't found an understandable reply
to my need (except in multiple queries)
Here is demand:
I have a Valuations table like this one:
ValDate Fund SubFund Amount
17/01/2008 AA A1 10
17/01/2008 AA A2 10.5
17/01/2008 AA A3 11
17/01/2008 BB B1 11.5
17/01/2008 BB B2 10
17/01/2008 BB B3 10.5
17/01/2008 BB B4 11
17/01/2008 CC C1 11.5
17/01/2008 CC C2 11
18/01/2008 BB B2 11.5
18/01/2008 BB B3 10
18/01/2008 BB B4 10.5
18/01/2008 CC C1 11
18/01/2008 CC C2 11.5
18/01/2008 AA A2 12
18/01/2008 DD D1 11
18/01/2008 DD D2 11.5
19/01/2008 AA A1 10
19/01/2008 AA A2 10.5
19/01/2008 BB B1 11
19/01/2008 BB B2 11.5
19/01/2008 BB B3 11.5

I want to calculate how many DISTINCT funds and subfunds I have
everyday, and get a result like this:
Date #Funds #SubFunds
17/1/08 3 9
18/1/08 4 8
19/1/08 2 5

I can do it with 2 queries but when can't get it working with 1 query
contining a subquery.
I tried something like this, but no success:
SELECT VDate, COUNT(*) as SubFundsCount, (SELECT COUNT(*) FROM (SELECT
DISTINCT fund FROM Valuations s WHERE s.VDate = main.VDate) as
FundsCount
FROM Valuations main;

Any idea or link ?

Thanks !

Klatuu

unread,
Jan 14, 2008, 2:02:07 PM1/14/08
to
You can do this in Access with a Totals query.
Create your query in the query designer.
Right click in the upper portion of the designer (where the tables are
shown) and select properties. Select Distinct Values.

Now click on the totals icon on the menu bar. It looks like the Greek
letter Sigma.
You will get another row in the designer that you use to define the out put.
Select Group by for all fields except the one you want to count and select
Count for that field.

--
Dave Hargis, Microsoft Access MVP

Roger Carlson

unread,
Jan 14, 2008, 2:22:54 PM1/14/08
to
Well, this works, though Lord knows it's not elegant:

SELECT Main.ValDate, (SELECT Count(Valuations.Fund) AS CountOfFund
FROM [SELECT Valuations.ValDate, Valuations.Fund
FROM Valuations
GROUP BY Valuations.ValDate, Valuations.Fund
ORDER BY Valuations.ValDate]. AS temp
WHERE temp.Fund=[temp].[fund] AND Temp.ValDate = Main.Valdate
GROUP BY temp.ValDate) AS CountOfFunds, Count(main.SubFund) AS
CountOfSubFund
FROM Valuations as Main
GROUP BY main.ValDate
ORDER BY main.ValDate;

The secret here is to return the Funds with a query like this:

SELECT Count(Valuations.Fund) AS CountOfFund
FROM [SELECT Valuations.ValDate, Valuations.Fund
FROM Valuations
GROUP BY Valuations.ValDate, Valuations.Fund
ORDER BY Valuations.ValDate]. AS temp
WHERE (((temp.Fund)=[temp].[fund]))
GROUP BY temp.ValDate;

which uses the bracket ([ ])nomenclature to use a query in the From clause,
and returns the count of the funds.

Then you have to integrate it with your main query as a subquery.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"PatdeLux" <patrick...@gmail.com> wrote in message
news:e50c01b9-2089-4e18...@i7g2000prf.googlegroups.com...

Roger Carlson

unread,
Jan 14, 2008, 2:42:35 PM1/14/08
to
Whups! Looking this over, temp.Fund=[temp].[fund] is superfluous, so you
can shorten it to this:

SELECT Main.ValDate, (SELECT Count(Valuations.Fund) AS CountOfFund
FROM [SELECT Valuations.ValDate, Valuations.Fund
FROM Valuations
GROUP BY Valuations.ValDate, Valuations.Fund
ORDER BY Valuations.ValDate]. AS temp

WHERE Temp.ValDate = Main.Valdate


GROUP BY temp.ValDate) AS CountOfFunds, Count(main.SubFund) AS
CountOfSubFund

FROM Valuations AS Main


GROUP BY main.ValDate
ORDER BY main.ValDate;

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"Roger Carlson" <RogerC...@noemail.noemail> wrote in message
news:%23agwdLu...@TK2MSFTNGP02.phx.gbl...

Michel Walsh

unread,
Jan 14, 2008, 3:05:49 PM1/14/08
to
A crosstab can do (from an original proposition from Steve Dassin) as long
as you count either the fund, either the subfund:

-------------------------------
TRANSFORM COUNT(*) AS theCell
SELECT ValDate,
COUNT(*) AS StandardCount,
COUNT(theCell) AS DistinctCount
FROM tableName
GROUP BY ValDate
PIVOT fund IN(Null)
-------------------------------

which, for each day (group), will return the number of records and the
number of different (distinct) funds.


Change

PIVOT fund IN(Null)

to

PIVOT subfund IN(Null)


to get the same, for sub-funds.

Hoping it may help,
Vanderghast, Access MVP


"PatdeLux" <patrick...@gmail.com> wrote in message
news:e50c01b9-2089-4e18...@i7g2000prf.googlegroups.com...

Michael Gramelspacher

unread,
Jan 14, 2008, 3:54:37 PM1/14/08
to
On Mon, 14 Jan 2008 09:12:07 -0800 (PST), PatdeLux <patrick...@gmail.com>
wrote:

maybe this is all you need:

SELECT ValDate, Count (*) AS [Daily Fund Count]
FROM (SELECT DISTINCT M.ValDate, M.Fund, M.SubFund
FROM Main AS M) AS A
GROUP BY ValDate;

ValDate Daily Fund Count
1/17/2008 9
1/18/2008 8
1/19/2008 5

PatdeLux

unread,
Jan 18, 2008, 4:22:25 PM1/18/08
to
On 14 jan, 21:05, "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
wrote:

> A crosstab can do (from an original proposition from Steve Dassin) as long
> as you count either the fund, either the subfund:
>
> -------------------------------
> TRANSFORM COUNT(*) AS theCell
> SELECT ValDate,
> COUNT(*) AS StandardCount,
> COUNT(theCell) AS DistinctCount
> FROM tableName
> GROUP BY ValDate
> PIVOT fund IN(Null)
> -------------------------------
>
> which, for each day (group), will return the number of records and the
> number of different (distinct) funds.
>
> Change
>
> PIVOT fund IN(Null)
>
> to
>
> PIVOT subfund IN(Null)
>
> to get the same, for sub-funds.
>
> Hoping it may help,
> Vanderghast, Access MVP
>
> "PatdeLux" <patrick.hono...@gmail.com> wrote in message

Thank you again ! (I replied the same day but I do not see the reply,
so I post it again).
Thnks you all for your nice an quick replies. I was really impressed
by the elegance of the Pivot Table solution, and also it is the
fastest one.
I have 300.000 rows on a network disk, so performance counts.
Patrick

0 new messages