Yet another count distinct question

[jwalsh2000 via AccessMonster.com]

I've read through the many many threads already here on this subject but
can't seem to translate what I see in those answers into solving my query - I
realize that this is remedial work and appreciate any help I can get. I know
that I have to replace my count distinct with a count function and a subquery
that gets the distinct value, but I can't figure out how to write the
subquery and where to insert it in the rest of the query:


SELECT [Profile Balance Data Appended].[Asset Group], Sum([Profile Balance
Data Appended].[Net Worth]) AS [SumOfNet Worth], [Profile Balance Data
Appended].Office, Count(distinct [Profile Balance Data Appended].[Account #])
AS [CountOfAccount #]
FROM [Profile Balance Data Appended]
GROUP BY [Profile Balance Data Appended].[Asset Group], [Profile Balance Data
Appended].Office;

Many thanks,

Jen

 

[geebee]

Hi Jen,

Here is an example of a query containing a subquery, which you may want to
consider...

SELECT [Asset Group], Sum([Net Worth]) AS [SumOfNetWorth], Office,
Count([Account #])
FROM Profile_Balance_Data_Appended
WHERE [Account #]= (SELECT MAX([Account #]) FROM
Profile_Balance_Data_Appended);

First, it is NOT good idea to use spaces in ENTITY names, specifically table
names.
A subquery must return one and only one value. This query may or may not be
useful in your case, because all the information comes from one table, and it
ONLY returns ONE row. Are you sure that is what you want to use? Perhaps a
query based on another stored query would work better?
Please let me know exactly what you are trying to accomplish,
office-policy-wise, or what the client wants to see.

geebee

 

[Van T. Dinh]

You need to describe your Table structure and what you want the Query to
show in words.

Posting the SQL String that gives you in appropriate answer certainly won't
help (at least to me) potentail respondents to work out what you need.

 

[John Spencer]

That would look something like the following. I have aliased your table
(query) name to save myself a bunch of typing.
Note that this is untested and may fail. Avoid table and field names with
spaces and other non-alpha or numeric characters. They can make the use of
subqueries problematic.


SELECT [Asset Group]
, Sum([Net Worth]) AS [SumOfNet Worth]
, Office
, (SELECT Count(*) FROM
(SELECT DISTINCT [Account#]
FROM [Profile Balance Data Appended] as Temp1
WHERE Temp1.[Asset Group] = A.[Asset Group] AND
Temp1.Office = A.Office) as Temp 2) as NumberOfAccounts
FROM [Profile Balance Data Appended] As A
GROUP BY [Asset Group], Office;

 

[jwalsh2000 via AccessMonster.com]

Thanks to both - my table has, among other things, the fields Office, Asset
Type (4 values: Mutual Fund, Cash, Stock, Fixed Income), Account #, Investor
ID and Net Worth.

An investor can have multiple accounts and accounts can have balances (Net
Worth) in multiple asset types. There are 22K rows total (combo of investor,
account # & asset type), appx 5 K accounts and appx 800 investors.

The output needs to give me the number of unique accounts and sum of net
worth grouped by asset type and office.

Thanks,

Jen

You need to describe your Table structure and what you want the Query to
show in words.

Posting the SQL String that gives you in appropriate answer certainly won't
help (at least to me) potentail respondents to work out what you need.

I've read through the many many threads already here on this subject but
can't seem to translate what I see in those answers into solving my

[quoted text clipped - 19 lines]

Jen

 

[jwalsh2000 via AccessMonster.com]

Thank you John...that was what I was looking for - hopefully it will work!

Jen

That would look something like the following. I have aliased your table
(query) name to save myself a bunch of typing.
Note that this is untested and may fail. Avoid table and field names with
spaces and other non-alpha or numeric characters. They can make the use of
subqueries problematic.

SELECT [Asset Group]
, Sum([Net Worth]) AS [SumOfNet Worth]
, Office
, (SELECT Count(*) FROM
(SELECT DISTINCT [Account#]
FROM [Profile Balance Data Appended] as Temp1
WHERE Temp1.[Asset Group] = A.[Asset Group] AND
Temp1.Office = A.Office) as Temp 2) as NumberOfAccounts
FROM [Profile Balance Data Appended] As A
GROUP BY [Asset Group], Office;

I've read through the many many threads already here on this subject but
can't seem to translate what I see in those answers into solving my

[quoted text clipped - 19 lines]

Jen