Group Counting in a Report

[Huddle]

Not sure how to accomplish what I am trying to do.



I've attached an example.

I have a group of inspectors who are assigned a group of buildings. I want to show a count of how many each inspector has. The end result should look like this:

Jones FSL 0: 0 FSL 1: 0 FSL 2: 1 FSL 3: 2 FSL 4: 2 Total: 5

Smith FSL 0: 0 FSL 1: 1 FSL 2: 2 FSL 3: 2 FSL 4: 1 Total: 6

White FSL 0: 1 FSL 1: 3 FSl 2: 0 FSL 3: 0 FSl 4: 0 Total: 4

[June7]

What is the structure of the source dataset?

Could a crosstab query accomplish this?

[Huddle]

Sorry I forgot to attach the example.

[June7]

You have Tbl_Building_Information primary key as text field with values 1 through 15. This data will not sort properly. 15 will sort before 2. Need place holder zeros, like: 01, 02, ... 15. Also Bldg_Number 10 does not have an FSL value. What is FSL?

Each building can have only one inspector associated with it? Why not have field in Bldg_Number for InspectorID instead of building number in Connector table?

Crosstab based on current data structure:
TRANSFORM Count(Tbl_Bldg_Insp_Connector.Connecting_Bldg) AS CountOfConnecting_Bldg
SELECT Tbl_Bldg_Insp_Connector.Connecting_Inspector, Count(Tbl_Bldg_Insp_Connector.Connecting_Bldg) AS [Total]
FROM Tbl_Building_Information INNER JOIN Tbl_Bldg_Insp_Connector ON Tbl_Building_Information.Bldg_Number = Tbl_Bldg_Insp_Connector.Connecting_Bldg
GROUP BY Tbl_Bldg_Insp_Connector.Connecting_Inspector
PIVOT "FSL" & [FSL];

[Huddle]

This is only and example of a much larger database so the connector table is necessary.

I had never done a crosstab query before but it looks like that's just what I need. Thanks so much.

[Huddle]

Can you not do a parameter in a crosstab query?

[Huddle]

Is there a way to total the columns as well so I can get an average?

[Huddle]

So I'm trying to do what I need as a Union but it's telling me "Characters found after end of SQL statement". Can you help me out?

TRANSFORM Nz(Count(Tbl_Bldg_Insp_Connector.Connecting_Bldg), 0) AS CountOfConnecting_Bldg
SELECT Lookup_Tbl_Commander_Inspector.Commander, Count(Tbl_Bldg_Insp_Connector.Connecting_Bldg) AS Total
FROM Tbl_Building_Information INNER JOIN (Lookup_Tbl_Commander_Inspector INNER JOIN Tbl_Bldg_Insp_Connector ON Lookup_Tbl_Commander_Inspector.Inspector = Tbl_Bldg_Insp_Connector.Connecting_Inspector) ON Tbl_Building_Information.Bldg_Number = Tbl_Bldg_Insp_Connector.Connecting_Bldg
WHERE (((Tbl_Building_Information.Status)="Active") AND ((Tbl_Building_Information.District)="D1"))
GROUP BY Lookup_Tbl_Commander_Inspector.Commander, Tbl_Building_Information.Status, Tbl_Building_Information.District
PIVOT "FSL" & [FSL];
Union all;
TRANSFORM Nz(Count(Tbl_Bldg_Insp_Connector.Connecting_Bldg), 0) AS CountOfConnecting_Bldg
SELECT "FSL" & [FSL], Count(Tbl_Bldg_Insp_Connector.Connecting_Bldg) AS Total
FROM Tbl_Building_Information INNER JOIN (Lookup_Tbl_Commander_Inspector INNER JOIN Tbl_Bldg_Insp_Connector ON Lookup_Tbl_Commander_Inspector.Inspector = Tbl_Bldg_Insp_Connector.Connecting_Inspector) ON Tbl_Building_Information.Bldg_Number = Tbl_Bldg_Insp_Connector.Connecting_Bldg
WHERE (((Tbl_Building_Information.Status)="Active") AND ((Tbl_Building_Information.District)="D1"))
GROUP BY Tbl_Building_Information.Status, Tbl_Building_Information.District, "FSL" & [FSL]
PIVOT Lookup_Tbl_Commander_Inspector.Commander;

[Huddle]

Figured out the error but it will never work because the rows and columns will never be the same number.

[Huddle]

So I re-worked my example with everything I could figure out so far. Here are the issues I've come across.
1. The sub reports are not grouping
2. I don't know how to get the totals or averages needed for the report
I'm sure there is a better way to do this but I wanted you to see what I'm ultimately wanting to accomplish so you could point me in the right direction.

[June7]

Why do some records in Tbl_Building_Information not have an FSL code? Do you need to include those in the report? How many possible FSL codes will there be?

[Huddle]

Some have 0 FSL because they haven't been rated yet and we do need those in the report. There are only 5 possible ratings 0-4.

[Huddle]

Figured out why the subreports were not grouping.. I needed to have them unbound rather then bind them with master /child fields.

[June7]

None of the records have an FSL code of 0. Some records have no code at all.

So the issue is solved?

[Huddle]

I'm still working on getting the averages. I'm probably doing this the hard way but what I've come up with is taking two crosstab queries and doing a union. The union looks good. When I try to do the formula in the report I'm getting errors.

If I try to change the control source in the Text4 box to =Sum([InspCt]) it gives the error. With Count([InspCt]) is counting the number of records per commander rather than the InspCt field.