Report with monthly counts (1 Viewer)

[RichO]

Hey all, I am having a bit of trouble figuring out how to do a specific type of report.

I want it to show totals (count of records) from most to least, broken down by month, like this:

January
-------
Apples 20 (20 records exist for apples in January, etc.)
Oranges 18
Bananas 12
Grapes 10

February
--------
Grapes 32
Oranges 24
Bananas 16
Apples 15


etc......

I have a report with a totals query as its source that does this correctly over the entire report but I can't figure out how to group it by month.

Thanks for the help

 

[pbaldy]

In Group & Sort, put a "Group On" before the sort, and use the date field. If it's not already the month, in the grouping options you can specify By Month.

 

[RichO]

In the totals query, what would I choose for the date field in the "total" row? Group By? The order of the fields in the query is important as well, isn't it?

 

[pbaldy]

If the query returns what you want, I wouldn't change it. You can do the grouping in the report. The order of fields in the query is not important to the report, other than as relates to sorting priority, which the report doesn't use anyway.

 

[RichO]

The query pulls the correct results until I add the date field, and since I need to group by month, I have to have the date field in there. It doesn't seem to affect the results when I select "First" or "Last" for the group option, however when I run it in the report grouped by month, the results are different than the query where some records are excluded and each field (apples, oranges, etc) appears only once in the entire report where it should appear in each month with that month's total.

 

[JHB]

Have you formatted the date field to show month?

.... Format([YourDate];"mm-yyyy")

Show your query-string.

 

[RichO]

SELECT Count(tb_Jobs.Contract_Price) AS CountOfContract_Price, tb_Jobs.Artist_Name, First(tb_Jobs.Job_Date) AS FirstOfJob_Date
FROM tb_Jobs
WHERE (((tb_Jobs.Job_Date) Between #1/1/2017# And #12/31/2017#))
GROUP BY tb_Jobs.Artist_Name
HAVING (((Count(tb_Jobs.Contract_Price))>0))
ORDER BY Count(tb_Jobs.Contract_Price) DESC;

This returns the proper results for the entire year. Breaking it down by month within the report is where it goes wrong.

 

[pbaldy]

Did you try this on the FirstOfJob_Date field?

In Group & Sort, put a "Group On" before the sort, and use the date field. If it's not already the month, in the grouping options you can specify By Month.

 

[RichO]

Yes, that is how I have it set:

This is the result of the query (partial):

This is for the entire year. When the report breaks it up by month, JOHNNY WAD only shows up in JANUARY, THE PRESIDENTS only show up under July, THE COUGARS only show up under AUGUST, etc. Each of these bands should be appearing in each month with their count for the month but they only appear once in the report, in whatever month shows in the date column. I am sure this is issue because of choosing "first" as the total option in the query but I am not sure how to fix that.

 

[pbaldy]

The report can't break down data that it doesn't have, which in this case is monthly data. Either add the field JHB suggested to the query and group on it, or take the totals out of the query and do it in the report.