counting unique values in a report

[Guest]

I rather rudely hijacked another thread with this problem. I'm sorry about
that. So I'm going to post this on it's own thread so I can stay off of the
other thread...

I have picked a lot of brains on this one and it seems that there just isn't
an easy way to do this... So I need real help, because I'm a relatively
novice user of Access (SQL is still rather foreign to me, so if this answer
is in SQL, I'm going to need the explaination in "Captain dummy talk" so I
can get it... sorry about that...), and this seems to be a complicated issue
(Which would really be easy if there was just a "UCount" function that
counted only unique values...)

I have a report that shows a number of pieces of information... The report
is based on a database which has one entry per transaction, thus there can be
many transactions per account.

Now my superiors want my reports to be based on the Account field, but they
still want a count of the transactions. To complicate things further; the
reports must be grouped on the Account field and a "Type" field, and
"Country" field... Currently, the reports have to be seperated into these
with Header/footers.

Okay, that said; I need to count the number of accounts as well on my report
(As well as the number of records/transactions) at the bottom of each footer
section. The report is set up with a "Type" header/footer, a "Country"
Header/footer and an "Account" header/footer. And this "Account" Count must
be in each footer.

So, in summary, I need a report that tells me the number of transactions
(Count([#ofTransactions]) ) as well as the number of accounts (????) grouped
on each Type and in each country, knowing that the Database has one entry for
each transaction and not for each account...

this is sorta forming up to be a "Holy Grail" for me now... I now have 5
seperate reports that need this function (Almost all of my reports need both
individual as well as combined numbers).

Thanks in advance... I really appreciate any help you can give...

---Guy

 

[Marshall Barton]

I rather rudely hijacked another thread with this problem. I'm sorry about
that. So I'm going to post this on it's own thread so I can stay off of the
other thread...

I have picked a lot of brains on this one and it seems that there just isn't
an easy way to do this... So I need real help, because I'm a relatively
novice user of Access (SQL is still rather foreign to me, so if this answer
is in SQL, I'm going to need the explaination in "Captain dummy talk" so I
can get it... sorry about that...), and this seems to be a complicated issue
(Which would really be easy if there was just a "UCount" function that
counted only unique values...)

I have a report that shows a number of pieces of information... The report
is based on a database which has one entry per transaction, thus there can be
many transactions per account.

Now my superiors want my reports to be based on the Account field, but they
still want a count of the transactions. To complicate things further; the
reports must be grouped on the Account field and a "Type" field, and
"Country" field... Currently, the reports have to be seperated into these
with Header/footers.

Okay, that said; I need to count the number of accounts as well on my report
(As well as the number of records/transactions) at the bottom of each footer
section. The report is set up with a "Type" header/footer, a "Country"
Header/footer and an "Account" header/footer. And this "Account" Count must
be in each footer.

So, in summary, I need a report that tells me the number of transactions
(Count([#ofTransactions]) ) as well as the number of accounts (????) grouped
on each Type and in each country, knowing that the Database has one entry for
each transaction and not for each account...

this is sorta forming up to be a "Holy Grail" for me now... I now have 5
seperate reports that need this function (Almost all of my reports need both
individual as well as combined numbers).

That's too complex for a newsgroup question, so how about I
just provide some pointers for you to work from.

First, break your problem down into smaller steps. Start
small and, when you get that working, build up from there.

Second, you can get the count of **detail** records
(transactions) in any group header/footer section by using a
text box with the expression =Count(*)

Third, you can get a count of group a group header/footer by
using a text box with the expression =1 and setting its
RunningSum property to Over Group or Over All, as
appropriate. The next "higher level" group header/footer
can then display the count by using a text box that refers
to the running sum text box.