Wanting a Query to Total the Number of Specific Issues Per Month

[Af7082a]

I used this first code to get the total number of issues per month based upon the reporting date



SELECT Count([Employee Complaints Database].[Reporting Date]) AS [CountOfReporting Date]
FROM [Employee Complaints Database]
HAVING ((Year([Reporting Date])=Year(Now()) And Month([Reporting Date])=Month(Now())));

This gave me a total and worked fine.

Now I'm trying to get specific with the number of issue types per month and I'm having a syntax error.
Could anyone help?

This is the second code tried:

SELECT Count([Employee Complaints Database].[Issue Type]), AS Occurrences
FROM [Employee Complaints Database]
HAVING ((Year([Reporting Date])=Year(Now()) And Month([Reporting Date])=Month(Now())))
GROUP BY [Employee Complaints Database].[Issue Type];

[ranman256]

you need to add the month in the query fields,
(no comma before AS OCCURRENCES)
And Id think youd want a data range to limit the data.

SELECT Count([Employee Complaints Database].[Issue Type]) AS Occurrences
format([reporting date],"yyyy-mm") as RptMonth
FROM [Employee Complaints Database]
where [reporting date] between forms!myForm!StartDate and forms!myForm!EndDate

[ssanfu]

Just an FYI: you should delete ALL SPACES in object names.
You have a field name "[Reporting Date]"
and a table name "[Employee Complaints Database]"

Better is
"[ReportingDate]" or "[Reporting_Date]"
"[EmployeeComplaintsDatabase]" or "[Employee_Complaints_Database]"


Object names mean fields names, table names, form names, query names report names and module names.



Also, please use code tags when posting code/SQL.
You posted
SELECT Count([Employee Complaints Database].[Reporting Date]) AS [CountOfReporting Date]
FROM [Employee Complaints Database]
HAVING ((Year([Reporting Date])=Year(Now()) And Month([Reporting Date])=Month(Now())));


Should be (easier to read and it keeps formatting) :

SELECT Count([Employee Complaints Database].[Reporting Date]) AS [CountOfReporting Date]
FROM [Employee Complaints Database]
HAVING ((Year([Reporting Date])=Year(Now()) And Month([Reporting Date])=Month(Now())));

Paste in the code, highlight it and press the "#" in the menu.

[Af7082a]

Hi Thank you! I got it to work. But now I'm trying to print a report that contains three sub reports and it keeps printing the sub reports twice in print mode, but it shows normal in the report mode. Any idea on what might be causing this?


Object names mean fields names, table names, form names, query names report names and module names.



Also, please use code tags when posting code/SQL.
You posted
SELECT Count([Employee Complaints Database].[Reporting Date]) AS [CountOfReporting Date]
FROM [Employee Complaints Database]
HAVING ((Year([Reporting Date])=Year(Now()) And Month([Reporting Date])=Month(Now())));


Should be (easier to read and it keeps formatting) :

SELECT Count([Employee Complaints Database].[Reporting Date]) AS [CountOfReporting Date]
FROM [Employee Complaints Database]
HAVING ((Year([Reporting Date])=Year(Now()) And Month([Reporting Date])=Month(Now())));

Paste in the code, highlight it and press the "#" in the menu.[/QUOTE]

[June7]

The subreports are in Detail section? Main report is bound? How many records does the main report retrieve?

[Af7082a]

The subreports are in Detail section? Main report is bound? How many records does the main report retrieve?

The Sub Reports are in the detail section. I'm not sure if the main report is bound. And the main report is not receiving records but instead receiving averages and totals.

Thank you!

[June7]

Does the main report have a RecordSource? If it does, then it is bound.

Subreports will repeat for each record in the main report.