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
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")