Group Level Count

[cefrancke]

I can't seem to find a straight answer for my specific issue.
Any help would be appreciated.

I would like to count the various items in a table where the fields
have a 'group' relationship.
I would like to count the items per group.
(Please forgive the inappropriate use of database terms.)

An example follows:

F1 F2 F3
------------------------------
a 1 A
a 1 B
a 5 A
a 3 C
b 7 A
b 7 C
b 2 D
b 2 E
b 5 A
b 3 A
c 4 E
c 1 C
c 3 B

The information I need is...

F1 F2 F3
-------------------------------
a 3 4
b 4 6
c 3 3

Analysis:
---------------------------------------------------------------
The example uses simplified data, however, neither field has items that
could be ordered consecutively.

Of course, the 'c' group has one F2 item with only one F3 item.
I find this easy to query with a group by clause.
However, as you can see with group 'a' this is not the case.

Generally it seems easy (with a group by) to get the count on the
final level (field) with respect to any 'higher' level, however it
escapes me on how to get the 'in between' levels too, grouping of
course, on the highest level.
This way you have a count of each 'lower' level with respect to the
highest level in the query.
I suspect that I would have to have a 'sub query' for each 'in between'
level, looking up the key value and counting the items as usual.
In Access 2000, I tried a Dlookup on a 10k record table and this was
somewhat slow. I need to do this for multiple levels (>=3) and this
would become VERY SLOW. (I only tried it on one level, though.)

Is there is a general discussion on this issue?
In general, is there a way to do this for any amount of levels?
Is this possible, with an elegant query, or will it involve multiple
queries/sub queries?
I'm using Access 2000, however, any additional comments with respect to
SQL Server (cursors, perhaps) is ok.

Finally, I'm not sure how this complicates the issue, the Fields are
from joined tables, (I think outer right join) where there can be
nulls, therefore, the null should not be counted.

Any advice would be greatly appreciated.

TIA

[Bruno Campanini]

<ce*******@yahoo.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I can't seem to find a straight answer for my specific issue.
Any help would be appreciated.

I would like to count the various items in a table where the fields
have a 'group' relationship. [...] The information I need is...

F1 F2 F3
-------------------------------
a 3 4
b 4 6
c 3 3

Sure the above table of info is correct?
If so, how do you calculate them?

Bruno

[jimfortune]

ce*******@yahoo.com wrote:

I can't seem to find a straight answer for my specific issue.
Any help would be appreciated.

Using your example (called tblExample),

qry1:
TRANSFORM Count(tblExample.F3) AS [The Value] SELECT tblExample.F1,
Count([The Value]) AS DistinctF2Count, Sum([The Value]) AS
DistinctF1Count FROM tblExample GROUP BY tblExample.F1, tblExample.F2
PIVOT tblExample.F1;
qry2:
SELECT qry1.F1, Sum(qry1.DistinctF2Count) AS F2,
Sum(qry1.DistinctF1Count) AS F3 FROM qry1 GROUP BY qry1.F1;

yielded:
F1 F2 F3
a 3 4
b 4 6
c 3 3

When the last F3 value was nulled out in tblExample it yielded:
F1 F2 F3
a 3 4
b 4 6
c 3 2

When I changed qry1 to:
TRANSFORM Count(tblExample.F3) AS [The Value] SELECT tblExample.F1,
Count([The Value]) AS DistinctF2Count, Sum([The Value]) AS
DistinctF1Count FROM tblExample WHERE tblExample.F2 IS NOT NULL GROUP
BY tblExample.F1, tblExample.F2 PIVOT tblExample.F1;

and additionally nulled out an F2 value (10th one) I got:
F1 F2 F3
a 3 4
b 3 5
c 3 2
I'm not sure whether this technique can be generalized to multiple
levels or not but I start thinking about crosstab queries immediately
now whenever an 'in between' level grouping is desired. I didn't test
many cases where null values were involved but I feel confident that
it's close to what you need when grouping a few levels. Also, your
example counts didn't seem to differentiate duplicate F3 values.

James A. Fortune