Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Posts
    11

    Unanswered: Count rows in a report with unique field value

    I have a report of "transactions" in which I need to total the number of "documents", where there may be more than one transaction per document. I would like to just add a text box with a control source expression and not bother with an external query. Tried Dcount, but can't make it work using the dates in the query for the report. The report has a query that allows customer selection, then a selection for a range of dates for the report.
    Thanks if anyone can help. I have tried a separate query as the control source using the unique document id field twice, once to group and once to count, but I still get the total records in the file and not the # on the report. "Count" just gives the number of transaction records in the report.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Have you tried

    = Count(*)

    ? I think that might work.

    Sam

  3. #3
    Join Date
    Jan 2007
    Posts
    11

    thanks but

    thanks for trying to help, but where would i put the "count(*)" expression? In the text box, I doubt. In the query?
    thanks

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by jerrywheels
    thanks for trying to help, but where would i put the "count(*)" expression? In the text box, I doubt. In the query?
    thanks
    Certainly, as the Control Source in an unbound text box in the detail footer section. And don't forget the leading equals sign.

    Sam

  5. #5
    Join Date
    Jan 2007
    Posts
    11

    tried it

    Tried it, but it returned the number of transactions (44), not the number of unique documents (40). I have tried other ways too with the same result. You say put it in the "detail footer"? Not the report footer? How do I get a "detail footer" section?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you should be able to do it using a count in a report footer .... count say the product no in a report detail.... in reality it can be anything/control int hre report detail.. product description.. you name it.

    you can extend that same idea to group footers.......

    you could choose instead to do it using some code behind the report.. but if you are only using the one SQL aggregation then it shouldn't adversely affect performance
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    H'mmm. you'll need two levels of queries for this.

    Code:
    SELECT documents FROM tblTableName GROUP BY documents
    Call that query, for example, qryDocCntSub.

    Code:
    SELECT Count(documents) As CountOfDocs FROM qryDocCntSub
    can be your upper query.

    Or, you can save the sub query as a real query. The upper query can be the control source of your unbound textbox in the report's footer. However, in there, you must add equals and parentheses, i.e.
    Code:
    =(SELECT Count(documents) As CountOfDocs FROM qryDocCntSub)
    HTH,
    Sam

  8. #8
    Join Date
    Jan 2007
    Posts
    11

    sorry sam

    sorry sam, but I have tried this and still do not get what i need. This seems to be such a simple problem but no one can help me. If I were to send you the whole deal, fe and be, do you think you could figure it out? I can use the Count function in the detail line to count the # of "documents" for that line (always '1") and this returns the correct value; seems like i should just be able to sum that up, but when I do I still get the number of TRANSACTIONS and not the number of DOCUMENTS. Remember that there is a range of dates selected here. Actually, the COUNT (*) expression gives the number of transactions also, (all of these are in the report footer; don't know how to make a "detail" footer.
    thanks

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not knowing in detail how your report is set up..... but lets make some assumptions....
    you have a single report which is pulling off documents including transactions on
    each invoice
    you have a group footer for each document
    you have a report footer

    add text in a label that reads something like "this document comprises:"
    add a control in the document footer and set its data source to count([<transactionno>])
    add text in a label that reads something like "transactions:"

    add text in a label that reads something like "this report comprises:"
    add a control to the the report footer and set its data source to count([<documentno>])...
    add text in a label that reads something like "documents, and:"
    add a control to the the report footer and set its data source to count([<transactionno>])...
    add text in a label that reads something like "transactions:"
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Maybe a CZ&U (compact the db, zip it, and upload it to this site) is called for here, so we can get a better look at the situation. Okay with you, Jerrywheels?

    Sam

  11. #11
    Join Date
    Jan 2007
    Posts
    11

    Thanks, but I figured it out, by, of all things, using HELP

    Sam
    Thanks again for trying to help, but what I did was to Number each detail record in the group by making an invisible text box on the report that does this (ControSource is =1, RunningSum = Over Group, Name is txtItemNo, put "#" in the Format property box).
    Then I made another text box in the "report footer" section and just made the control source equal to the last "Numbered record" by making the controlsource "=txtItemNo".
    Thanks again
    Jerry Bennett

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •