Tags: access, complicated, database, entire, exhibition, fiinancialinformation, hii, major, microsoft, mysql, oracle, report, returns, sold, sql, stands, unique, values

COunt Unique Values in Report

On Database » Microsoft Access

3,231 words with 2 Comments; publish: Sun, 01 Jun 2008 16:48:00 GMT; (25078.13, « »)

Hi

I have a very complicated (well to me it is) report which returns fiinancial

information on exhibition stands sold for a major show. The entire database

is on the principle unique invoice number equals a stand. CLient has just

informed me that very occasionally thye will sell additional space to the

smae client which results in additional invoice number but same stand number.

In my report I need to total the number of stands which is 4 less than the

number of invoices upon which the underlying query groups.

Is there a way in the report to Count Uniqu occurences? I tried inserting a

sub report which worked great and returned the correct figure but for some

reason when I ran the report asked for a variable value for a field which

definitely does not exist in the report - can't figure that out either! ANy

help much appreciated

Sheila

All Comments

Leave a comment...

  • 2 Comments
    • Without knowing your table structures, the queries used to gather the info,

      and the report layout, it is to difficult to offer a concrete answer.

      Please feel free to visit the Queries or Reports newsgroups to post more

      specific information about this objects.

      Steve Clark, Access MVP

      FMS, Inc.

      www.fmsinc.com/consulting

      "Sheila D" <SheilaD.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      news:2BA5A232-8F12-4D07-B28E-BFBD12C0860C.ms-access.questionfor.info.microsoft.com...

      > Hi

      > I have a very complicated (well to me it is) report which returns

      > fiinancial

      > information on exhibition stands sold for a major show. The entire

      > database

      > is on the principle unique invoice number equals a stand. CLient has just

      > informed me that very occasionally thye will sell additional space to the

      > smae client which results in additional invoice number but same stand

      > number.

      > In my report I need to total the number of stands which is 4 less than the

      > number of invoices upon which the underlying query groups.

      > Is there a way in the report to Count Uniqu occurences? I tried inserting

      > a

      > sub report which worked great and returned the correct figure but for some

      > reason when I ran the report asked for a variable value for a field which

      > definitely does not exist in the report - can't figure that out either!

      > ANy

      > help much appreciated

      > Sheila

      #1; Sun, 01 Jun 2008 16:50:00 GMT
    • Sheila, my guess would be to add DISTINCT to your SELECT statement...

      "[MVP] S.Clark" wrote:

      > Without knowing your table structures, the queries used to gather the info,

      > and the report layout, it is to difficult to offer a concrete answer.

      > Please feel free to visit the Queries or Reports newsgroups to post more

      > specific information about this objects.

      > --

      > Steve Clark, Access MVP

      > FMS, Inc.

      > www.fmsinc.com/consulting

      > "Sheila D" <SheilaD.ms-access.questionfor.info.discussions.microsoft.com> wrote in message

      > news:2BA5A232-8F12-4D07-B28E-BFBD12C0860C.ms-access.questionfor.info.microsoft.com...

      >

      >

      #2; Sun, 01 Jun 2008 16:51:00 GMT