Referrence to SubReport

[M.A.Halim]

Hi! I have a sales report that displays the monthly sales for 45 branches.
all the sales figures are stored in the sales table in the sales field and so
the months in the months field in the same table.
now Im comparing the sales of 2007 to 2006 "Which all located in the same
field" All waht I did is I created a Qry that displays the required
year"2007" for the main report and another Qry to display the sales of "2006"
for a sub Qry then i added it to my Main report and I linked child and master
fields and the display of figures went well, now when Im trying to referrence
my formula in the main report to the subreport I get error "#name?"
the formula is "=[Sales]-Reports![MySubReport]![Sales] "
Any help why the error hapens? I tried to play around like tranferring the
formula tro the sub report and even I used an Alias Name for the Sales Field
to avoid this error but no way.
Im using Access 2003.
Appreciate your continuous help

 

[Allen Browne]

Reports are different to forms. You need to collect the total in the Report
Footer section of the subreport, and then refer to that text box in your
main report, in the same section.

Details in:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

The subreport probably is the best way to handle it, but you may also like
to be aware of subqueries. Details:
http://allenbrowne.com/subquery-01.html#YTD

 

[M.A.Halim]

Thanks Allen
But I don't mean the total here.In the Main Report's details section I have
each Branch's sales in 2007 and in the subreport I have same Branch's sales
in 2006 and for the comparison i need 2007 sales minus 2006 sales all within
the details section but regarding the total It is O.K for what you adviced me.
--
M.A.Halim
(e-mail address removed)

Reports are different to forms. You need to collect the total in the Report
Footer section of the subreport, and then refer to that text box in your
main report, in the same section.

Details in:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

The subreport probably is the best way to handle it, but you may also like
to be aware of subqueries. Details:
http://allenbrowne.com/subquery-01.html#YTD

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I have a sales report that displays the monthly sales for 45 branches.
all the sales figures are stored in the sales table in the sales field and
so
the months in the months field in the same table.
now Im comparing the sales of 2007 to 2006 "Which all located in the same
field" All waht I did is I created a Qry that displays the required
year"2007" for the main report and another Qry to display the sales of
"2006"
for a sub Qry then i added it to my Main report and I linked child and
master
fields and the display of figures went well, now when Im trying to
referrence
my formula in the main report to the subreport I get error "#name?"
the formula is "=[Sales]-Reports![MySubReport]![Sales] "
Any help why the error hapens? I tried to play around like tranferring the
formula tro the sub report and even I used an Alias Name for the Sales
Field
to avoid this error but no way.
Im using Access 2003.
Appreciate your continuous help

 

[Allen Browne]

You cannot do that.

The subreport exists in one section of the main report - perhaps Detail, or
a Group Footer.

The subreport has (potentially) many records. There is no such thing as THE
record in the subreport, because there could be many. Therefore you can only
relate an aggregate from the subreport to the value in the main report.

It may be necessary to redesign this report if you want one total for each
value in the main report. For example, it might be possible to use a DSum()
expression in the Control Source of a text box to get the value for the
corresponding period of the previous year.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen
But I don't mean the total here.In the Main Report's details section I
have
each Branch's sales in 2007 and in the subreport I have same Branch's
sales
in 2006 and for the comparison i need 2007 sales minus 2006 sales all
within
the details section but regarding the total It is O.K for what you adviced
me.
--
M.A.Halim
(e-mail address removed)

Reports are different to forms. You need to collect the total in the
Report
Footer section of the subreport, and then refer to that text box in your
main report, in the same section.

Details in:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

The subreport probably is the best way to handle it, but you may also
like
to be aware of subqueries. Details:
http://allenbrowne.com/subquery-01.html#YTD

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I have a sales report that displays the monthly sales for 45
branches.
all the sales figures are stored in the sales table in the sales field
and
so
the months in the months field in the same table.
now Im comparing the sales of 2007 to 2006 "Which all located in the
same
field" All waht I did is I created a Qry that displays the required
year"2007" for the main report and another Qry to display the sales of
"2006"
for a sub Qry then i added it to my Main report and I linked child and
master
fields and the display of figures went well, now when Im trying to
referrence
my formula in the main report to the subreport I get error "#name?"
the formula is "=[Sales]-Reports![MySubReport]![Sales] "
Any help why the error hapens? I tried to play around like tranferring
the
formula tro the sub report and even I used an Alias Name for the Sales
Field
to avoid this error but no way.
Im using Access 2003.
Appreciate your continuous help

 

[M.A.Halim]

when I added the sub report and linked the child and master fields the values
of the previous year displayed very well and accurate, but I cant minus it
from the current year in the same details section may be I have to open
another hidden report so i can do the calculations.?

--
M.A.Halim
(e-mail address removed)

You cannot do that.

The subreport exists in one section of the main report - perhaps Detail, or
a Group Footer.

The subreport has (potentially) many records. There is no such thing as THE
record in the subreport, because there could be many. Therefore you can only
relate an aggregate from the subreport to the value in the main report.

It may be necessary to redesign this report if you want one total for each
value in the main report. For example, it might be possible to use a DSum()
expression in the Control Source of a text box to get the value for the
corresponding period of the previous year.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen
But I don't mean the total here.In the Main Report's details section I
have
each Branch's sales in 2007 and in the subreport I have same Branch's
sales
in 2006 and for the comparison i need 2007 sales minus 2006 sales all
within
the details section but regarding the total It is O.K for what you adviced
me.
--
M.A.Halim
(e-mail address removed)

Reports are different to forms. You need to collect the total in the
Report
Footer section of the subreport, and then refer to that text box in your
main report, in the same section.

Details in:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

The subreport probably is the best way to handle it, but you may also
like
to be aware of subqueries. Details:
http://allenbrowne.com/subquery-01.html#YTD

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I have a sales report that displays the monthly sales for 45
branches.
all the sales figures are stored in the sales table in the sales field
and
so
the months in the months field in the same table.
now Im comparing the sales of 2007 to 2006 "Which all located in the
same
field" All waht I did is I created a Qry that displays the required
year"2007" for the main report and another Qry to display the sales of
"2006"
for a sub Qry then i added it to my Main report and I linked child and
master
fields and the display of figures went well, now when Im trying to
referrence
my formula in the main report to the subreport I get error "#name?"
the formula is "=[Sales]-Reports![MySubReport]![Sales] "
Any help why the error hapens? I tried to play around like tranferring
the
formula tro the sub report and even I used an Alias Name for the Sales
Field
to avoid this error but no way.
Im using Access 2003.
Appreciate your continuous help

 

[Allen Browne]

Perhaps you could use a crosstab query, with a Calculated field like this:
Field: TheYear: Year([SaleDate])
Total: Group By
Crosstab: Column Heading

Then the next column would be something like this:
field: Amount
Total: Sum
Crosstab: Value

And the row heading would be whatever you are trying to compare, e.g.:
Field: Dept
Total: Group By
Crosstab: Row Heading

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

when I added the sub report and linked the child and master fields the
values
of the previous year displayed very well and accurate, but I cant minus it
from the current year in the same details section may be I have to open
another hidden report so i can do the calculations.?

--
M.A.Halim
(e-mail address removed)

You cannot do that.

The subreport exists in one section of the main report - perhaps Detail,
or
a Group Footer.

The subreport has (potentially) many records. There is no such thing as
THE
record in the subreport, because there could be many. Therefore you can
only
relate an aggregate from the subreport to the value in the main report.

It may be necessary to redesign this report if you want one total for
each
value in the main report. For example, it might be possible to use a
DSum()
expression in the Control Source of a text box to get the value for the
corresponding period of the previous year.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen
But I don't mean the total here.In the Main Report's details section I
have
each Branch's sales in 2007 and in the subreport I have same Branch's
sales
in 2006 and for the comparison i need 2007 sales minus 2006 sales all
within
the details section but regarding the total It is O.K for what you
adviced
me.
--
M.A.Halim
(e-mail address removed)


:

Reports are different to forms. You need to collect the total in the
Report
Footer section of the subreport, and then refer to that text box in
your
main report, in the same section.

Details in:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

The subreport probably is the best way to handle it, but you may also
like
to be aware of subqueries. Details:
http://allenbrowne.com/subquery-01.html#YTD

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I have a sales report that displays the monthly sales for 45
branches.
all the sales figures are stored in the sales table in the sales
field
and
so
the months in the months field in the same table.
now Im comparing the sales of 2007 to 2006 "Which all located in the
same
field" All waht I did is I created a Qry that displays the required
year"2007" for the main report and another Qry to display the sales
of
"2006"
for a sub Qry then i added it to my Main report and I linked child
and
master
fields and the display of figures went well, now when Im trying to
referrence
my formula in the main report to the subreport I get error "#name?"
the formula is "=[Sales]-Reports![MySubReport]![Sales] "
Any help why the error hapens? I tried to play around like
tranferring
the
formula tro the sub report and even I used an Alias Name for the
Sales
Field
to avoid this error but no way.
Im using Access 2003.
Appreciate your continuous help

 

[M.A.Halim]

Yes this crosstab is the best Idea to go out of this vicious circle.
Thanks Allen.
Appreciate your responses and time.

--
M.A.Halim
(e-mail address removed)

Perhaps you could use a crosstab query, with a Calculated field like this:
Field: TheYear: Year([SaleDate])
Total: Group By
Crosstab: Column Heading

Then the next column would be something like this:
field: Amount
Total: Sum
Crosstab: Value

And the row heading would be whatever you are trying to compare, e.g.:
Field: Dept
Total: Group By
Crosstab: Row Heading

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

when I added the sub report and linked the child and master fields the
values
of the previous year displayed very well and accurate, but I cant minus it
from the current year in the same details section may be I have to open
another hidden report so i can do the calculations.?

--
M.A.Halim
(e-mail address removed)

You cannot do that.

The subreport exists in one section of the main report - perhaps Detail,
or
a Group Footer.

The subreport has (potentially) many records. There is no such thing as
THE
record in the subreport, because there could be many. Therefore you can
only
relate an aggregate from the subreport to the value in the main report.

It may be necessary to redesign this report if you want one total for
each
value in the main report. For example, it might be possible to use a
DSum()
expression in the Control Source of a text box to get the value for the
corresponding period of the previous year.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen
But I don't mean the total here.In the Main Report's details section I
have
each Branch's sales in 2007 and in the subreport I have same Branch's
sales
in 2006 and for the comparison i need 2007 sales minus 2006 sales all
within
the details section but regarding the total It is O.K for what you
adviced
me.
--
M.A.Halim
(e-mail address removed)


:

Reports are different to forms. You need to collect the total in the
Report
Footer section of the subreport, and then refer to that text box in
your
main report, in the same section.

Details in:
Bring the total from a subreport back onto the main report
at:
http://allenbrowne.com/casu-18.html

The subreport probably is the best way to handle it, but you may also
like
to be aware of subqueries. Details:
http://allenbrowne.com/subquery-01.html#YTD

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I have a sales report that displays the monthly sales for 45
branches.
all the sales figures are stored in the sales table in the sales
field
and
so
the months in the months field in the same table.
now Im comparing the sales of 2007 to 2006 "Which all located in the
same
field" All waht I did is I created a Qry that displays the required
year"2007" for the main report and another Qry to display the sales
of
"2006"
for a sub Qry then i added it to my Main report and I linked child
and
master
fields and the display of figures went well, now when Im trying to
referrence
my formula in the main report to the subreport I get error "#name?"
the formula is "=[Sales]-Reports![MySubReport]![Sales] "
Any help why the error hapens? I tried to play around like
tranferring
the
formula tro the sub report and even I used an Alias Name for the
Sales
Field
to avoid this error but no way.
Im using Access 2003.
Appreciate your continuous help