Help with Count - trickier than I imagined?!

[MrPete]

First time post here.

I've got what I thought was a relatively simple query... but not so much.

How do I fix this?

I have organization, account and investment tables. Multiple inv's per acct, multiple accts per org. Org's have names, accts (aka funds) have status and type, and investments have cost.

I have a nice summary report showing, for each organization and each account status and type, a summary of:
Org, Acct Status, Acct Type, # investments and total value

WHAT I NEED: instead of # investments, I need # accounts in each group (org/status/type).

MY PROBLEM: everything I've tried, I end up with # investments because the underlying grouping is on org/status/type

How do I condense the multiple investment records-per-acct so that I can get an account-count?

My closest thought, which didn't work, was to get
Count(First(account.acct_id))

...but you can't do that.

ANY IDEAS mucho appreciated!
Pete

SELECT org.nm AS Org, account.acct_stat AS fundStatus, account.acct_type AS fundType, Count(account.acct_id) AS hldgsCount, Sum(acct_inv_balance.cost) AS Total
FROM acct_inv_balance, org INNER JOIN account ON org.org_id = account.org_id
WHERE account.acct_id=[acct_inv_balance].[acct_id]
GROUP BY org.nm, account.acct_stat, account.acct_type
ORDER BY org.nm, account.acct_stat, account.acct_type;

 

[jzwp22]

You need a simple query to get the count of the accounts by organization. Something like this:


SELECT account.org_id, count(account.org_id) AS CountOfAccounts
FROM account
GROUP By account.org_id

Then create a new query that brings in your original query and this new account counting query and join them via the org_ID field.

 

[MrPete]

Mmmm... well, that query does not work, because it counts the wrong thing... but thanks anyway. The *concept* is sound.

So, I made a detail query that produces per-account data (summing all sub-account elements, and then another summary query that groups the detail query appropriately.

All done, quick and easy. Thanks!

Detail Query:

SELECT org.nm AS Org, account.acct_stat AS fundStatus, account.acct_type AS fundType, account.acct_id, Count(account.acct_id) AS hldgsCount, Sum(acct_inv_balance.cost) AS Total
FROM acct_inv_balance, org INNER JOIN account ON org.org_id = account.org_id
WHERE account.acct_id=[acct_inv_balance].[acct_id]
GROUP BY account.acct_id;

Summary Query:

SELECT Org, fundStatus, fundType, Count(acct_id) as fundCount, Sum(Total) as fundTotal
FROM [Detail Query]
GROUP BY Org, fundStatus, fundType
ORDER BY Org, fundStatus, fundType;

 

[jzwp22]

You're welcome. I'm glad you got it worked out.