COUNT DISTINCT problem

P

PatdeLux

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 !
 
K

Klatuu

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.
 
R

Roger Carlson

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
 
R

Roger Carlson

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 said:
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 said:
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 !
Click to expand...
Click to expand...
 
M

Michel Walsh

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
 
M

Michael Gramelspacher

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 !
Click to expand...

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
 
P

PatdeLux

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
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

show top values 5
Advanced problem using auto-number and datediff to find readmissio 1
Crosstab Query based on Ordinal Values 6
first record query 1
Find then highlight in yellow 6
Trying to recreate bootable CD from zipped files 14
Data timesheet makeover 4
SumProduct Help 1

Top