Subreport problem with COUNT and GROUP BY

[Andrew Meador]

Hello all!
Trying to create a report with a subreport. The subreport needs to run
a query that returns a list of Home Grades with a Count per Grade.
This in turn needs to be done for each Neighborhood in the main
report. The report would look something like this:

Neighborhood: 10
A 5
B 10
C 12
D 6
F 1

Neighborhood: 20
A 10
B 18
C 30
D 15
F 4

Neighborhood: 30
A 30
B 22
C 15
D 8
F 1

This data is split over two tables. The tables can be linked by a
common Field called PARID. The PS table has the Neighborhood field,
and the DWEL table has the GRADE field.

I am using this query to get the Neighborhood data:
SELECT DISTINCT NBHD AS Neighborhood
FROM PS
ORDER BY NBHD;

I am using this query to get the subreport data:
SELECT GRADE, count(GRADE) AS [Number]
FROM DWEL
GROUP BY grade
ORDER BY grade;

I can't figure out how to do this (to get the subreport into the main
report). I thought to innor join the two tables in the subreport query
so I could include the Neighborhood field from PS, and then set a
where clause to only include those records that match the current
iteration of the main query, but due to the count and Group By, I get
errors griping about not having the Neighborhood field in the
aggragate function. Plus, I don't know how to compare it to the
current iteration of the main query.

The whole idea here is that we have many neighborhoods in our county
and we need to get the number of houses in each of these
neighborhoods, tallied by their grade so we can compare neighborhoods
(is one neighborhood similar to another based on number and/or
percentage of similar grades houses).

Anyway, I hope this is clear enough to get help.
Thanks in advance!

 

[John Spencer]

I would think you could use one query
SELECT NBHD, GRADE, count(GRADE) AS [Number]
FROM PS INNER JOIN DWEL
ON PS.ParID = DWEL.ParID
GROUP BY NBHD, GRADE

Then use the reports sorting and grouping (View: Sorting and Grouping) to
create a group based on NBHD, and sorting by Grade

Then put NBHD in that group section of the report
And the grade and count in the detail section of the report.

If you do that there should be no need for a sub-report.

By the way if a house has two children in grade 5 (twins) do you count 1 for
the house or 2 for the house? How about if the house has children in
several grades?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Andrew Meador]

Thanks John, this was exactly what I was looking for... I think I was
over thinking this one...

By the way if a house has two children in grade 5 (twins) do you count 1 for
the house or 2 for the house? How about if the house has children in
several grades?

Hopefully you're not making a joke here that is going over my head,
but in case you're not, let me explain. This is not related to school
grades, it is related to the grade of the house itself. It has to do
with property taxing rates and such. We're looking at merging segments
of the county (neighborhoods of houses) together into the same taxing
neighborhoods (which in part determines the property tax rate) and
want to make sure the homes are similar enough in grade so as not to
cause peoples property taxes to fluctuate greatly in one direction or
the other. If one neighborhood is primarily composed of A+ grade
houses and we merge that neighborhood with another neighborhood that
is mostly C grade houses, all the people who have C grade houses would
end up paying higher property taxes and the houses in the A+ grade
houses would get lower property taxes.

Thanks again!

 

[Andrew Meador]

Thanks John, this was exactly what I was looking for... I think I was
over thinking this one...

By the way if a house has two children in grade 5 (twins) do you count 1 for
the house or 2 for the house? How about if the house has children in
several grades?

Hopefully you're not making a joke here that is going over my head,
but in case you're not, let me explain. This is not related to school
grades, it is related to the grade of the house itself. It has to do
with property taxing rates and such. We're looking at merging segments
of the county (neighborhoods of houses) together into the same taxing
neighborhoods (which in part determines the property tax rate) and
want to make sure the homes are similar enough in grade so as not to
cause peoples property taxes to fluctuate greatly in one direction or
the other. If one neighborhood is primarily composed of A+ grade
houses and we merge that neighborhood with another neighborhood that
is mostly C grade houses, all the people who have C grade houses would
end up paying higher property taxes and the houses in the A+ grade
houses would get lower property taxes.

Thanks Again!

(Sorry if this is a re-post, but I couldn't see my first reply to John
Spencer)