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:

ID	FMIS Project Number	Review Type	Inspection Type	Reviewer	Review Date	Findings / Observations? (Yes or No)	Finding / Observation Number	Finding / Observation
1	0004008	Environment	Env - Public Hearing/Meeting	Tribble	9/11/2018	Yes	1	​Something was discovered
2	0116896	Construction	Construction - Construction Quality	Casalone	9/2/2018	Yes	1	​Lots of thing were discovered.
3	0116896	Construction	Construction - Construction Quality	Casalone	9/2/2018	Yes	2	​Something else was found.
4	0243095	Construction	Construction - Construction Quality	Bluhm	9/17/2018	No
5	0M2M001	Design / ROW	Env - Public Hearing/Meeting	Hamilton-Jones	8/27/2018	No
6	0116896	Construction	Construction - Construction Quality	Casalone	9/2/2018	Yes	3	Something was found
7	0116896	Construction	Construction - Construction Quality	Casalone	9/16/2018	Yes	1	New problem
8	0116896	Construction	Construction - Construction Quality	Casalone	9/16/2018	Yes	2	Another problem

<tbody>
</tbody>

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:

Type of Review	# of Individual Reviews
Environment	1
Design / ROW	1
Construction	3

<tbody>
</tbody>

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