Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Total in Report Summary Needs to be Distinct Count

    My Access report is filtered based on dynamically built report criteria. When items are selected from the form's list box, a criteria statement is built, for example:
    SELECT [Client System] IN ("AS400","AS 400",...etc.
    This string is placed in the variable criteria, then the variable is used with the statement that opens the report:
    DoCmd.OpenReport "reportName", acViewPreview, acSaveNo, Criteria

    When I produce a summary report of two fields, [Client Name] and [Client System], there can be 5 unique rows correctly displayed on the report. On the source table, a client may be called more than one time, so there is a record for each call and there is duplication because some clients have been called more than once.

    The problem: how do I get the distinct count of client names to appear at the bottom of the report. In the example, where one client was called twice, the count displayed is 6 when it should be 5.

    I have created a string of SQL text to get the count I need, and if this is the solution, where do I go from there to get the total into the report?

    Thanks.
    Jerry

  2. #2
    Join Date
    Aug 2005
    Location
    Louisville, KY
    Posts
    28
    Would it be easier to group the report by client, and page break between, printing a seperate page for each client?

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    No, the reports are short. The multi-select list is being added so that several items can be selected because of spelling variation. It would be unreasonable to produce a report with one line to a page. Not sure how this would correct my total to display the count of distinct client names.

    The report is grouped and shows no duplicates. Its the total at the end that doesn't work because it counts the duplicates.

    Jerry

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I created a stored query with the two fields grouped, instead of using the table, which has duplicates, as the source for the report data. Counting records from this query gives me a distinct count in the report summary.

    Problem solved.
    JD

Posting Permissions

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