Counting different values

[LDP]

Hello:

I have reviewed a number of threads and help files dealing with counting including...
703-669669 Expression showing count
958-658402 counting specific values using Sum (IIF(..
707-668215 similar using Dcount(
Help File.. Calculating a total or other aggregate values

I cannot find the correct expression to count the number of second order sort/groupings. For instance..

I have a monthly report based on a query that is grouped by week and sorted by document number.(second order)
The submittal document number (SD#) refers to a submission that may contain a number of related documents.
Each document has a record in the database, each SD# may have a number of records, a number of records
may have the same submittal number (SD#)
Our report lists the SD#'s (and the related documents to each SD#) received weekly over any monthly period.

I need to total the number of SD# 's submitted in the month as well as the overall total of records.

I'm not sure which expression to place in the control source of a text box to get these totals but I believe it is
a Sum(IIf expression that may read the SD# field of each record in the report and only count the number of different values not just total number of records. I've tried

=count([SD#])
in a text box in the footer but I still just get total records similar to
=count(*)
I've also tried a text box in the group section sum by group and overall to no avail

I'm trying to get...

Week of Oct 1

SD# X Dwg #123 etc
Dwg #902 etc.
SD#AB Dwg #287 etc
Dwg #983 etc.
Dwg #784

Week of Oct 6

SD# Xyz Dwg 138 etc
Dwg 289 etc.

SD#HJK Dwg 24b etc
Dwg 3982 etc.


(Report Footer)

Total Dwgs this month = 9
Total SD# this month = 4

I'm not sure what expression will give me the total SD# over the month.

(Basic design)
Report Header
Return Date (Week) Header (per Page)
Detail
SD# Dwg# Rev No. Drawing Title Received Forwarded ResponseDate Status
Page Footer
Report Footer
Total Shop Drawings This Month ..=count(*)
Number of SD# submitted ???

Thanks for your help

 

[jrbarnett]

Hi,

Go to tools -> sorting and grouping and add a group on the SD# with a footer.
In the group footer put =Count(*)

John

 

[LDP]

Thanks John

I'm a beginner in Access.

I still can't make it function.
I'm not sure where to add the footer. At the Report ruler intersection in Design mode?

SD# is already listed second in sorting/grouping
as Group Header No
Group Header No
Group on Each Value
Group Interval 1
Keep Together no

Anyway...
I placed the SD# group footer from the properties of the Detail section in the report.
It just manages again to give me a total number of records under each SD#.

example...
Week 1
SD#X DWGrg..
SD#X DWGgh6
SD#X DWG34f
Total SD# 3
SD#AB Dwg7uhg
SD#AB Dwgdvg
Total SD# 2

Again just adding up the records.

I'd like to add up the number of different SD# values.It's difficult to explain.

In the sample above there are 5 DWG's and 2 SD#'s , 3 DWG's have the same SD# value in the the SD# field and 2 other DWG's have a different value of SD# in the SD# field. there are only 2 different values in the SD# fields in this case.
I'm trying to understand the expression that will count different values in the SD# fields as (in the case above)2.

Thanks John for your reply