Results 1 to 3 of 3
  1. #1
    babar_bhaai is offline Novice
    Windows XP Access 2003
    Join Date
    May 2016
    Posts
    2

    count unique record in report footer

    Dear All, Hope U all doin gr8
    i want to count all the unique records of a field in report footer
    sample attaced



    kindly guide

    Click image for larger version. 

Name:	report design.JPG 
Views:	23 
Size:	29.0 KB 
ID:	24957

  2. #2
    KeithSayers is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Apr 2016
    Location
    Canberra, Australia
    Posts
    5
    You seem to be not getting much help on this one ! Are you still in need of a solution ? Because if so I can offer something that seems to work for me.

    The figures you offered are too small for me to read so I have created a new table called Parts - assuming for the sake of exemplification that we are dealing with a range of part numbers any one of which can have one or more associated quantities. (Perhaps parts stored in several different warehouses.)

    My table has three fields :
    Key - Number - Quantity and the contents are :
    1 1 17
    2 12 3
    3 12 62
    4 23 14
    5 23 21
    6 23 35
    7 34 26
    8 34 8
    9 34 12
    10 34 50
    11 45 39
    12 45 16
    13 45 42
    14 45 57
    15 45 22
    - thus we can see we have 15 Number/Quantity entries spread over 5 part numbers - one to part number 1, 2 to part number 12, 3 to part number23, 4 to part number 34 and 5 to part number 45. Save the table and create a query based on it.

    Query 1 :
    Field : Number Quantity
    Table : Parts Parts
    Total : Group By Count

    This produces :
    Number Count of Quantity
    1 1
    12 2
    23 3
    34 4
    45 5
    Save that query and create another based on it.

    Query 2 :
    Field : Number
    Table : Query1
    Total : Count

    This produces :
    Count of Number
    5

    - which is the number you seek. Feed that into the query on which your report is based and you have your solution !
    Last edited by KeithSayers; 08-28-2016 at 04:47 AM. Reason: Respacing

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    there is no single phrase for Distinct Count as there is for Count - so you do have to do it outboard and then call into the report.

    I would suggest making a simple aggregate query (the sigma symbol like a big E) of the table with just Field 1 defaulting to 'group on' will result in the distinct values - save that as Q1.

    Then make another aggregate query using Q1 as the starting point but change the default from 'group on' to 'count' - this will result in a single row with the value you want - save that as Q2.

    Then in the Report Footer - add an unbound text box; put as its control: DLookUp("Q2FieldName", "Q2")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count Unique Records in a Report
    By Mohmand in forum Reports
    Replies: 2
    Last Post: 09-10-2014, 10:44 PM
  2. Replies: 2
    Last Post: 10-15-2013, 09:30 AM
  3. Using "Count" function in a report footer
    By GraeagleBill in forum Reports
    Replies: 5
    Last Post: 05-11-2013, 03:42 PM
  4. Count unique customers in report header
    By hithere in forum Reports
    Replies: 7
    Last Post: 02-08-2013, 12:47 AM
  5. Replies: 2
    Last Post: 06-05-2012, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums