Counting unique instances given another criteria

[gregorii77]

Hello! I am creating an Access report. Inside the report, I have created a box that will count the number of project inspections (based on Review type column) given a date. It is worthy to note that a single project inspection on a given day can have multiple findings. I am not interested in counting the number of findings but the number of inspections. Here is my data:

[TABLE="width: 1060"]
<tbody>[TR]
[TD="width: 103, bgcolor: silver"]ID
[/TD]
[TD="width: 140, bgcolor: silver"]FMIS Project Number
[/TD]
[TD="width: 99, bgcolor: silver"]Review Type
[/TD]
[TD="width: 199, bgcolor: silver"]Inspection Type
[/TD]
[TD="width: 109, bgcolor: silver"]Reviewer
[/TD]
[TD="width: 111, bgcolor: silver"]Review Date
[/TD]
[TD="width: 237, bgcolor: silver"]Findings / Observations? (Yes or No)
[/TD]
[TD="width: 208, bgcolor: silver"]Finding / Observation Number
[/TD]
[TD="width: 208, bgcolor: silver"]Finding / Observation
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]1
[/TD]
[TD="width: 140, bgcolor: transparent"]0004008
[/TD]
[TD="width: 99, bgcolor: transparent"]Environment
[/TD]
[TD="width: 199, bgcolor: transparent"]Env - Public Hearing/Meeting
[/TD]
[TD="width: 109, bgcolor: transparent"]Tribble
[/TD]
[TD="width: 111, bgcolor: transparent"]9/11/2018
[/TD]
[TD="width: 237, bgcolor: transparent"]Yes
[/TD]
[TD="width: 208, bgcolor: transparent"]1
[/TD]
[TD="width: 208, bgcolor: transparent"] ​Something was discovered
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: #D6DCE4"]2
[/TD]
[TD="width: 140, bgcolor: #D6DCE4"]0116896
[/TD]
[TD="width: 99, bgcolor: #D6DCE4"]Construction
[/TD]
[TD="width: 199, bgcolor: #D6DCE4"]Construction - Construction Quality
[/TD]
[TD="width: 109, bgcolor: #D6DCE4"]Casalone
[/TD]
[TD="width: 111, bgcolor: #D6DCE4"]9/2/2018
[/TD]
[TD="width: 237, bgcolor: #D6DCE4"]Yes
[/TD]
[TD="width: 208, bgcolor: #D6DCE4"]1
[/TD]
[TD="width: 208, bgcolor: #D6DCE4"]​Lots of thing were discovered.
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: #D6DCE4"]3
[/TD]
[TD="width: 140, bgcolor: #D6DCE4"]0116896
[/TD]
[TD="width: 99, bgcolor: #D6DCE4"]Construction
[/TD]
[TD="width: 199, bgcolor: #D6DCE4"]Construction - Construction Quality
[/TD]
[TD="width: 109, bgcolor: #D6DCE4"]Casalone
[/TD]
[TD="width: 111, bgcolor: #D6DCE4"]9/2/2018
[/TD]
[TD="width: 237, bgcolor: #D6DCE4"]Yes
[/TD]
[TD="width: 208, bgcolor: #D6DCE4"]2
[/TD]
[TD="width: 208, bgcolor: #D6DCE4"]​Something else was found.
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]4
[/TD]
[TD="width: 140, bgcolor: transparent"]0243095
[/TD]
[TD="width: 99, bgcolor: transparent"]Construction
[/TD]
[TD="width: 199, bgcolor: transparent"]Construction - Construction Quality
[/TD]
[TD="width: 109, bgcolor: transparent"]Bluhm
[/TD]
[TD="width: 111, bgcolor: transparent"]9/17/2018
[/TD]
[TD="width: 237, bgcolor: transparent"]No
[/TD]
[TD="width: 208, bgcolor: transparent"][/TD]
[TD="width: 208, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]5
[/TD]
[TD="width: 140, bgcolor: transparent"]0M2M001
[/TD]
[TD="width: 99, bgcolor: transparent"]Design / ROW
[/TD]
[TD="width: 199, bgcolor: transparent"]Env - Public Hearing/Meeting
[/TD]
[TD="width: 109, bgcolor: transparent"]Hamilton-Jones
[/TD]
[TD="width: 111, bgcolor: transparent"]8/27/2018
[/TD]
[TD="width: 237, bgcolor: transparent"]No
[/TD]
[TD="width: 208, bgcolor: transparent"][/TD]
[TD="width: 208, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: #D6DCE4"]6
[/TD]
[TD="width: 140, bgcolor: #D6DCE4"]0116896
[/TD]
[TD="width: 99, bgcolor: #D6DCE4"]Construction
[/TD]
[TD="width: 199, bgcolor: #D6DCE4"]Construction - Construction Quality
[/TD]
[TD="width: 109, bgcolor: #D6DCE4"]Casalone
[/TD]
[TD="width: 111, bgcolor: #D6DCE4"]9/2/2018
[/TD]
[TD="width: 237, bgcolor: #D6DCE4"]Yes
[/TD]
[TD="width: 208, bgcolor: #D6DCE4"]3
[/TD]
[TD="width: 208, bgcolor: #D6DCE4"]Something was found
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]7
[/TD]
[TD="width: 140, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]0116896
[/TD]
[TD="width: 99, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Construction
[/TD]
[TD="width: 199, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Construction - Construction Quality
[/TD]
[TD="width: 109, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Casalone
[/TD]
[TD="width: 111, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]9/16/2018
[/TD]
[TD="width: 237, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Yes
[/TD]
[TD="width: 208, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]1
[/TD]
[TD="width: 208, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]New problem
[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]8
[/TD]
[TD="width: 140, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]0116896
[/TD]
[TD="width: 99, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Construction
[/TD]
[TD="width: 199, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Construction - Construction Quality
[/TD]
[TD="width: 109, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Casalone
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] , align: right"]9/16/2018
[/TD]
[TD="width: 237, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Yes
[/TD]
[TD="width: 208, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]2
[/TD]
[TD="width: 208, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ACB9CA]#ACB9CA[/URL] "]Another problem
[/TD]
[/TR]
</tbody>[/TABLE]

So, as you see FMIS project number 0116896 has 2 Construction (see Review Type column) inspections. The first done on 9/2/2018 has 3 observations and the other was performed on 9/16/2018 and had 2 observations. How do I tell Access to count these as 2 inspections based on Review Type?

In the long run the total number of inspections for the whole sheet is as follows:

[TABLE="width: 182"]
<tbody>[TR]
[TD="width: 103, bgcolor: transparent"]Type of Review
[/TD]
[TD="width: 140, bgcolor: transparent"]# of Individual Reviews
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Environment
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Design / ROW
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Construction
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated. Thank you!
GS

 

[xenou]

Two parts - first resolve the unique values you want to count, then (second part) count them.

Using one query with a subquery:

SELECT 
	A.[FMIS_Project_Number], 
	A.[Review_Type], 
	A.[Review_Date], 
	Count(A.[FMIS_Project_Number) as Total
FROM
	(
	SELECT DISTINCT 
		[FMIS Project Number] as FMIS_Project_Number, 
		[Review Type] as Review_Type, 
		[Review Date] as Review_Date
	FROM Table1
	) A

Or using two actual queries:

Query1:
	SELECT DISTINCT 
		[FMIS Project Number] as FMIS_Project_Number, 
		[Review Type] as Review_Type, 
		[Review Date] as Review_Date
	FROM Table1

Query2:
	SELECT 
		[FMIS_Project_Number], 
		[Review_Type], 
		[Review_Date], 
		Count([FMIS_Project_Number) as Total
	FROM
		Query1

I generally like to rename fields between inner and outer queries with MSAccess when I am unable to test the queries so that's why I did that (Access seems to be picky about field names, more so than other databases).