I need to list multiple related records within a single report she

V

vic1

I have a report which shows data, one sheet per school, for secondary
schools. On each school's sheet I need to list the feeder primary school(s).
At the moment, when there is more than one feeder primary school, the report
is creating one duplicate sheet per secondary school, each with a different
primary listed, rather than listing, say, 2 or 3 primary schools on the one
secondary school reoport sheet. How do I solve this? Any help would be much
appreciated.

The primary school data is drawn from a different, but related, table to the
secondary data, with a one-to-many join on the ID fields.
 
M

Marshall Barton

vic1 said:
I have a report which shows data, one sheet per school, for secondary
schools. On each school's sheet I need to list the feeder primary school(s).
At the moment, when there is more than one feeder primary school, the report
is creating one duplicate sheet per secondary school, each with a different
primary listed, rather than listing, say, 2 or 3 primary schools on the one
secondary school reoport sheet. How do I solve this? Any help would be much
appreciated.

The primary school data is drawn from a different, but related, table to the
secondary data, with a one-to-many join on the ID fields.
Click to expand...


Not sure what you are doing in your report, but it sounds
like you need to use Sorting and Grouping (View menu) on the
secondary school field. Put the secondary school related
text boxes in the group header and leave the primary school
data in the detail section.
 
V

vic1

Many thanks for your reply. However, I tried grouping on the Secondary School
ID in the Page Header section - the only interesting thing it achieved was
that all records with a single feeder school then produced 2 pages - one with
the header only (the rest of the page blank) and the other complete with
detail, while the schools with more than one feeder primary remained 'one
sheet per primary' (with no blank sheets) as before!

In broad terms the format of my report is:

Report Header: (Single front page with title)
Page Header: (Sorted by school ID with name and reference numbers)
Detail: (all contact details and related information, including 'list' of
primary feeder schools - only I'm not getting a list, just a separate sheet
for each feeder school with the rest of the details duplicated)
Page Footer: (usual page numers, file path etc.)

The only 'sorting and grouping' applied is on the Secondary school ID.

It is a report I have been working with for years, but I have only just
needed to add feeder primary schools to it. I don't know whether any of this
helps, but I would appreciate any other suggestions you have.
 
M

Marshall Barton

vic1 said:
Many thanks for your reply. However, I tried grouping on the Secondary School
ID in the Page Header section - the only interesting thing it achieved was
that all records with a single feeder school then produced 2 pages - one with
the header only (the rest of the page blank) and the other complete with
detail, while the schools with more than one feeder primary remained 'one
sheet per primary' (with no blank sheets) as before!

In broad terms the format of my report is:

Report Header: (Single front page with title)
Page Header: (Sorted by school ID with name and reference numbers)
Detail: (all contact details and related information, including 'list' of
primary feeder schools - only I'm not getting a list, just a separate sheet
for each feeder school with the rest of the details duplicated)
Page Footer: (usual page numers, file path etc.)

The only 'sorting and grouping' applied is on the Secondary school ID.

It is a report I have been working with for years, but I have only just
needed to add feeder primary schools to it. I don't know whether any of this
helps, but I would appreciate any other suggestions you have.
Click to expand...
 
M

Marshall Barton

vic1 said:
Many thanks for your reply. However, I tried grouping on the Secondary School
ID in the Page Header section - the only interesting thing it achieved was
that all records with a single feeder school then produced 2 pages - one with
the header only (the rest of the page blank) and the other complete with
detail, while the schools with more than one feeder primary remained 'one
sheet per primary' (with no blank sheets) as before!

In broad terms the format of my report is:

Report Header: (Single front page with title)
Page Header: (Sorted by school ID with name and reference numbers)
Detail: (all contact details and related information, including 'list' of
primary feeder schools - only I'm not getting a list, just a separate sheet
for each feeder school with the rest of the details duplicated)
Page Footer: (usual page numers, file path etc.)

The only 'sorting and grouping' applied is on the Secondary school ID.

It is a report I have been working with for years, but I have only just
needed to add feeder primary schools to it. I don't know whether any of this
helps, but I would appreciate any other suggestions you have.
Click to expand...


Either you have something really strange in your report or
you are saying Page Header when you mean Feeder School Group
Header. I will assume the latter.

I can't be sure, but the feeder school data being on
separate pages simply sounds like you have ForceNewPage set
on the detail section.

I don't see anything in your original post about "contact
details" so I don't know what to make of that. Maybe you
have multiple contacts for each feeder school and want the
feeder school data separate from the contact data and you
need another group level for feeder schools. OTOH, if the
contact records are related to the secondary school, then
you have two one to many relationships and a feeder school
subreport in the secondary school group footer is needed to
get the desired output.
 
V

vic1

'Page Header' is simply what it actually says on the blue bar at the top of
the section in Design View. I certainly don't want to group by feeder schools
- only by Secondary school. All the contact and other details on the report
are related to the Secondary school only - hence the need to list the
'related Primary Schools'. In my base table, the related Primary Schools are
accessible by clicking the '+' sign at the left hand side of each record in
the table, if this helps you visualise it. It may be that I need a subreport
- but I did try and nothing displayed in it. Can you help me how to go about
it please?

On the other hand, I do have similar reports, such as a remittance advice,
which displays more than one payment per school - but I don't seem to be able
to replicate the way it works!
 
M

Marshall Barton

You can not group using a page header.

Please try using a GROUP header for the secondary school
data with the feeder school data in the detail section.

I stll need to understand how the contact data fits into
your table structure. Is there one contact field in the
secondary school table or is there a one to many
relationship to a contacts table?
 
V

vic1

I'm really sorry to be so much trouble - but the Secondary school data is
already grouped and the primary feeder school(s) are already in the detail
section.

In my base table, all the 'contact' details (ie. address, phone number etc.)
and all other details (such as teacher names, pupil numbers) are in separate
fields within the one table. (Probably not ideal - but that's how it started
off and it's worked well ever since). The only items in a separate table are
the related Primary schools - and they are linked via the school ID numbers.

I've tried again to produce a subreport with the wizard - but every time I
go to 'Print Preview' I just get a blank space where the subreport should be.
Am I misunderstanding something?

By the way, ForceNewPage is set to 'none'.

Marshall Barton said:
You can not group using a page header.

Please try using a GROUP header for the secondary school
data with the feeder school data in the detail section.

I stll need to understand how the contact data fits into
your table structure. Is there one contact field in the
secondary school table or is there a one to many
relationship to a contacts table?
--
Marsh
MVP [MS Access]

'Page Header' is simply what it actually says on the blue bar at the top of
the section in Design View. I certainly don't want to group by feeder schools
- only by Secondary school. All the contact and other details on the report
are related to the Secondary school only - hence the need to list the
'related Primary Schools'. In my base table, the related Primary Schools are
accessible by clicking the '+' sign at the left hand side of each record in
the table, if this helps you visualise it. It may be that I need a subreport
- but I did try and nothing displayed in it. Can you help me how to go about
it please?

On the other hand, I do have similar reports, such as a remittance advice,
which displays more than one payment per school - but I don't seem to be able
to replicate the way it works!
Click to expand...
Click to expand...
 
M

Marshall Barton

vic1 said:
I'm really sorry to be so much trouble - but the Secondary school data is
already grouped and the primary feeder school(s) are already in the detail
section.

In my base table, all the 'contact' details (ie. address, phone number etc.)
and all other details (such as teacher names, pupil numbers) are in separate
fields within the one table. (Probably not ideal - but that's how it started
off and it's worked well ever since). The only items in a separate table are
the related Primary schools - and they are linked via the school ID numbers.

I've tried again to produce a subreport with the wizard - but every time I
go to 'Print Preview' I just get a blank space where the subreport should be.
Am I misunderstanding something?

By the way, ForceNewPage is set to 'none'.
Click to expand...


Let's leave the subreport idea aside for now.

The way I see your description of the report at this point
is that all the secondary school information, including the
contact information, is in the group header section. The
detail section contains nothing but the feeder school data.
The only thing that you said was wrong with this arrangement
is that the detail section displays each feeder record on a
separate page, but there are no ForceNewPage settings
causing it.

The only way I can see that happening is if you nave a page
break control in the detail section or if the detail section
is so tall that only one instance can fit on a page (with
KeepTogether set to Yes?).

If I still don;t have the right picture, please explain
where I have gone off the rails.
 
V

vic1

Sorry no - you haven't got the picture yet - though I am beginning to think
that I will have to start again from scratch with a report format similar to
the one you describe!

In my report, only the School ID and name (plus a few other "irrelevant to
this issue" details) are in the Group Header. All other contact and 'school
specific' details are in the detail section - along with the list of feeder
primary schools). It may be that the problem is that the Detail Section is
drawing its information from two separate (though linked) sources.

The report design is fairly complex and I am reluctant to start again unless
I have absolutely got to because it will take me several hours to re-format
it successfully so that all info still fits on a single sheet. (Having said
that, I have already spent several hours trying to resolve this problem!)

If this is the problem, then, as far as I can make out, a subreport should
work. However, the subreport brings up no details at all in the print
preview, as long as the LinkedMasterField and LinkedChildField are filled in.
(I am trying to use the Secondary school ID as the Linked MasterField, to
group the results). As soon as I clear these fields, the subreport prints a
list of ALL the primary schools on EVERY sheet.

It has occurred to me that maybe you would get the picture better if I
referred to "High Schools" and "Elementary Schools" instead of Secondary and
Primary?!!
 
V

vic1

EUREKA!!

I've found a solution. I now have a subreport with fields ALL DRAWN FROM THE
SAME TABLE - and it works and shows in print preview with the correct schools
listed together.

VERY MANY THANKS FOR ALL YOUR EFFORTS AND I'M SORRY MY PROBLEM WAS SO
DIFFICULT TO UNDERSTAND.
 
V

vic1

Sorry - I spoke too soon!

It all looks perfect apart from one thing - It still prints a completely
duplicate sheet whenever there is more than one Elementary School listed on
the High School sheet.

Is there s setting for disabling duplicates?

Many thanks!
 
M

Marshall Barton

Totally duplicate pages come about beacuse the record source
query is retrieving duplicate records.

When you started using a subreport, I suspect that you
forgot to modify the main report's record source query to
remove the feeder school table and fields.
 
M

Marshall Barton

vic1 said:
Sorry no - you haven't got the picture yet - though I am beginning to think
that I will have to start again from scratch with a report format similar to
the one you describe!

In my report, only the School ID and name (plus a few other "irrelevant to
this issue" details) are in the Group Header. All other contact and 'school
specific' details are in the detail section - along with the list of feeder
primary schools). It may be that the problem is that the Detail Section is
drawing its information from two separate (though linked) sources.
Click to expand...


When you use grouping instead of a subreport, then all
secondary school fields should be in the group header. If
any secondary school fields are in the detail section, they
will be duplicated with each feeder school record.
 
V

vic1

BRILLIANT - THANK YOU!

Marshall Barton said:
Totally duplicate pages come about beacuse the record source
query is retrieving duplicate records.

When you started using a subreport, I suspect that you
forgot to modify the main report's record source query to
remove the feeder school table and fields.
--
Marsh
MVP [MS Access]

Sorry - I spoke too soon!

It all looks perfect apart from one thing - It still prints a completely
duplicate sheet whenever there is more than one Elementary School listed on
the High School sheet.

Is there s setting for disabling duplicates?

Many thanks!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dealing with the error: unable to filter on excel hc home school 0
Mail Merge - List multiple items then page break on unique data 1
linking sheets and workbooks 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
How do I get 1 combo box to correctly bring up values in another b 1
info from multiple records in same field in report 0
Creating a report from a query using drop down box parameters 1
group/sort on 2 fields w/ same value 2

Top