By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,333 Members | 1,843 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,333 IT Pros & Developers. It's quick & easy.

Test for common values

P: n/a
Hi all,
I've looked through the archives and I haven't been able to find or at
least recognize the solution to my problem. I have a student data
base, Access 2000.

I have 12 school districts. Each school district has from 1-5 schools.
Each school provides some special education services, but not
necessarily all of the services that are provided within the county. I
need to create a report that does this:

SchoolDistA has 3 schools
School 1 provides services 20, 26, 50 (codes)
School 2 provides services 20, 57, 58
School 3 provides services 20, 42, 57

If a service is provided at each of the schools within a single
district, I need to be able to list that in the School District group
header, but not in the detail. If a service is provided at only some
of the schools, I need for it to remain in the detail for that school.
I would greatly appreciate some suggestions on how to approach this.
Thanks,
Nancy

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
I haven't had time to work on this but I did want to thank you for
taking the time to think about it and offer a solution. I learn quite
a lot from this group.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.