Sub report data not right?

[Sheila D]

I have a report which displays information on total number of procedures
carried out by individual consultants and calculates the % of patients who
have to be re-stented. The report displays information calculated overall and
grouped / calculated by year. This part is working fine.

I need to also output the detail information and have designed a subreport
to bring this in which uses the same information and pulls in records where
re-stent is >0 - the underlying query shows the correct info - number of
procedures re-stented by each consultant. I have put the sub report in the
Year Date group header but am not getting the correct information for each
year; I get just 1 consultant totals for the whole period (3 years) showing
against each year of my report. I hope this makes sense, any help would be
much appreciated.

 

[Ken Sheridan]

It sounds like the subreport is linked to the parent report on the
consultant, not on the year. Access may have done this automatically for you
when you added the subreport control to the report.

If both the parent and subreport's RecordSource queries return a column with
the years, using an expression such as Year([ProcedureDate]) for instance
then you can link on those columns by making them the LinkMasterFields and
LinkChildFields properties of the subreport control.

If not you can introduce such a column into the queries, probably the
easiest solution, or you can add a hidden computed control to the report with
an expression which returns the year as its ControlSource property. You can
then use the name of this control as the LinkMasterFields property. You'll
still need a column returning the year in the subreport's underlying
recordset, however, to use as the LinkChildFields property.

You could probably achieve the same end result with a single report based on
a query which returns the detail and includes a computed column returning the
year. In this case the aggregation would be done in the year group header
and the detail section would contain the details. You'd exclude the
irrelevant detail rows by cancelling each in the detail section's Format
event procedure like so:

Cancel = ([re-stent] = 0)

Ken Sheridan
Stafford, England

 

[Sheila D]

Ken you are quite right - I did link on the consultant and now I have added
the Year and linked on that it works perfectly. I did not know I could
exclude data in the Detail section like that so thanks very much - that will
be a much better route next time.

Sheila

It sounds like the subreport is linked to the parent report on the
consultant, not on the year. Access may have done this automatically for you
when you added the subreport control to the report.

If both the parent and subreport's RecordSource queries return a column with
the years, using an expression such as Year([ProcedureDate]) for instance
then you can link on those columns by making them the LinkMasterFields and
LinkChildFields properties of the subreport control.

If not you can introduce such a column into the queries, probably the
easiest solution, or you can add a hidden computed control to the report with
an expression which returns the year as its ControlSource property. You can
then use the name of this control as the LinkMasterFields property. You'll
still need a column returning the year in the subreport's underlying
recordset, however, to use as the LinkChildFields property.

You could probably achieve the same end result with a single report based on
a query which returns the detail and includes a computed column returning the
year. In this case the aggregation would be done in the year group header
and the detail section would contain the details. You'd exclude the
irrelevant detail rows by cancelling each in the detail section's Format
event procedure like so:

Cancel = ([re-stent] = 0)

Ken Sheridan
Stafford, England

I have a report which displays information on total number of procedures
carried out by individual consultants and calculates the % of patients who
have to be re-stented. The report displays information calculated overall and
grouped / calculated by year. This part is working fine.

I need to also output the detail information and have designed a subreport
to bring this in which uses the same information and pulls in records where
re-stent is >0 - the underlying query shows the correct info - number of
procedures re-stented by each consultant. I have put the sub report in the
Year Date group header but am not getting the correct information for each
year; I get just 1 consultant totals for the whole period (3 years) showing
against each year of my report. I hope this makes sense, any help would be
much appreciated.