Query/Reports that illustrate frequency

[chloe]

Please help



I finally finished my table and form with all the coding needed (yay!). However I'm trying to set up reports to print. I have a macro that will work to print them, but setting up the initial report has been problematic.

Report 1 - Frequency of Title
I want to have a report that has the title of a person making an inquiry (MD, DO, RN, PA, etc) - these values are in a list box on my form - so that I know that, like, in the month of January 4 MDs called in, 2 RNs, 6 PAs, etc. In addition to this, I have a yes no check box on my form asking if these titled values are faculty staff of my university. The problem I'm having is, the query is showing a title twice - I'm getting that 2 doctors inquired, and 2 doctors that were faculty inquired. I would rather the query show the TOTAL number of MD's that called together, and then the number of faculty that were those MD's as a subset. Make sense? Maybe I need to combine two queries, or it's something I can designate in the report, but I do not have any idea how to do this.


Example:

Title Total Requests Faculty Members Students

MD 3 1 1
Rn 3 1


So that would imply that of the 3 doctor request received, 1 was from faculty member, 1 from a student, 1 from an outside source. Make sense?


So, with that being said, I can get it to etiher do the total or it will list a title twice like this:
Example:

Title Total Requests Faculty Members Students

MD 2 1 1
MD 1

Where it shows 3 requests, but separates it out. Any fixes for this?

[June7]

Why does a field named Total of Title not actually have number value?

What are the SumOfCampbell fields? Are they calculated? What are the expressions? Why do these show as checkboxes? Why are they GROUP BY criteria?

BTW, I deleted your duplicate thread.

[chloe]

I tried to delete these photos because they were outdated. Here is an updated screenshot below. The title is a drop down menu listing professional title. The total is the amount of inquiries by title. The Faculty and Student fields are check boxes on a form and are meant to indicate if the inquiry title is from the school (faculty/staff) or an outside party.

[June7]

What happens if you remove the check fields from the query? If you want to do count of Yes checks, can't include the fields in GROUP BY, need an expression and Sum function. What are the original field names in the table this query is based on? Try

CountIsStudent: Abs([Student])

CountIsFaculty: Abs([Faculty])

Then select Sum function instead of Group By for the calculated fields.

[chloe]

The table these are coming from have the same names for the columns (Campbell Student, Campbell faculty, Title).

I basically need to know like that 10 doctors are calling in a month, 6 are faculty. But it won't break down like that. When I add columns with the expressions you suggested it populates funky. I'm sorry this is probably something really easy, it's just out of my scope of knowledge.

[chloe]

By organizing the data sheet I got it to do this:

:

[June7]

Didn't do all I suggested. The GROUP BY on the check fields must be removed. Delete those two columns from the grid.

However, if you do the grouping in report design, don't build query with grouping. The report will allow display of detail records as well as summary calcs in group and report sections.