multiple source properties

[Miskacee]

How can I use the same sub-report on various reports but have a different
source property? I understand that I should not have anything on record
source but I don't know how to reference the particular query I want to use
for that particular report.

Thank you!

 

[Duane Hookom]

Can you explain why you need different record sources for the same subreport?
Wouldn't you just need to some how influence the filter of the records?

 

[Miskacee]

what do you mean by influence the filters?
I have a subform which shows the number of employees by region and then on
the report there is a pie chart showing the average number of employees by
department. On all charts/graphs to the left, I show the actual number of
employees. I have various graphs but always the same subform only the
subform chnages, based upon the criteria selected. I am not certain how to
have only one subform to be used on 10 different charts/graphs. I don't know
how to 'influence' or select a particular query when opening up that
particular chart/graph.

If you need more info, please let me know. Right now I have 10 of the same
subforms each with a different record source and I know I should only have
one subform and each subform point at a different record source but don't
understanding where/how to place this code.

THANKS!

 

[Duane Hookom]

I assume you mean subreport and not subform. The standard method of
influencing the records displayed in a subform or subreport is to use the
Link Master/Child properties of the subreport control. This allows you to
filter the sub based on a field or fields in the main.

Can't you use this method? How are your record sources different?

 

[Miskacee]

I apologize for saying subform. I knew what I meant but you can't read my
mind I actually meant subreport. I understand totally about child/master
links. I still don't think I am explaining this correctly. Just replace
subform with subreport. Let me try this way: I have on the left a subreport
showing the reason why someone left employement and the number of people
selecting this reason. On the right I have a graph on the report showing the
same data as an average. Depending upon what the user selects, there will
always be a pie chart on the right of a number or average representation and
on the left, the same subreport, showing the number of employees selecting
that reason. The drop down list boxes for selecting the type of graph
depends upon location, country, etc. This is the reason for the same
subreport on the left with different data representation. I use the same
subreport whenever the user selects criteria by dates. Hope this helps. I
have no problems linking up the correct data if I use a different subreport
for each report. However, I only want to reference sone subreport instead of
10 like I have now. There is no point in having the same subreport this many
times. I tried writing the code but it didn't work.

I am asking for assistance with writing the code for referencing a different
query with the same subreport.

Thank you very much!

 

[Duane Hookom]

If I understand correctly you want to use multiple copies of a single
subreport in a main report. One copy of the subreport would display counts by
a group and the other subreport might display averages by the same group.

You can do this by creating a union query to use as your subreport's record
source. For instance in the Orders table of Northwind, if you want to display
Counts of orders by customer in one sub and averages of freight in the other
sub. Create a union query like:

SELECT Orders.CustomerID, Avg(Orders.Freight) AS TheValue,
"TheAvg" AS Attribute
FROM Orders
GROUP BY Orders.CustomerID, "TheAvg"
UNION ALL
SELECT Orders.CustomerID, Count(Orders.Freight), "TheCount"
FROM Orders
GROUP BY Orders.CustomerID, "TheCount";

Then use two text boxes in your main report
Name: txtCount
Control Source: ="TheCount"

Name: txtAvg
Control Source: ="TheAvg"

These can be used in the link master/child properties to match with the
[Attribute] field in the subreport.

 

[Duane Hookom]

If I understand correctly you want to use multiple copies of a single
subreport in a main report. One copy of the subreport would display counts by
a group and the other subreport might display averages by the same group.

You can do this by creating a union query to use as your subreport's record
source. For instance in the Orders table of Northwind, if you want to display
Counts of orders by customer in one sub and averages of freight in the other
sub. Create a union query like:

SELECT Orders.CustomerID, Avg(Orders.Freight) AS TheValue,
"TheAvg" AS Attribute
FROM Orders
GROUP BY Orders.CustomerID, "TheAvg"
UNION ALL
SELECT Orders.CustomerID, Count(Orders.Freight), "TheCount"
FROM Orders
GROUP BY Orders.CustomerID, "TheCount";

Then use two text boxes in your main report
Name: txtCount
Control Source: ="TheCount"

Name: txtAvg
Control Source: ="TheAvg"

These can be used in the link master/child properties to match with the
[Attribute] field in the subreport.

 

[Miskacee]

I want to use one subreport and one report then I want to use the same
subreport on a different report. So, no, not multiple copies on one report.

If I understand correctly you want to use multiple copies of a single
subreport in a main report. One copy of the subreport would display counts by
a group and the other subreport might display averages by the same group.

You can do this by creating a union query to use as your subreport's record
source. For instance in the Orders table of Northwind, if you want to display
Counts of orders by customer in one sub and averages of freight in the other
sub. Create a union query like:

SELECT Orders.CustomerID, Avg(Orders.Freight) AS TheValue,
"TheAvg" AS Attribute
FROM Orders
GROUP BY Orders.CustomerID, "TheAvg"
UNION ALL
SELECT Orders.CustomerID, Count(Orders.Freight), "TheCount"
FROM Orders
GROUP BY Orders.CustomerID, "TheCount";

Then use two text boxes in your main report
Name: txtCount
Control Source: ="TheCount"

Name: txtAvg
Control Source: ="TheAvg"

These can be used in the link master/child properties to match with the
[Attribute] field in the subreport.
--
Duane Hookom
Microsoft Access MVP

I apologize for saying subform. I knew what I meant but you can't read my
mind I actually meant subreport. I understand totally about child/master
links. I still don't think I am explaining this correctly. Just replace
subform with subreport. Let me try this way: I have on the left a subreport
showing the reason why someone left employement and the number of people
selecting this reason. On the right I have a graph on the report showing the
same data as an average. Depending upon what the user selects, there will
always be a pie chart on the right of a number or average representation and
on the left, the same subreport, showing the number of employees selecting
that reason. The drop down list boxes for selecting the type of graph
depends upon location, country, etc. This is the reason for the same
subreport on the left with different data representation. I use the same
subreport whenever the user selects criteria by dates. Hope this helps. I
have no problems linking up the correct data if I use a different subreport
for each report. However, I only want to reference sone subreport instead of
10 like I have now. There is no point in having the same subreport this many
times. I tried writing the code but it didn't work.

I am asking for assistance with writing the code for referencing a different
query with the same subreport.

Thank you very much!

 

[Duane Hookom]

How would the records be different in one report from another report? Would
the fields be the same? Would the value in one field/column be different when
used in the different main reports?

--
Duane Hookom
Microsoft Access MVP

I want to use one subreport and one report then I want to use the same
subreport on a different report. So, no, not multiple copies on one report.

If I understand correctly you want to use multiple copies of a single
subreport in a main report. One copy of the subreport would display counts by
a group and the other subreport might display averages by the same group.

You can do this by creating a union query to use as your subreport's record
source. For instance in the Orders table of Northwind, if you want to display
Counts of orders by customer in one sub and averages of freight in the other
sub. Create a union query like:

SELECT Orders.CustomerID, Avg(Orders.Freight) AS TheValue,
"TheAvg" AS Attribute
FROM Orders
GROUP BY Orders.CustomerID, "TheAvg"
UNION ALL
SELECT Orders.CustomerID, Count(Orders.Freight), "TheCount"
FROM Orders
GROUP BY Orders.CustomerID, "TheCount";

Then use two text boxes in your main report
Name: txtCount
Control Source: ="TheCount"

Name: txtAvg
Control Source: ="TheAvg"

These can be used in the link master/child properties to match with the
[Attribute] field in the subreport.
--
Duane Hookom
Microsoft Access MVP

I apologize for saying subform. I knew what I meant but you can't read my
mind I actually meant subreport. I understand totally about child/master
links. I still don't think I am explaining this correctly. Just replace
subform with subreport. Let me try this way: I have on the left a subreport
showing the reason why someone left employement and the number of people
selecting this reason. On the right I have a graph on the report showing the
same data as an average. Depending upon what the user selects, there will
always be a pie chart on the right of a number or average representation and
on the left, the same subreport, showing the number of employees selecting
that reason. The drop down list boxes for selecting the type of graph
depends upon location, country, etc. This is the reason for the same
subreport on the left with different data representation. I use the same
subreport whenever the user selects criteria by dates. Hope this helps. I
have no problems linking up the correct data if I use a different subreport
for each report. However, I only want to reference sone subreport instead of
10 like I have now. There is no point in having the same subreport this many
times. I tried writing the code but it didn't work.

I am asking for assistance with writing the code for referencing a different
query with the same subreport.

Thank you very much!

:

I assume you mean subreport and not subform. The standard method of
influencing the records displayed in a subform or subreport is to use the
Link Master/Child properties of the subreport control. This allows you to
filter the sub based on a field or fields in the main.

Can't you use this method? How are your record sources different?

--
Duane Hookom
Microsoft Access MVP


:

what do you mean by influence the filters?
I have a subform which shows the number of employees by region and then on
the report there is a pie chart showing the average number of employees by
department. On all charts/graphs to the left, I show the actual number of
employees. I have various graphs but always the same subform only the
subform chnages, based upon the criteria selected. I am not certain how to
have only one subform to be used on 10 different charts/graphs. I don't know
how to 'influence' or select a particular query when opening up that
particular chart/graph.

If you need more info, please let me know. Right now I have 10 of the same
subforms each with a different record source and I know I should only have
one subform and each subform point at a different record source but don't
understanding where/how to place this code.

THANKS!

:

Can you explain why you need different record sources for the same subreport?
Wouldn't you just need to some how influence the filter of the records?
--
Duane Hookom
Microsoft Access MVP


:

How can I use the same sub-report on various reports but have a different
source property? I understand that I should not have anything on record
source but I don't know how to reference the particular query I want to use
for that particular report.

Thank you!

 

[Miskacee]

go back and reread my messages, i explain details above., information is
different based upon drop down list boxes from a form and criteria
identifying what the user wants, by location, cntry, reason why leaving.
\

How would the records be different in one report from another report? Would
the fields be the same? Would the value in one field/column be different when
used in the different main reports?

--
Duane Hookom
Microsoft Access MVP

I want to use one subreport and one report then I want to use the same
subreport on a different report. So, no, not multiple copies on one report.

If I understand correctly you want to use multiple copies of a single
subreport in a main report. One copy of the subreport would display counts by
a group and the other subreport might display averages by the same group.

You can do this by creating a union query to use as your subreport's record
source. For instance in the Orders table of Northwind, if you want to display
Counts of orders by customer in one sub and averages of freight in the other
sub. Create a union query like:

SELECT Orders.CustomerID, Avg(Orders.Freight) AS TheValue,
"TheAvg" AS Attribute
FROM Orders
GROUP BY Orders.CustomerID, "TheAvg"
UNION ALL
SELECT Orders.CustomerID, Count(Orders.Freight), "TheCount"
FROM Orders
GROUP BY Orders.CustomerID, "TheCount";

Then use two text boxes in your main report
Name: txtCount
Control Source: ="TheCount"

Name: txtAvg
Control Source: ="TheAvg"

These can be used in the link master/child properties to match with the
[Attribute] field in the subreport.
--
Duane Hookom
Microsoft Access MVP


:

I apologize for saying subform. I knew what I meant but you can't read my
mind I actually meant subreport. I understand totally about child/master
links. I still don't think I am explaining this correctly. Just replace
subform with subreport. Let me try this way: I have on the left a subreport
showing the reason why someone left employement and the number of people
selecting this reason. On the right I have a graph on the report showing the
same data as an average. Depending upon what the user selects, there will
always be a pie chart on the right of a number or average representation and
on the left, the same subreport, showing the number of employees selecting
that reason. The drop down list boxes for selecting the type of graph
depends upon location, country, etc. This is the reason for the same
subreport on the left with different data representation. I use the same
subreport whenever the user selects criteria by dates. Hope this helps. I
have no problems linking up the correct data if I use a different subreport
for each report. However, I only want to reference sone subreport instead of
10 like I have now. There is no point in having the same subreport this many
times. I tried writing the code but it didn't work.

I am asking for assistance with writing the code for referencing a different
query with the same subreport.

Thank you very much!

:

I assume you mean subreport and not subform. The standard method of
influencing the records displayed in a subform or subreport is to use the
Link Master/Child properties of the subreport control. This allows you to
filter the sub based on a field or fields in the main.

Can't you use this method? How are your record sources different?

--
Duane Hookom
Microsoft Access MVP


:

what do you mean by influence the filters?
I have a subform which shows the number of employees by region and then on
the report there is a pie chart showing the average number of employees by
department. On all charts/graphs to the left, I show the actual number of
employees. I have various graphs but always the same subform only the
subform chnages, based upon the criteria selected. I am not certain how to
have only one subform to be used on 10 different charts/graphs. I don't know
how to 'influence' or select a particular query when opening up that
particular chart/graph.

If you need more info, please let me know. Right now I have 10 of the same
subforms each with a different record source and I know I should only have
one subform and each subform point at a different record source but don't
understanding where/how to place this code.

THANKS!

:

Can you explain why you need different record sources for the same subreport?
Wouldn't you just need to some how influence the filter of the records?
--
Duane Hookom
Microsoft Access MVP


:

How can I use the same sub-report on various reports but have a different
source property? I understand that I should not have anything on record
source but I don't know how to reference the particular query I want to use
for that particular report.

Thank you!

 

[Duane Hookom]

If the records are the only difference then use controls on a form to filter
the records that get displayed in the subreport. What exactly "depends upon
location, country, etc"? Do the columns/field depend on this or just the
records displayed in the subreport?

If it is only the filtering of the records that will change, then either use
code to modify the SQL property of the query used as the record source or use
controls on a form to filter the records.
--
Duane Hookom
Microsoft Access MVP

How would the records be different in one report from another report? Would
the fields be the same? Would the value in one field/column be different when
used in the different main reports?

--
Duane Hookom
Microsoft Access MVP

I want to use one subreport and one report then I want to use the same
subreport on a different report. So, no, not multiple copies on one report.

If I understand correctly you want to use multiple copies of a single
subreport in a main report. One copy of the subreport would display counts by
a group and the other subreport might display averages by the same group.

You can do this by creating a union query to use as your subreport's record
source. For instance in the Orders table of Northwind, if you want to display
Counts of orders by customer in one sub and averages of freight in the other
sub. Create a union query like:

SELECT Orders.CustomerID, Avg(Orders.Freight) AS TheValue,
"TheAvg" AS Attribute
FROM Orders
GROUP BY Orders.CustomerID, "TheAvg"
UNION ALL
SELECT Orders.CustomerID, Count(Orders.Freight), "TheCount"
FROM Orders
GROUP BY Orders.CustomerID, "TheCount";

Then use two text boxes in your main report
Name: txtCount
Control Source: ="TheCount"

Name: txtAvg
Control Source: ="TheAvg"

These can be used in the link master/child properties to match with the
[Attribute] field in the subreport.
--
Duane Hookom
Microsoft Access MVP


:

I apologize for saying subform. I knew what I meant but you can't read my
mind I actually meant subreport. I understand totally about child/master
links. I still don't think I am explaining this correctly. Just replace
subform with subreport. Let me try this way: I have on the left a subreport
showing the reason why someone left employement and the number of people
selecting this reason. On the right I have a graph on the report showing the
same data as an average. Depending upon what the user selects, there will
always be a pie chart on the right of a number or average representation and
on the left, the same subreport, showing the number of employees selecting
that reason. The drop down list boxes for selecting the type of graph
depends upon location, country, etc. This is the reason for the same
subreport on the left with different data representation. I use the same
subreport whenever the user selects criteria by dates. Hope this helps. I
have no problems linking up the correct data if I use a different subreport
for each report. However, I only want to reference sone subreport instead of
10 like I have now. There is no point in having the same subreport this many
times. I tried writing the code but it didn't work.

I am asking for assistance with writing the code for referencing a different
query with the same subreport.

Thank you very much!

:

I assume you mean subreport and not subform. The standard method of
influencing the records displayed in a subform or subreport is to use the
Link Master/Child properties of the subreport control. This allows you to
filter the sub based on a field or fields in the main.

Can't you use this method? How are your record sources different?

--
Duane Hookom
Microsoft Access MVP


:

what do you mean by influence the filters?
I have a subform which shows the number of employees by region and then on
the report there is a pie chart showing the average number of employees by
department. On all charts/graphs to the left, I show the actual number of
employees. I have various graphs but always the same subform only the
subform chnages, based upon the criteria selected. I am not certain how to
have only one subform to be used on 10 different charts/graphs. I don't know
how to 'influence' or select a particular query when opening up that
particular chart/graph.

If you need more info, please let me know. Right now I have 10 of the same
subforms each with a different record source and I know I should only have
one subform and each subform point at a different record source but don't
understanding where/how to place this code.

THANKS!

:

Can you explain why you need different record sources for the same subreport?
Wouldn't you just need to some how influence the filter of the records?
--
Duane Hookom
Microsoft Access MVP


:

How can I use the same sub-report on various reports but have a different
source property? I understand that I should not have anything on record
source but I don't know how to reference the particular query I want to use
for that particular report.

Thank you!

 

[Miskacee]

This is way harder than I expected - trying to get a simple answer. All I
wanted to know in general, is how to use one subreport on various reports.
My main question is where do I put the code, how do I do this - when opening
one report, then use qry_xks if another report is generated then on the
subreport use qry_abc. That is all I'm trying to ask.

If the records are the only difference then use controls on a form to filter
the records that get displayed in the subreport. What exactly "depends upon
location, country, etc"? Do the columns/field depend on this or just the
records displayed in the subreport?

If it is only the filtering of the records that will change, then either use
code to modify the SQL property of the query used as the record source or use
controls on a form to filter the records.
--
Duane Hookom
Microsoft Access MVP

How would the records be different in one report from another report? Would
the fields be the same? Would the value in one field/column be different when
used in the different main reports?

--
Duane Hookom
Microsoft Access MVP

I want to use one subreport and one report then I want to use the same
subreport on a different report. So, no, not multiple copies on one report.

:

If I understand correctly you want to use multiple copies of a single
subreport in a main report. One copy of the subreport would display counts by
a group and the other subreport might display averages by the same group.

You can do this by creating a union query to use as your subreport's record
source. For instance in the Orders table of Northwind, if you want to display
Counts of orders by customer in one sub and averages of freight in the other
sub. Create a union query like:

SELECT Orders.CustomerID, Avg(Orders.Freight) AS TheValue,
"TheAvg" AS Attribute
FROM Orders
GROUP BY Orders.CustomerID, "TheAvg"
UNION ALL
SELECT Orders.CustomerID, Count(Orders.Freight), "TheCount"
FROM Orders
GROUP BY Orders.CustomerID, "TheCount";

Then use two text boxes in your main report
Name: txtCount
Control Source: ="TheCount"

Name: txtAvg
Control Source: ="TheAvg"

These can be used in the link master/child properties to match with the
[Attribute] field in the subreport.
--
Duane Hookom
Microsoft Access MVP


:

I apologize for saying subform. I knew what I meant but you can't read my
mind I actually meant subreport. I understand totally about child/master
links. I still don't think I am explaining this correctly. Just replace
subform with subreport. Let me try this way: I have on the left a subreport
showing the reason why someone left employement and the number of people
selecting this reason. On the right I have a graph on the report showing the
same data as an average. Depending upon what the user selects, there will
always be a pie chart on the right of a number or average representation and
on the left, the same subreport, showing the number of employees selecting
that reason. The drop down list boxes for selecting the type of graph
depends upon location, country, etc. This is the reason for the same
subreport on the left with different data representation. I use the same
subreport whenever the user selects criteria by dates. Hope this helps. I
have no problems linking up the correct data if I use a different subreport
for each report. However, I only want to reference sone subreport instead of
10 like I have now. There is no point in having the same subreport this many
times. I tried writing the code but it didn't work.

I am asking for assistance with writing the code for referencing a different
query with the same subreport.

Thank you very much!

:

I assume you mean subreport and not subform. The standard method of
influencing the records displayed in a subform or subreport is to use the
Link Master/Child properties of the subreport control. This allows you to
filter the sub based on a field or fields in the main.

Can't you use this method? How are your record sources different?

--
Duane Hookom
Microsoft Access MVP


:

what do you mean by influence the filters?
I have a subform which shows the number of employees by region and then on
the report there is a pie chart showing the average number of employees by
department. On all charts/graphs to the left, I show the actual number of
employees. I have various graphs but always the same subform only the
subform chnages, based upon the criteria selected. I am not certain how to
have only one subform to be used on 10 different charts/graphs. I don't know
how to 'influence' or select a particular query when opening up that
particular chart/graph.

If you need more info, please let me know. Right now I have 10 of the same
subforms each with a different record source and I know I should only have
one subform and each subform point at a different record source but don't
understanding where/how to place this code.

THANKS!

:

Can you explain why you need different record sources for the same subreport?
Wouldn't you just need to some how influence the filter of the records?
--
Duane Hookom
Microsoft Access MVP


:

How can I use the same sub-report on various reports but have a different
source property? I understand that I should not have anything on record
source but I don't know how to reference the particular query I want to use
for that particular report.

Thank you!