Wyllielass wrote:
I would greatly appreciate some suggestions on how to approach this.
Thanks,
Nancy
I used the following test table:
tblSchoolServices:
SServID AutoNumber Primary Key
DistrictID Long Foreign Key
SchoolID Long Foreign Key
ServiceID Long Foreign Key
1 1 1 20
2 1 1 26
3 1 1 50
4 1 2 20
5 1 2 57
6 1 2 58
7 1 3 20
8 1 3 42
9 1 3 57
10 2 4 20
11 2 4 50
Show the DistrictID (invisibly?) along with the DistrictName in the
ReportHeader
School District Group Header
txtDistrictID
txtDistrictName
lstServicesAvailableInAllSchoolsInDistrict.Visible =
SomeBooleanFunctionOf(DistrictID)?
tblSchoolServices_Crosstab:
TRANSFORM Count(tblSchoolServices.SchoolID) AS CountOfSchoolID SELECT
tblSchoolServices.ServiceID, Count(tblSchoolServices.SchoolID) AS
SchoolsWithService FROM tblSchoolServices WHERE DistrictID = " &
txtDistrictID.Value & " GROUP BY tblSchoolServices.DistrictID,
tblSchoolServices.ServiceID PIVOT tblSchoolServices.SchoolID;
Gives for DistrictID = 1 (inserted manually):
ServiceID SchoolsWithService 1 2 3
20 3 1 1 1
26 1 1
42 1 1
50 1 1
57 2 1 1
58 1 1
Any of the SchoolsWithService numbers that match the number of schools
in the district will need to be displayed in the header. The others,
if any, must go in the Detail section.
qryEnumerateSchoolsByDistrict:
SELECT DISTINCT DistrictID, SchoolID FROM tblSchoolServices GROUP BY
DistrictID, SchoolID;
Gives:
DistrictID SchoolID
1 1
1 2
1 3
2 4
strSQL for qrySchoolsInDistrict:
"SELECT DistrictID, Count(SchoolID) AS SchoolsInDistrict FROM
qryEnumerateSchoolsByDistrict WHERE DistrictID = " &
txtDistrictID.Value & " GROUP BY DistrictID;"
Gives for DistrictID = 1 (inserted manually):
DistrictID SchoolsInDistrict
1 3
strSQL for qryServiceIDsToShowInGroupHeader:
"SELECT DISTINCT tblSchoolServices_Crosstab.ServiceID FROM
tblSchoolServices_Crosstab INNER JOIN qrySchoolsInDistrict ON
tblSchoolServices_Crosstab.SchoolsWithService =
qrySchoolsInDistrict.SchoolsInDistrict WHERE
qrySchoolsInDistrict.DistrictID=" & txtDistrictID.Value & ";"
Gives for DistrictID = 1 (inserted manually):
ServiceID
20
strSQL for qryServiceIDsToShowInDetailSection:
"SELECT DISTINCT tblSchoolServices_Crosstab.ServiceID FROM
tblSchoolServices_Crosstab INNER JOIN qrySchoolsInDistrict ON
tblSchoolServices_Crosstab.SchoolsWithService <>
qrySchoolsInDistrict.SchoolsInDistrict WHERE
qrySchoolsInDistrict.DistrictID=" & txtDistrictID.Value & ";"
Gives for DistrictID = 1 (inserted manually):
ServiceID
26
42
50
57
58
Perhaps the RecordCount of these queries can be used to set the Visible
Property of a Listbox in the Group Header and of a Listbox in the
Detail Section. I dunno. Hopefully these queries will give you a
start in tackling this difficult problem. I'm not sure how this
technique will pan out when used in report code. Tables, Queries and
Listboxes can use shorter, less descriptive names. Disclaimer: I'm not
that great with crosstab queries yet so be sure to test the results
thoroughly. Hopefully someone will have a better approach. No
warranties are implied, use at your own risk, not responsible for lost
time, productivity, effectiveness, etc.
James A. Fortune
