Query criteria by month (1 Viewer)

[tihmir]

Hi all. I am trying to solve the following problem. I have 2 tables - tblObjects and tblInspections. When I build query with that 2 tables
I want to create a criterion that allows me to filter for each type of object how many times in the quarter there are inspections - for each quarter (from 01.01. to 31.03.; from 01.04. to 30.06.; from 01.07. to 30.09. from 01.10. to 31.12.). Then to use the form fmFilter and when I set a year for example 2022 it shows me the corresponding frequency of inspections (1, 2, 3, n .... times a year)for each object

 

[XPS35]

I think your query should look like:

SELECT ObjectType, DatePart("yyyy",dateInspection) & "/" & DatePart("q",dateInspection) AS Quarter, inspectionType, Count(object_id) AS NoOfInspections
FROM tblObjects INNER JOIN tblInspections ON tblObjects.ID = tblInspections.object_id
GROUP BY ObjectType, DatePart("yyyy",[dateInspection]) & "/" & DatePart("q",[dateInspection]), inspectionType;

 

[tihmir]

I think your query should look like:

SELECT ObjectType, DatePart("yyyy",dateInspection) & "/" & DatePart("q",dateInspection) AS Quarter, inspectionType, Count(object_id) AS NoOfInspections
FROM tblObjects INNER JOIN tblInspections ON tblObjects.ID = tblInspections.object_id
GROUP BY ObjectType, DatePart("yyyy",[dateInspection]) & "/" & DatePart("q",[dateInspection]), inspectionType;

Thanks for your time and reply. I will try now

 

[arnelgp]

see crosstab query qryCrosstab.

 

[tihmir]

see crosstab query qryCrosstab.

Thanks for the help, arnelgp!
I will look into the request and may need to add more things.
Тhanks a lot again!