New Report

[chegra26]

I have a new report that I am trying to generate from a query based on two tables. The relationship between the two tables is a case number. The query works great but I am having problems with the grouping options in my report.
I need the report to Group by School, by Motive, by Gender and then calculate Race by Gender. The report needs to generate these totals for all schools even if the result is 0. Hope this makes sense.
I am trying to attached a picture of what the tables look like and what the report should look like but I don't know how to get it attached. The only attached option I have is from a URL. The picture is on my computer.

 

[chegra26]

 

[chegra26]

Here is a view of my tables, query and what the report should look like.
I hope I did this correctly
Any help is appreciated.
[RANGE=cls:xl2bb-100][XR][XH=cs:13]Excel 2010[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][XH]I[/XH][XH]J[/XH][XH]K[/XH][XH]L[/XH][/XR][XR][XH]1[/XH][XD=h:c]Table 1[/XD][XD=h:c][/XD][XD=h:r][/XD][XD=h:c]Table 2[/XD][XD=h:c][/XD][XD=h:r][/XD][XD=h:c]Query[/XD][XD=h:c][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]2[/XH][XD=h:l]Case Number[/XD][XD=h:l]Primary Key[/XD][XD=h:r][/XD][XD=h:l]Case Number[/XD][XD=h:l]Primary Key[/XD][XD=h:r][/XD][XD=h:l]Field[/XD][XD=h:l]Table[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]3[/XH][XD=h:l]School Year[/XD][XD=h:l]Text[/XD][XD=h:r][/XD][XD=h:l]Race[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]School Year[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]4[/XH][XD=h:l]School[/XD][XD=h:l]Text[/XD][XD=h:r][/XD][XD=h:l]National Origin[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]School[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]5[/XH][XD=h:l]Student[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Marital Status[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Female[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]6[/XH][XD=h:l]Victim[/XD][XD=h:l]Text[/XD][XD=h:r][/XD][XD=h:l]Sex[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Male[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]7[/XH][XD=h:l]Victim School[/XD][XD=h:l]Text[/XD][XD=h:r][/XD][XD=h:l]Sexual Orientation[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Race[/XD][XD=h:l]Table 2[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]8[/XH][XD=h:l]Female[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Gender Identity[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Sex[/XD][XD=h:l]Table 2[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]9[/XH][XD=h:l]Male[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Religion[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Disability[/XD][XD=h:l]Table 2[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]10[/XH][XD=h:l]Hispanic[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Disability[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Hispanic[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]11[/XH][XD=h:l]American Indian[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Appearance[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]American Indian[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]12[/XH][XD=h:l]Asian[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Impress[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Asian[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]13[/XH][XD=h:l]Native Hawaiian[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Mean[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Native Hawaiian[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]14[/XH][XD=h:l]AfrAmerican[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Gang Related[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]AfrAmerican[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]15[/XH][XD=h:l]Caucasian[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Gang Recruitment[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Caucasian[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]16[/XH][XD=h:l]MultiRace[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Prostitution[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]MultiRace[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]17[/XH][XD=h:l]LEP[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Another Reason[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]LEP[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]18[/XH][XD=h:l]IDEA[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]Unknown[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:l]IDEA[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]19[/XH][XD]504[/XD][XD=h:l]Yes/No[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD]504[/XD][XD=h:l]Table 1[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]20[/XH][XD][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]21[/XH][XD=h:l]Report Should look like this[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]22[/XH][XD][/XD][XD=h:c]Hispanic or Latino of any race[/XD][XD=h:c]American Indian or Alasks Native[/XD][XD=h:c]Asian[/XD][XD=h:c]Native Hawaiian or Other pacific
Islander[/XD][XD=h:c]African American[/XD][XD=h:c]Caucasian[/XD][XD=h:c]Two or More Races[/XD][XD=h:c]Total[/XD][XD=h:c]LEP[/XD][XD=h:c]Students with Disabilities (IDEA)[/XD][XD=h:c]Students with Disabilities (504)[/XD][/XR][XR][XH]23[/XH][XD][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]24[/XH][XD=h:r|fw:b][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]25[/XH][XD=h:r|fw:b][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]26[/XH][XD=h:r|fw:b][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]27[/XH][XD=h:l|fw:b]AES[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]28[/XH][XD=h:l]Students reported as harassed or bullied
on the basis of sex:[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]29[/XH][XD=h:l]Male[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]30[/XH][XD=h:l]Female[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]31[/XH][XD=h:l]Students reported as harassed or bullied
on the basis of race, color or national
origin: [/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]32[/XH][XD=h:l]Male[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]33[/XH][XD=h:l]Female[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]34[/XH][XD=h:l]Students reported as harassed or bullied
on the basis of disability:[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]35[/XH][XD=h:l|fw:b]Total[/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r|bc:d9d9d9][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH=cs:13][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[chegra26]

 

[Micron]

All looks very confusing and this: even if the result is 0 is unclear. What result? No records as a result of running your query? Nothing to total in the section?
I'm not seeing how any of your posted images helps figure out why your report does not total. If your query "works great" then the issue must be related to report design.
I get no indication of what you know related to report design, so I might be wasting time telling you that you need a textbox with an expression that sums or counts the report textboxes by name in a report grouping header or footer and you have to tell Access you want it to 'group by' with totals for each section.

I also get the impression you really should research 'database normalization' because your approach to table design appears to be based on a spreadsheet paradigm. The table design with all those races/genders is just not right. It might even be the cause of your problem.

 

[HiTechCoach]

chegra26,

I agree with Micron that the database not being properly normalized is a real problem. The current design will require a lot of extra queries to properly normalize the data for re0port.

To solve the requirement for "even if the result is 0" I would try changing the join type in the query between the two tables. The type yo want will be all records from table 1 and matching from table 2.

I need the report to Group by School, by Motive, by Gender

School name works since there is a field for that. You do not have fields named Motive or Gender. Gender appears to be multiple fields. That is a problem for grouping. I suspect Motive is also multiples and will have the same issue as Gender.


I do not see any reason Access can not easily handle what you need. The key will be in properly designing your tables so they represent the data (not a report). All the Yes/No fields are are huge read flag to design issues. Currently your table design is a report or spreadsheet not a Relational Database. If at all possible fix this now and everything else will be a lot easier.