Unbound Report

  • Thread starter Thread starter Luis
  • Start date Start date
L

Luis

Hi:

I have the followinf problem with my report:

Problem:
I have created a report in which I added a subreport.
Well, I created the subreport using the Wixard and
selected "not to bound it"...The problem I have is that
now that I add new data into my subreport it adds it for
every contract that I have instead of adding only the we
are suppose to bill each contract...
Example
I get:
Contract LMOBG1022
Code Bill
H305 2
H306 1
H305 5
H306 2
Contract RAMBG0608
Code Bill
H305 2
H306 1
H305 5
H306 2
and I want to obtain:
Contract LMOBG1022
Code Bill
H305 2
H306 1
Contract RAMBG0608
Code Bill
H305 5
H305 2

How do I go back to tell it that Bill Units vary per
contract...I used the wizard to create my subreport and
selected unbound, but now I do not want to recreate the
entire report....

I hope it makes sense what I am asking...

Thank you...
 
I think all you have to do is set the Link Master/Child properties of the
subreport to your linking field (Contract?).
 
Duane:

I did what you have told me and this is what I get:
"You can't use a pass through query or a non-fixed-colum
crosstab query as a record source for a subform or
subreport.

Before you bind the suform or subreport to a crosstab
query, set the query's ColumnHeadings property."

I subreport comes from a query, but it is not
crosstab...I do not understand what it means about
setting ColumHeadings property. Can you please assit me
on this issue...
 
Apparently Access can't resolve the fields in your subreport or main report.
Can you provide the SQL views of both and the common/join field?
 
Good morning Duane:

The following is the SQL for the Query that makes up my
main report:
SELECT qry_protocols_medicare.Diagnosis,
tbl_protocols_cycle_frequency.Protocols,
tbl_protocols_cycle_frequency.Description,
tbl_protocols_cycle_frequency.[Number of Treatments], Sum
(qry_protocols_medicare!Profit)+Sum
(qry_protocols_medicare![Infusion Profit]) AS [Profit per
Treatment], [Number of Treatments]*[Profit per Treatment]
AS [Profit per Regimen]
FROM (tbl_protocols_cycle_frequency LEFT JOIN
tbl_protocols_detail_hcpcs ON
tbl_protocols_cycle_frequency.Protocols =
tbl_protocols_detail_hcpcs.Protocols) LEFT JOIN
qry_protocols_medicare ON
(tbl_protocols_detail_hcpcs.HCPCS =
qry_protocols_medicare.HCPCS) AND
(tbl_protocols_detail_hcpcs.Protocols =
qry_protocols_medicare.Protocol)
GROUP BY qry_protocols_medicare.Diagnosis,
tbl_protocols_cycle_frequency.Protocols,
tbl_protocols_cycle_frequency.Description,
tbl_protocols_cycle_frequency.[Number of Treatments];

Now, this SQL pertains to the query that makes up my
subreport:
SELECT qry_supp_med.Diseases, qry_supp_med.Protocols,
qry_supp_med.HCPCS, tbl_supportive_drugs.Description,
tbl_supportive_drugs.TYPE, tbl_supportive_drugs.[Number
of Injections], qry_supp_med.PROFIT, [Number of
Injections]*[PROFIT] AS [PROFIT PER REGIMEN]
FROM tbl_supportive_drugs INNER JOIN qry_supp_med ON
(tbl_supportive_drugs.Protocols = qry_supp_med.Protocols)
AND (tbl_supportive_drugs.HCPCS = qry_supp_med.HCPCS)
GROUP BY qry_supp_med.Diseases, qry_supp_med.Protocols,
qry_supp_med.HCPCS, tbl_supportive_drugs.Description,
tbl_supportive_drugs.TYPE, tbl_supportive_drugs.[Number
of Injections], qry_supp_med.PROFIT;

I hope I am right about the common field, which
is "protocols"

What I am trying to show is the pertaining supportive
drugs in my subreport for each protocol in my main
report...I get both in separate queries and I am trying
to put them together in a report, but I selected unbound
when creating it through the wizard and it worked for one
protocol, but if I add another one, then I get the
results below...Then, when I bound the by protocol is
when I have issues also...Each protocol in my main report
will have the same supportive drugs, but different doses..

I hope I have not confused you by now...

Thanks a lot for all of your help...
 
Linking reports to subreports suggests that a single record in the main
report relates to multiple records in the subreport. I assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum(qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS [Profit per Regimen]
I would use:
[Number of Treatments] * (Sum(qry_protocols_medicare!Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As [Profit per Regimen]

--
Duane Hookom
MS Access MVP


Good morning Duane:

The following is the SQL for the Query that makes up my
main report:
SELECT qry_protocols_medicare.Diagnosis,
tbl_protocols_cycle_frequency.Protocols,
tbl_protocols_cycle_frequency.Description,
tbl_protocols_cycle_frequency.[Number of Treatments], Sum
(qry_protocols_medicare!Profit)+Sum
(qry_protocols_medicare![Infusion Profit]) AS [Profit per
Treatment], [Number of Treatments]*[Profit per Treatment]
AS [Profit per Regimen]
FROM (tbl_protocols_cycle_frequency LEFT JOIN
tbl_protocols_detail_hcpcs ON
tbl_protocols_cycle_frequency.Protocols =
tbl_protocols_detail_hcpcs.Protocols) LEFT JOIN
qry_protocols_medicare ON
(tbl_protocols_detail_hcpcs.HCPCS =
qry_protocols_medicare.HCPCS) AND
(tbl_protocols_detail_hcpcs.Protocols =
qry_protocols_medicare.Protocol)
GROUP BY qry_protocols_medicare.Diagnosis,
tbl_protocols_cycle_frequency.Protocols,
tbl_protocols_cycle_frequency.Description,
tbl_protocols_cycle_frequency.[Number of Treatments];

Now, this SQL pertains to the query that makes up my
subreport:
SELECT qry_supp_med.Diseases, qry_supp_med.Protocols,
qry_supp_med.HCPCS, tbl_supportive_drugs.Description,
tbl_supportive_drugs.TYPE, tbl_supportive_drugs.[Number
of Injections], qry_supp_med.PROFIT, [Number of
Injections]*[PROFIT] AS [PROFIT PER REGIMEN]
FROM tbl_supportive_drugs INNER JOIN qry_supp_med ON
(tbl_supportive_drugs.Protocols = qry_supp_med.Protocols)
AND (tbl_supportive_drugs.HCPCS = qry_supp_med.HCPCS)
GROUP BY qry_supp_med.Diseases, qry_supp_med.Protocols,
qry_supp_med.HCPCS, tbl_supportive_drugs.Description,
tbl_supportive_drugs.TYPE, tbl_supportive_drugs.[Number
of Injections], qry_supp_med.PROFIT;

I hope I am right about the common field, which
is "protocols"

What I am trying to show is the pertaining supportive
drugs in my subreport for each protocol in my main
report...I get both in separate queries and I am trying
to put them together in a report, but I selected unbound
when creating it through the wizard and it worked for one
protocol, but if I add another one, then I get the
results below...Then, when I bound the by protocol is
when I have issues also...Each protocol in my main report
will have the same supportive drugs, but different doses..

I hope I have not confused you by now...

Thanks a lot for all of your help...
-----Original Message-----
Apparently Access can't resolve the fields in your subreport or main report.
Can you provide the SQL views of both and the common/join field?

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the size of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single record in the main
report relates to multiple records in the subreport. I assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum (qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS [Profit per Regimen]
I would use:
[Number of Treatments] * (Sum(qry_protocols_medicare! Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As [Profit per Regimen]

--
Duane Hookom
MS Access MVP


Good morning Duane:

The following is the SQL for the Query that makes up my
main report:
SELECT qry_protocols_medicare.Diagnosis,
tbl_protocols_cycle_frequency.Protocols,
tbl_protocols_cycle_frequency.Description,
tbl_protocols_cycle_frequency.[Number of Treatments], Sum
(qry_protocols_medicare!Profit)+Sum
(qry_protocols_medicare![Infusion Profit]) AS [Profit per
Treatment], [Number of Treatments]*[Profit per Treatment]
AS [Profit per Regimen]
FROM (tbl_protocols_cycle_frequency LEFT JOIN
tbl_protocols_detail_hcpcs ON
tbl_protocols_cycle_frequency.Protocols =
tbl_protocols_detail_hcpcs.Protocols) LEFT JOIN
qry_protocols_medicare ON
(tbl_protocols_detail_hcpcs.HCPCS =
qry_protocols_medicare.HCPCS) AND
(tbl_protocols_detail_hcpcs.Protocols =
qry_protocols_medicare.Protocol)
GROUP BY qry_protocols_medicare.Diagnosis,
tbl_protocols_cycle_frequency.Protocols,
tbl_protocols_cycle_frequency.Description,
tbl_protocols_cycle_frequency.[Number of Treatments];

Now, this SQL pertains to the query that makes up my
subreport:
SELECT qry_supp_med.Diseases, qry_supp_med.Protocols,
qry_supp_med.HCPCS, tbl_supportive_drugs.Description,
tbl_supportive_drugs.TYPE, tbl_supportive_drugs.[Number
of Injections], qry_supp_med.PROFIT, [Number of
Injections]*[PROFIT] AS [PROFIT PER REGIMEN]
FROM tbl_supportive_drugs INNER JOIN qry_supp_med ON
(tbl_supportive_drugs.Protocols = qry_supp_med.Protocols)
AND (tbl_supportive_drugs.HCPCS = qry_supp_med.HCPCS)
GROUP BY qry_supp_med.Diseases, qry_supp_med.Protocols,
qry_supp_med.HCPCS, tbl_supportive_drugs.Description,
tbl_supportive_drugs.TYPE, tbl_supportive_drugs.[Number
of Injections], qry_supp_med.PROFIT;

I hope I am right about the common field, which
is "protocols"

What I am trying to show is the pertaining supportive
drugs in my subreport for each protocol in my main
report...I get both in separate queries and I am trying
to put them together in a report, but I selected unbound
when creating it through the wizard and it worked for one
protocol, but if I add another one, then I get the
results below...Then, when I bound the by protocol is
when I have issues also...Each protocol in my main report
will have the same supportive drugs, but different doses..

I hope I have not confused you by now...

Thanks a lot for all of your help...
-----Original Message-----
Apparently Access can't resolve the fields in your subreport or main report.
Can you provide the SQL views of both and the common/join field?

--
Duane Hookom
MS Access MVP


Duane:

I did what you have told me and this is what I get:
"You can't use a pass through query or a non-fixed- colum
crosstab query as a record source for a subform or
subreport.

Before you bind the suform or subreport to a crosstab
query, set the query's ColumnHeadings property."

I subreport comes from a query, but it is not
crosstab...I do not understand what it means about
setting ColumHeadings property. Can you please assit me
on this issue...


-----Original Message-----
I think all you have to do is set the Link Master/Child
properties of the
subreport to your linking field (Contract?).

--
Duane Hookom
MS Access MVP
--

message
Hi:

I have the followinf problem with my report:

Problem:
I have created a report in which I added a subreport.
Well, I created the subreport using the Wixard and
selected "not to bound it"...The problem I have is that
now that I add new data into my subreport it adds it
for
every contract that I have instead of adding only the
we
are suppose to bill each contract...
Example
I get:
Contract LMOBG1022
Code Bill
H305 2
H306 1
H305 5
H306 2
Contract RAMBG0608
Code Bill
H305 2
H306 1
H305 5
H306 2
and I want to obtain:
Contract LMOBG1022
Code Bill
H305 2
H306 1
Contract RAMBG0608
Code Bill
H305 5
H305 2

How do I go back to tell it that Bill Units vary per
contract...I used the wizard to create my subreport and
selected unbound, but now I do not want to recreate the
entire report....

I hope it makes sense what I am asking...

Thank you...


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Are any of your queries crosstabs like qry_supp_med? Are you trying to set
the link fileds using the builder or just typing the field names in?

--
Duane Hookom
MS Access MVP


You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the size of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single record in the main
report relates to multiple records in the subreport. I assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum (qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS [Profit per Regimen]
I would use:
[Number of Treatments] * (Sum(qry_protocols_medicare! Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As [Profit per Regimen]
Click to expand...
Click to expand...
 
My main report derives from qry_breast_adj_sum_A which
has 2 tables and a query called qry_protocols_medicare.
Qry_protocols_medicare has 5 tables and another query
called qry_medicare_profit_analysis.
Qry_medicare_profit_analysis has a field that comes from
a crosstab query called qry_preferred_vendor_cost_final...

Qry_protocols_medicare pulls a calculated field called
cost per mg from qry_medicare_profit_analysis which uses
the field called preferred cost from the crosstab query
to calculate cost per mg...


My subreport derives from qry_breast_adj_sum which has 1
table and 1 query called qry_supp_med...qry_supp_med has
4 tables and a query called qry_medicare_profit_analysis
which has one field coming from a crosstab query called
qry_preferred_vendor_cost_final...

qry_supp_med also pulls a calculated field from
qry_medicare_profit_analysis called cost per mg which is
the same as our main report...

I hope I have not confused you and that the answer to
this is simple...I do not have much time to complete
it..thank you again...
-----Original Message-----
Are any of your queries crosstabs like qry_supp_med? Are you trying to set
the link fileds using the builder or just typing the field names in?

--
Duane Hookom
MS Access MVP


You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the size of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single record in the main
report relates to multiple records in the subreport. I assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum (qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS [Profit per Regimen]
I would use:
[Number of Treatments] * (Sum(qry_protocols_medicare! Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As [Profit per Regimen]
Click to expand...
Click to expand...


.
Click to expand...
 
I am sorry, I did not answer your second question...I
have used the subform wizard from my toolbox...I also
tried putting my main report and Database Window side to
side and dragging what I want as subreport and the wizard
comes up automatically....
-----Original Message-----
Are any of your queries crosstabs like qry_supp_med? Are you trying to set
the link fileds using the builder or just typing the field names in?

--
Duane Hookom
MS Access MVP


You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the size of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single record in the main
report relates to multiple records in the subreport. I assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum (qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS [Profit per Regimen]
I would use:
[Number of Treatments] * (Sum(qry_protocols_medicare! Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As [Profit per Regimen]
Click to expand...
Click to expand...


.
Click to expand...
 
Since your error message stated something about a crosstab, I can't
understand why it took about 5 replies to find out that you actually do have
a crosstab query. Have you tried setting the Column Headings property of the
crosstab query as the error message suggested?

You don't need to use a builder or wizard to set the link Master/Child
properties. Just type in the common field name(s).

--
Duane Hookom
MS Access MVP


Luis said:
My main report derives from qry_breast_adj_sum_A which
has 2 tables and a query called qry_protocols_medicare.
Qry_protocols_medicare has 5 tables and another query
called qry_medicare_profit_analysis.
Qry_medicare_profit_analysis has a field that comes from
a crosstab query called qry_preferred_vendor_cost_final...

Qry_protocols_medicare pulls a calculated field called
cost per mg from qry_medicare_profit_analysis which uses
the field called preferred cost from the crosstab query
to calculate cost per mg...


My subreport derives from qry_breast_adj_sum which has 1
table and 1 query called qry_supp_med...qry_supp_med has
4 tables and a query called qry_medicare_profit_analysis
which has one field coming from a crosstab query called
qry_preferred_vendor_cost_final...

qry_supp_med also pulls a calculated field from
qry_medicare_profit_analysis called cost per mg which is
the same as our main report...

I hope I have not confused you and that the answer to
this is simple...I do not have much time to complete
it..thank you again...
-----Original Message-----
Are any of your queries crosstabs like qry_supp_med? Are you trying to set
the link fileds using the builder or just typing the field names in?

--
Duane Hookom
MS Access MVP


You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the size of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single
record in the main
report relates to multiple records in the subreport. I
assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum
(qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS
[Profit per Regimen]
I would use:
[Number of Treatments] * (Sum(qry_protocols_medicare!
Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As
[Profit per Regimen]
Click to expand...


.
Click to expand...
Click to expand...
 
This is the message suggested?
You can't use a pass through query or a non-fixed-column
crosstab query as a record source for a subform or
subreport.
Before you bind the subform or subreport to a crosstab
query, set the query's ColumnHeadings property.

What do they mean by set the query's ColumnHeadings
property? What do I need to set and how?

What do you mean the common filed name (s? You mean
clicking on the external part of the subreport and then
going to view, properties, data and typing common fiels...

I apologize for my ignorance....Thank you
-----Original Message-----
Since your error message stated something about a crosstab, I can't
understand why it took about 5 replies to find out that you actually do have
a crosstab query. Have you tried setting the Column Headings property of the
crosstab query as the error message suggested?

You don't need to use a builder or wizard to set the link Master/Child
properties. Just type in the common field name(s).

--
Duane Hookom
MS Access MVP


My main report derives from qry_breast_adj_sum_A which
has 2 tables and a query called qry_protocols_medicare.
Qry_protocols_medicare has 5 tables and another query
called qry_medicare_profit_analysis.
Qry_medicare_profit_analysis has a field that comes from
a crosstab query called qry_preferred_vendor_cost_final...

Qry_protocols_medicare pulls a calculated field called
cost per mg from qry_medicare_profit_analysis which uses
the field called preferred cost from the crosstab query
to calculate cost per mg...


My subreport derives from qry_breast_adj_sum which has 1
table and 1 query called qry_supp_med...qry_supp_med has
4 tables and a query called qry_medicare_profit_analysis
which has one field coming from a crosstab query called
qry_preferred_vendor_cost_final...

qry_supp_med also pulls a calculated field from
qry_medicare_profit_analysis called cost per mg which is
the same as our main report...

I hope I have not confused you and that the answer to
this is simple...I do not have much time to complete
it..thank you again...
-----Original Message-----
Are any of your queries crosstabs like qry_supp_med?
Click to expand...
Are
you trying to set
the link fileds using the builder or just typing the field names in?

--
Duane Hookom
MS Access MVP


You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the size of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single
record in the main
report relates to multiple records in the subreport. I
assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum
(qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS
[Profit per Regimen]
I would use:
[Number of Treatments] * (Sum (qry_protocols_medicare!
Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As
[Profit per Regimen]

--
Duane Hookom
MS Access MVP


.
Click to expand...
Click to expand...


.
Click to expand...
 
(Q) What do they mean by set the query's
ColumnHeadings property? What do I
need to set and how?
(A) Did you try look in Help?
Did you look in the crosstab query
properties dialog?

(Q) What do you mean the common filed name (s?
You mean clicking on the external part of the subreport
and then going to view, properties, data and typing
common fiels...
(A) I only meant the field value that is common (exists)
in both the Main Report and the Subreport.

--
Duane Hookom
MS Access MVP


Luis said:
This is the message suggested?
You can't use a pass through query or a non-fixed-column
crosstab query as a record source for a subform or
subreport.
Before you bind the subform or subreport to a crosstab
query, set the query's ColumnHeadings property.

What do they mean by set the query's ColumnHeadings
property? What do I need to set and how?

What do you mean the common filed name (s? You mean
clicking on the external part of the subreport and then
going to view, properties, data and typing common fiels...

I apologize for my ignorance....Thank you
-----Original Message-----
Since your error message stated something about a crosstab, I can't
understand why it took about 5 replies to find out that you actually do have
a crosstab query. Have you tried setting the Column Headings property of the
crosstab query as the error message suggested?

You don't need to use a builder or wizard to set the link Master/Child
properties. Just type in the common field name(s).

--
Duane Hookom
MS Access MVP


My main report derives from qry_breast_adj_sum_A which
has 2 tables and a query called qry_protocols_medicare.
Qry_protocols_medicare has 5 tables and another query
called qry_medicare_profit_analysis.
Qry_medicare_profit_analysis has a field that comes from
a crosstab query called qry_preferred_vendor_cost_final...

Qry_protocols_medicare pulls a calculated field called
cost per mg from qry_medicare_profit_analysis which uses
the field called preferred cost from the crosstab query
to calculate cost per mg...


My subreport derives from qry_breast_adj_sum which has 1
table and 1 query called qry_supp_med...qry_supp_med has
4 tables and a query called qry_medicare_profit_analysis
which has one field coming from a crosstab query called
qry_preferred_vendor_cost_final...

qry_supp_med also pulls a calculated field from
qry_medicare_profit_analysis called cost per mg which is
the same as our main report...

I hope I have not confused you and that the answer to
this is simple...I do not have much time to complete
it..thank you again...

-----Original Message-----
Are any of your queries crosstabs like qry_supp_med? Are
you trying to set
the link fileds using the builder or just typing the
field names in?

--
Duane Hookom
MS Access MVP


You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the size of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything
from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything
wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single
record in the main
report relates to multiple records in the subreport. I
assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum
(qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS
[Profit per Regimen]
I would use:
[Number of Treatments] * (Sum (qry_protocols_medicare!
Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As
[Profit per Regimen]

--
Duane Hookom
MS Access MVP


.
Click to expand...


.
Click to expand...
Click to expand...
 
Duane:

I apologize for not replying earlier, I just figure it
out by using help...Thanks a lot for your assistance, I
hope I did not drive you crazy...

All I had to do was to go to my crosstab queries, and
click on the backgroud, then properties and type all my
colum headings as they appear-in this case my vendor
names...it was simple...I almost quit on Access...Now, I
can finalize my pharmaceutical database...

Thanks a lot for all your help,

Luis
-----Original Message-----
(Q) What do they mean by set the query's
ColumnHeadings property? What do I
need to set and how?
(A) Did you try look in Help?
Did you look in the crosstab query
properties dialog?

(Q) What do you mean the common filed name (s?
You mean clicking on the external part of the subreport
and then going to view, properties, data and typing
common fiels...
(A) I only meant the field value that is common (exists)
in both the Main Report and the Subreport.

--
Duane Hookom
MS Access MVP


This is the message suggested?
You can't use a pass through query or a non-fixed- column
crosstab query as a record source for a subform or
subreport.
Before you bind the subform or subreport to a crosstab
query, set the query's ColumnHeadings property.

What do they mean by set the query's ColumnHeadings
property? What do I need to set and how?

What do you mean the common filed name (s? You mean
clicking on the external part of the subreport and then
going to view, properties, data and typing common fiels...

I apologize for my ignorance....Thank you
-----Original Message-----
Since your error message stated something about a crosstab, I can't
understand why it took about 5 replies to find out that you actually do have
a crosstab query. Have you tried setting the Column Headings property of the
crosstab query as the error message suggested?

You don't need to use a builder or wizard to set the link Master/Child
properties. Just type in the common field name(s).

--
Duane Hookom
MS Access MVP


My main report derives from qry_breast_adj_sum_A which
has 2 tables and a query called qry_protocols_medicare.
Qry_protocols_medicare has 5 tables and another query
called qry_medicare_profit_analysis.
Qry_medicare_profit_analysis has a field that comes from
a crosstab query called qry_preferred_vendor_cost_final...

Qry_protocols_medicare pulls a calculated field called
cost per mg from qry_medicare_profit_analysis which uses
the field called preferred cost from the crosstab query
to calculate cost per mg...


My subreport derives from qry_breast_adj_sum which
Click to expand...
has
1
table and 1 query called qry_supp_med...qry_supp_med has
4 tables and a query called qry_medicare_profit_analysis
which has one field coming from a crosstab query called
qry_preferred_vendor_cost_final...

qry_supp_med also pulls a calculated field from
qry_medicare_profit_analysis called cost per mg which is
the same as our main report...

I hope I have not confused you and that the answer to
this is simple...I do not have much time to complete
it..thank you again...

-----Original Message-----
Are any of your queries crosstabs like qry_supp_med? Are
you trying to set
the link fileds using the builder or just typing the
field names in?

--
Duane Hookom
MS Access MVP


You are correct Duane...I made the change and my querie
run perfect, but I still have the same problem with my
report and subreport...It displays the same message I
have received in my previous psoting...Does the
Click to expand...
size
of
text box matters? or grouping matters?

Thinking I was doing something wrong in my report and
subreport, I also run a test database using two
tables...Table One had First name, last name and
DOB...and table 2 had first name, and different items
purchase...when I created a report with a subreport
linked to table 2, it worked....but if I linked it to
subreport 2, it kept asking me for parameter value and
showed nothing...If I unbounded, it showe everything
from
subreport 2 for each name...I am confused...I also used
my Access QUE book to see if I was doing anything
wrong,
but I do not see anything different than what they
suggest...Please help...This report is making me go
crazy...I will keep trying and hopefully I will have
notived what I am doing wrong before your
response...Thank you so much for your help and hope to
hear from you...
-----Original Message-----
Linking reports to subreports suggests that a single
record in the main
report relates to multiple records in the
Click to expand...
subreport.
I
assume this is what
you want in your query.

The only issue I see is that you calculate a column:
Sum(qry_protocols_medicare!Profit) + Sum
(qry_protocols_medicare![Infusion
Profit]) AS [Profit per Treatment]
and then use [Profit per Treatment] in aother column:
[Number of Treatments]*[Profit per Treatment] AS
[Profit per Regimen]
I would use:
[Number of Treatments] * (Sum (qry_protocols_medicare!
Profit) +
Sum(qry_protocols_medicare![Infusion Profit])) As
[Profit per Regimen]

--
Duane Hookom
MS Access MVP


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top