Hi,
Within each group? There is a solution, in Jet, but it is not intuitive,
necessary:
TRANSFORM COUNT(*) As c
SELECT f1,
COUNT(*) As TotalCountOf_f2,
COUNT(c) As DistinctCountOf_f2
FROM myTable
GROUP BY f1
PIVOT f2 IN(null)
It is based on that COUNT(*) would be displayed as NULL under each
new_crosstab_pivot_column:
TRANSFORM COUNT(*)
SELECT f1
FROM myTable
GROUP BY f1
PIVOT f2
and on that COUNT(fieldName) does not count the NUL, those nulls... So,
counting, on each group, the result of the tranformation, we need the alias,
here, c, to be able to reach it, finally supply us with the count of
distinct values in each group.
PIVOT f2 IN(null) is a elegant syntax (original idea due to Steve Dassin)
that removes the would be "created" columns, without "merging" them in one
big blob either, as far as the transform is concerned. (You have to see the
SQL statement as translated into an imperative sequence of operations, or
else, you are really doomed to understand, imho).
Hoping it may help,
Vanderghast, Access MVP
kevin said:
Hi I am extremely new to access. This is probably an easy question i have
a table and want to run a query where i group the first column and then
count the UNIQUE values in the next 2 columns. is there a way to count items
ignoring duplicate names?
