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

access 97, too many nested subforms, is there a workaround

P: n/a
I've got the following tables

tblMaster
masterID
etc

tblDetail
masterId
detailId
etc

tblOptions
masterId
detailId
optionId
etc

so I should be able to create a report (master) with a subreport
(detail)
and the subreport (detail) would have a subreport (options)

but tblDetail has about 110 fields, so I end up with too many controls
on
my detail subreport
next attempt, break up the detail subreport into 2 pieces and them onto
a
report (detailAll)

but now I have report (master) with subreport (detailAll)
and the subreport (detailAll) has 2 subreports (detail1 & detail2)
and subreport (detail2) has a subreport (options)

but that creates 3 levels of nesting which isn't supported in access97

I can skip the detailAll subreport and put the detail1 and detail2
subreports
on report (master), but then I'd see all the detailId's of detail1 and
then all
the detailId's of detail2, which is incorrect

other than upgrading to access2XX which ain't going to happen soon,
is there another solution ?

Mar 2 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

<le*********@natpro.com> wrote in message
news:11*********************@p10g2000cwp.googlegro ups.com...
I've got the following tables

tblMaster
masterID
etc

tblDetail
masterId
detailId
etc

tblOptions
masterId
detailId
optionId
etc

so I should be able to create a report (master) with a subreport
(detail)
and the subreport (detail) would have a subreport (options)

but tblDetail has about 110 fields, so I end up with too many controls
on
my detail subreport
next attempt, break up the detail subreport into 2 pieces and them onto
a
report (detailAll)

but now I have report (master) with subreport (detailAll)
and the subreport (detailAll) has 2 subreports (detail1 & detail2)
and subreport (detail2) has a subreport (options)

but that creates 3 levels of nesting which isn't supported in access97

I can skip the detailAll subreport and put the detail1 and detail2
subreports
on report (master), but then I'd see all the detailId's of detail1 and
then all
the detailId's of detail2, which is incorrect

other than upgrading to access2XX which ain't going to happen soon,
is there another solution ?


Why don't you create a query which joins all the required table and
displays and the fields and records you need. Then create your report(s)
based on the query using the wizards making sure that you select the
appropriate grouping levels. No need fro sub reports then.

Jeff
Mar 2 '06 #2

P: n/a
that's an idea, so, I create one query that has all fields from
tblMaster, tblDetail, tblOptions
and I use the query in one report with 3 sections

section 1
master stuff

section 2
detail stuff

section 3
option stuff
assuming I have 2 or moredetail records for a given master, wouldn't it
duplicate the section 1 data ?
if so, how do I hide it ? using 'hide duplicates' for each control in
the section ?

Mar 2 '06 #3

P: n/a
If you use the proper grouping levels in your report, you should end up
with something like this:

Master stuff (include only fields in this section that pertain to the
master data)
Detail stuff (include only fields in this section that pertain to
the detail data)
Option stuff (you get the idea)

So, if you have records like this from the query:
MasterID | MasterDescription | Detail1 | DetailDescription | Option1
MasterID | MasterDescription | Detail2 | DetailDescription | Option 2

Your report would look like this, and would not duplicate data from the
master record:
MasterID MasterDescription (Section 1)
Detail1 DetailDescription (Section 2)
Detail2 DetailDescription
Option1 (Section 3)
Option2

This would repeat each time it hits a new MasterID.

HTH,
Jana

Mar 2 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.