Please help!! Sub-report problems...

[Amit]

MS Access 2K, Windows XP
========================
Hi,

I have a table that lists organizations and has checkboxes
for different languages (Spanish, Portuguese, Chinese
etc.) associated with each organization.

I'm trying to create a report that lists organizations for
each language, e.g.
==========================================
Chinese
-------
Org1
Org3

Portuguese
----------
Org1
Org2
Org4
Org6

Spanish
-------
Org1
Org2
Org3
Org4
Org5
Org6
==========================================
I've created a query based on the table, and am using this
query as the record source for my reports and sub-reports.

The way I approached this was to create sub-reports for
each language, based on the query, and apply the filter:
e.g. "[OrgLang_Chinese] = true" in the sub-report for orgs
that checked Chinese, and so on. So, I have 3 sub-reports:
srpt_Chinese, srpt_Portuguese and srpt_Spanish, and these
are working fine, in the sense that they display only
those orgs that checked the specific Language check-box.

I'm trying to create one single report that includes the 3
sub-reports, and this is where I run into problems. I'm
not sure how to define the "Link Child fields" and
the "Link Master fields" when I include the sub-report in
the main report. I've tried using "None" and "include for
each record.." and what it does is display ALL the
organization names for each organization name. This is
when the source for the main report is the query.

I've tried deleting the source for the main report, and
then the sub-report displays ALL the org names only once
(so, instead of 300+ pages, I end up with 3 pages). But,
it's still not displaying the organizations that checked
the specific Language check-box.
The sub-report is in the detail section of the main report.

I'm at my wit's ends here, and not sure how to fix this
problem. I'll appreciate any help here.

Thanks!

-Amit

 

[Duane Hookom]

I believe your issue is using different fields for each language. This is
not normalized. How do you add more languages? With your current structure,
you add fields, controls, columns in queries,... You really should have a
table of OrgLanguages with one record per organization per language. Since
you don't have this, you can create it with a union query.
SELECT Org, "Spanish" as Language
FROM tblOrgs
WHERE Spanish = True
UNION ALL
SELECT Org, "Portuguese"
FROM tblOrgs
WHERE Portuguese = True
....etc... for all languages

Then build your report based on this union query.