Subreport problem with COUNT and GROUP BY
am 30.11.2007 17:27:58 von ameador1Hello 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!