Subreport column not showing correctly on Main report

[Robbie Doo]

I have created a 2 column report where the 1st column shows the item and the
2nd column is the percentage of the 1st column devided by its total;
[item]/Sum([item]).

Everything shows ok when I run the report. However, when I use this report
on a Main report as a subreport the [item] part shows ok but the percentage
shows 100% on each and everyone. Is there a way to correct this?

 

[Marshall Barton]

I have created a 2 column report where the 1st column shows the item and the
2nd column is the percentage of the 1st column devided by its total;
[item]/Sum([item]).

Everything shows ok when I run the report. However, when I use this report
on a Main report as a subreport the [item] part shows ok but the percentage
shows 100% on each and everyone. Is there a way to correct this?

It sounds like the main report's record source query
includes the same items as the subreport and the
LinkMaster/Child properties are set to item field.

If you need further assistance, please post both the main
and sub report's record source queries along with the
LinkMaster/Child properties.

 

[Robbie Doo]

Hi Marshall,

Here are the SQL's from both queries:

MAIN

SELECT Main_NA.ReviewNo, Main_NA.SampleMonth, Main_NA.Districts,
Divisions.Division, Main_NA.Disposition, Main_NA.Deficiency,
Report_Status.Status_Id, Report_Status.Status
FROM Report_Status, Divisions INNER JOIN (Districts INNER JOIN Main_NA ON
Districts.DistNo = Main_NA.Districts) ON Divisions.DIV = Districts.Division
WHERE (((Main_NA.SampleMonth)=[Forms].[Cri_CAC_with_Status].[txtRevMo]) AND
((Main_NA.Disposition)=1) AND
((Report_Status.Status_Id)=[Forms].[Cri_CAC_with_Status].[txtStatus]));

SUPREPORT:

SELECT Main_NA.ReviewNo, Main_NA.SampleMonth, Main_NA.Disposition,
Main_NA.Sprt_Frms, Main_NA.Districts, Divisions.Division
FROM (Divisions INNER JOIN Districts ON Divisions.DIV = Districts.Division)
INNER JOIN Main_NA ON Districts.DistNo = Main_NA.Districts
WHERE (((Main_NA.SampleMonth)=[Forms].[Cri_CAC_with_Status].[txtRevMo]));

You're right, the master/child link is having an affect on the report. I
tried all the possible linkages but the report does not come out the way I'm
looking for.

I have created a 2 column report where the 1st column shows the item and the
2nd column is the percentage of the 1st column devided by its total;
[item]/Sum([item]).

Everything shows ok when I run the report. However, when I use this report
on a Main report as a subreport the [item] part shows ok but the percentage
shows 100% on each and everyone. Is there a way to correct this?

It sounds like the main report's record source query
includes the same items as the subreport and the
LinkMaster/Child properties are set to item field.

If you need further assistance, please post both the main
and sub report's record source queries along with the
LinkMaster/Child properties.

 

[Marshall Barton]

Here are the SQL's from both queries:

MAIN

SELECT Main_NA.ReviewNo, Main_NA.SampleMonth, Main_NA.Districts,
Divisions.Division, Main_NA.Disposition, Main_NA.Deficiency,
Report_Status.Status_Id, Report_Status.Status
FROM Report_Status, Divisions INNER JOIN (Districts INNER JOIN Main_NA ON
Districts.DistNo = Main_NA.Districts) ON Divisions.DIV = Districts.Division
WHERE (((Main_NA.SampleMonth)=[Forms].[Cri_CAC_with_Status].[txtRevMo]) AND
((Main_NA.Disposition)=1) AND
((Report_Status.Status_Id)=[Forms].[Cri_CAC_with_Status].[txtStatus]));

SUPREPORT:

SELECT Main_NA.ReviewNo, Main_NA.SampleMonth, Main_NA.Disposition,
Main_NA.Sprt_Frms, Main_NA.Districts, Divisions.Division
FROM (Divisions INNER JOIN Districts ON Divisions.DIV = Districts.Division)
INNER JOIN Main_NA ON Districts.DistNo = Main_NA.Districts
WHERE (((Main_NA.SampleMonth)=[Forms].[Cri_CAC_with_Status].[txtRevMo]));

You're right, the master/child link is having an affect on the report. I
tried all the possible linkages but the report does not come out the way I'm
looking for.

I've stared at those for a long time and am having trouble
unraveling all those joins to figure out what you referred
to as [item] in your original question.

Whatever it is, the main report query should not include
that table in its joins and the LinkMaster/Child settings
should only be the field(s?) that are used to relate the
subreport records to each main report record. I think(?)
this might be the division and district fields or maybe the
statusid??