How to define a Report to display 3 different type of Info

[Guest]

Hi

Hope some one can help me with this. I am required to create in 1 report showing 3 sections of information

----------- report details -----------------------

Total Number of Applications : [###] ---- section

Type of Business Number of Application
-----------------------------------------
[Business 1] [###] ---- section
[Business 2] [###
[etc.. ] [###

Receipt Method Numbe
-------------------------
[Method 1] [###] ----- section
[Method 2] [###
[etc... ] [###

------------- end of report details ---------------

Here is my Report >> Record Souce query and its results

SELECT * FROM [SELECT count(*), 'Total_Number', 1 FROM Busines
UNION SELECT count(*), BusinessType, 2 FROM Business GROUP BY BusinessTyp
UNION SELECT count(*), [Receipt Method], 3 FROM Business GROUP BY [Receipt Method]
]. AS [%$##@_Alias] ORDER BY 3, 2

Expr1000 Expr1001 Expr100
------------------------------------------------
10 Total_Number 1 {section 1 data
2 Exhibits 2 {section 2 data}
8 Community 2 { "
9 Mail 3 {section 3 data
1 Email 3 { "

I have all 3 sections defined in the Reports Details of the report. Question is how do I bind the control source of each [ ] textboxes so that it displays its associated section data only. What is happening is that Sections(1,2,3) in the Report Details are repeating for the number of times of data found. Is there a way I can get my report right ???

 

[Duane Hookom]

A report has only one details section so I don't understand '3 sections
defined in the Reports Details of the report'. I would not waste time
sorting the data in the query.
I would set up my report with a query like:
SELECT Count(*) as NumOf, Total_Number As Grouping, 1 as Category
FROM Business
UNION
SELECT Count(*), BusinessType, 2
FROM Business
GROUP BY BusinessType
UNION
SELECT Count(*), [Receipt Method], 3
FROM Business
GROUP BY [Receipt Method];

Then sort and group by Category then Grouping.
--
Duane Hookom
MS Access MVP

Hi,

Hope some one can help me with this. I am required to create in 1 report

showing 3 sections of information :

----------- report details ------------------------

Total Number of Applications : [###] ---- section 1

Type of Business Number of Applications
------------------------------------------
[Business 1] [###] ---- section 2
[Business 2] [###]
[etc.. ] [###]

Receipt Method Number
--------------------------
[Method 1] [###] ----- section 3
[Method 2] [###]
[etc... ] [###]

------------- end of report details ---------------

Here is my Report >> Record Souce query and its results :

SELECT * FROM [SELECT count(*), 'Total_Number', 1 FROM Business
UNION SELECT count(*), BusinessType, 2 FROM Business GROUP BY BusinessType
UNION SELECT count(*), [Receipt Method], 3 FROM Business GROUP BY [Receipt Method]
]. AS [%$##@_Alias] ORDER BY 3, 2;

Expr1000 Expr1001 Expr1002
-------------------------------------------------
10 Total_Number 1 {section 1 data}
2 Exhibits 2 {section 2 data}
8 Community 2 { " }
9 Mail 3 {section 3 data}
1 Email 3 { " }

I have all 3 sections defined in the Reports Details of the report.

Question is how do I bind the control source of each [ ] textboxes so that
it displays its associated section data only. What is happening is that
Sections(1,2,3) in the Report Details are repeating for the number of times
of data found. Is there a way I can get my report right ???

 

[Guest]

Hi Duane

Thank you for your reply. I am a newbie to access and am not quite clear your solution. Maybe I should explain more

My query returns the results I need to assemble into the report
Expr1000 Expr1001 Expr100
------------------------------------------------
10 Total_Number 1
2 Exhibits 2
8 Community 2
9 Mail 3
1 Email 3

I have the following controls in my report detail
-----------------------------------------------------------
lblTotal Number of Applications : txtTotalNumber (control source : Expr1000

lblType of Business lblNumber of Application
-------------------------------------------------------
txtBusiness txtBusinessCount (control source Expr1001 & Expr1000

lblReceipt Method lblNumbe
-------------------------------------------------
txtMethod txtMethodCount (control source Expr1001 & Expr1000

------------- end of report details ---------------

The problem is the above report details is repeated for every row of recordset returned from the query - this instance it is
repeating 5 times

I would like be able to let the report know that for the control [txtTotalNumber], it should only get from the recordset the record that has Expr1002 = 1, so it will only print 1 data item

ANd for the controls [txtBusiness] & [txtBusinessCount] - find the recordset which Expr1002 = 2 and print it, so it will print 2 data items since there is 2 rows matching the query. Similarly to the controls [txtMethod] & [txtMethodCount

Do I have the right approach to generating this report or is there an alternative way to do so. Please help -- I have 2 other reports which is similar to this but with more of these mini-reports inside 1 Print Preview. Also, any recommendation of a good Access book for reference. I am a developer using ASP & various scripting languages but its my first time working with Access

Thank you
Camero

 

[Duane Hookom]

Are you using subreports? Your statement "mini-reports inside" suggests this
but it is not clear.
Since you are a newbie, do yourself a favor and use proper column aliases so
you aren't dealing with names like "Expr1000" and "Expr1001".

--
Duane Hookom
MS Access MVP

Hi Duane,

Thank you for your reply. I am a newbie to access and am not quite clear

your solution. Maybe I should explain more.

My query returns the results I need to assemble into the report.
Expr1000 Expr1001 Expr1002
-------------------------------------------------
10 Total_Number 1
2 Exhibits 2
8 Community 2
9 Mail 3
1 Email 3


I have the following controls in my report details
------------------------------------------------------------
lblTotal Number of Applications : txtTotalNumber (control source : Expr1000)


lblType of Business lblNumber of Applications
--------------------------------------------------------
txtBusiness txtBusinessCount (control source Expr1001 & Expr1000)

lblReceipt Method lblNumber
--------------------------------------------------
txtMethod txtMethodCount (control source Expr1001 & Expr1000)

------------- end of report details ---------------

The problem is the above report details is repeated for every row of

recordset returned from the query - this instance it is

repeating 5 times.

I would like be able to let the report know that for the control

[txtTotalNumber], it should only get from the recordset the record that has
Expr1002 = 1, so it will only print 1 data item.

ANd for the controls [txtBusiness] & [txtBusinessCount] - find the

recordset which Expr1002 = 2 and print it, so it will print 2 data items
since there is 2 rows matching the query. Similarly to the controls
[txtMethod] & [txtMethodCount]

Do I have the right approach to generating this report or is there an

alternative way to do so. Please help -- I have 2 other reports which is
similar to this but with more of these mini-reports inside 1 Print Preview.
Also, any recommendation of a good Access book for reference. I am a
developer using ASP & various scripting languages but its my first time
working with Access.

 

[Guest]

Hi

No, I am not using subreports. I have tried though after learning about subreports while reading some discussions in this newsgroup. I created a main report that has 3 subreports within it. The only problem though is the Business subreport (maybe 3 inch height) physical container size does not grow with more data items to be displayed - a right hand scrollbar was available for scrolling but i do not want the scrollbar to be there - I need the entire container expanded to display all records. This is another question altogether though

Would certainly like to use meaning names besides the The Expr1000...2 I just do not know how to make alias in the bunch of select statements. My priority now is to find out how to produce the required report and then work on getting aliases into my select statement after

For what I need, is subreport the only way to implement ? or it is doable from what I have explained in my previous post ? Then, the question of how to handle those controls mentioned in my previous post

Thank yo

 

[Duane Hookom]

My first reply suggests a solution. Did you try it?

--
Duane Hookom
MS Access MVP

Hi,

No, I am not using subreports. I have tried though after learning about

subreports while reading some discussions in this newsgroup. I created a
main report that has 3 subreports within it. The only problem though is the
Business subreport (maybe 3 inch height) physical container size does not
grow with more data items to be displayed - a right hand scrollbar was
available for scrolling but i do not want the scrollbar to be there - I need
the entire container expanded to display all records. This is another
question altogether though.

Would certainly like to use meaning names besides the The Expr1000...2 I

just do not know how to make alias in the bunch of select statements. My
priority now is to find out how to produce the required report and then work
on getting aliases into my select statement after.

For what I need, is subreport the only way to implement ? or it is doable

from what I have explained in my previous post ? Then, the question of how
to handle those controls mentioned in my previous post.

 

[Guest]

yes, I tried but I still do not understand how to get the records I want into the respective section in the reports.

 

[Duane Hookom]

Does the union query that I suggested return the results as expected? Did
you set up the report's sorting and grouping as suggested? Did you display
the Category Header so that you have distinct sections for each category?

--
Duane Hookom
MS Access MVP
--

yes, I tried but I still do not understand how to get the records I want

into the respective section in the reports.

 

[Duane Hookom]

While in the report design view, select View|Sorting and Grouping... This
will open a dialog form that allows you to specify the sorting and grouping
in the report.

--
Duane Hookom
MS Access MVP
--

1) Yes, union query suggested returned results as expected with the nice aliases.

2) report's sorting and grouping - do not know what is that and do not

know how to use it - am now feeling my way into this report grouping thing.

3) Display Category Header -- do not know what is that and do not know how to use it.

I have quite a bit of learning to do on access reports concept. For some

one who has some access report knowledge will probably understand the good
tips you have provided. As for me, I am lost in access reports.

 

[Guest]

Hi Duane

I specified the Sorting and Grouping ==> by Category then Grouping in the report. I said YES to GroupHeader - I assume I put all my labels and textboxes in the GroupHeader section. The results are still repeating sets of information :

The subreport thing is working for me so far. I have a main report and I included 3 subreports, each subreport with its details and a total, in it. My prototype is producing the results layout that I want -- hooray! Then, I start to add the cosmetics to the main report; a picture, a title, date in the pageHeader. This seems to mess up my results layout - where my the details and its count data are all wrong. What did I do wrong or not do this time

----- Duane Hookom wrote: ----

While in the report design view, select View|Sorting and Grouping... Thi
will open a dialog form that allows you to specify the sorting and groupin
in the report

--
Duane Hooko
MS Access MV
-

1) Yes, union query suggested returned results as expected with the nic aliases

one who has some access report knowledge will probably understand the goo
tips you have provided. As for me, I am lost in access reports

 

[Duane Hookom]

I seem to be losing sight of what you actually want for your final layout.
Is it the same as you suggested in the beginning of this thread? When using
my suggestion, did you include any subreport? The only controls in the group
headers should be group specific information. All the detail fields should
be in the detail section.

--
Duane Hookom
MS Access MVP
--

Hi Duane,

I specified the Sorting and Grouping ==> by Category then Grouping in the

report. I said YES to GroupHeader - I assume I put all my labels and
textboxes in the GroupHeader section. The results are still repeating sets
of information

The subreport thing is working for me so far. I have a main report and I

included 3 subreports, each subreport with its details and a total, in it.
My prototype is producing the results layout that I want -- hooray! Then,
I start to add the cosmetics to the main report; a picture, a title, date in
the pageHeader. This seems to mess up my results layout - where my the
details and its count data are all wrong. What did I do wrong or not do
this time !

 

[Guest]

Hi Duane

Sorry... and thank you for being patient - l am all over myself trying different approaches and feeling lost and finally the subreport approach seems to be working except for a little glitch

To answer your question : YES, the same as suggested in the first post for the Visual layout. Using your suggestion - NO, did not include subreport. I am not familiar with the Group Header/Details concept - I tried using your suggestion with the very little I know - I might not have implemented it right and therefore not working the way it should

This subreport thing - earlier today I had started a raw Report page, added a labelTitle and 3 subreports as a test. My subreports if I preview it individually displays results as I expected. In the main report, I preview it and I see results as expected from the 3 subreports. So far so good

Finally, I started adding the rest of items (like logo, date, meaningful title) and when I did a preview, the results in subreport 2 & subreport 3 were wrong :- number of rows returned was reduced and its associated COUNT value was wrong

If I were to revert to my raw Report page before adding rest of items - the results were good. Any suggestion what I did not do right




----- Duane Hookom wrote: ----

I seem to be losing sight of what you actually want for your final layout
Is it the same as you suggested in the beginning of this thread? When usin
my suggestion, did you include any subreport? The only controls in the grou
headers should be group specific information. All the detail fields shoul
be in the detail section

--
Duane Hooko
MS Access MV
-

Hi Duane

report. I said YES to GroupHeader - I assume I put all my labels an
textboxes in the GroupHeader section. The results are still repeating set
of information :included 3 subreports, each subreport with its details and a total, in it
My prototype is producing the results layout that I want -- hooray! Then
I start to add the cosmetics to the main report; a picture, a title, date i
the pageHeader. This seems to mess up my results layout - where my th
details and its count data are all wrong. What did I do wrong or not d
this time

 

[Guest]

Hi Duane

It works ! it works and it is working ..... see, I was in my Main report Design View, from here, I switched to the Layout Preview - and I see my results whacky. That's where I just could not figure whatever happened to my results that I expected and is producing good when I look at the subreport itself. I closed my main report and launch this report from the explorer view - and the results are all looking goody...

Now, that I am not sweating over more time on my first report out ... I may have some time to try your suggestion which I'd prefer to avoid having subreports (I will have too many of it)

Thank you again for your valuable help... ;