Solved

Maximum number of sub reports in an Access report

Posted on 2004-04-26
12
398 Views
Last Modified: 2013-11-28
Hi,

I had a problem many moons ago including over 100 sub reports in a Microsoft Access report. I can't remember what the symptom was but I think the report excluded any sub reports over a particular limit when I tried to print it out. I have been given a spec for a report that requires 120 aggregate count values e.g. select count(user) from users where gender = "M". None of the count values are made up of others therefore I cannot add count logic to the parent report itself, sub reports are the only way I know how.

Does anybody know if this limit still exists and if so if there is a way around it? I am using Access 2000.

Any help greatly appreciated.

Shane
0
Comment
Question by:smceneaney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10919072
In the help file the Access Specifications will reveal the numbers.

The only way around is to make sure that it fits always the data is in separate rows and thus you can use the detail line as many times as you like....
So many subreports and controls often point to a not normalized database design.

Nic;o)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10919101
I agree with nico5038...can you describe a bit more completely what you application is and how your tables are structured?

It sounds like you are take the LONG way around, and I strongly suspect that there is a better way to accomplish the same result.

AW

0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10919127
Hi smceneaney,

Yes, each Section in The Report have a Maximum of 22"

Hope this helps

Jaffer
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:smceneaney
ID: 10919201
Thanks for the quick response. I appreciate it! The tables are normalised however the way I am creating the report could possibly be daft. I'm a newby to Access reports. Because all of the figures in the report are totals and only one total (aggregate) is allowed in an sql query then I need to use many queries. I've included some examples below

select count(*) from users where gender = 'Male' and a_status in (x,y,z)
select count(*) from users where gender = 'Male' and another_status in (a,b,c)
select count(*) from users where gender = 'Male' and shoe_size in (8,9,10)
select count(*) from users where gender = 'Female' and a_status in (x,y,z)
select count(*) from users where gender = 'Female' and another_status in (a,b,c)
select count(*) from users where gender = 'Female' and shoe_size in (8,9,10)

What I am doing is putting each of these queries in a sub report and adding each sub report to my main report. Does this make sence?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10919244
For this you can use an IIF in the query like:

select IIF('Male' and a_status in (x,y,z),1,0) as MaleAstatus, IIF('Male' and a_status in (a,b,c),1,0) as MaleAnotherstatus, etc.

This will fill the fields with 1 when success full and can be summed in a groupby.

Getting the idea ?

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10919266
Hmm the statement is a bit "sloppy" in syntax, the real syntax would be like:
IIF([Gender]='Male' and ([a_status] ='x' or[a_status] ='y' or [a_status] ='z'),1,0)

Nic;o)
0
 

Author Comment

by:smceneaney
ID: 10919426
Thanks Nic. I am a bit thick. I understand from your query that IIF returns either a value of 1 or 0 depending on if the condition is true. The result from your query would produce something like

1 0 1 0 1
1 1 0 0 0
0 0 1 1 1
1 1 0 1 0
............
............

Each column represents one of my queries from the post above. What do I do with this data? Is it not the case that I can only SUM one of them at a time?

Thanks for your patience. Thick people from all around the world are thanking you!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10919536
Almost correct :-)
I guess you have a kind of "grouping" like per City so you would get e.g.

City      A B C D
London 1 0 1 0
London 0 1 0 1
London 1 0 0 1
Paris    0 1 0 0

By creating a GroupBy query on City and a sum per column you'll get:
London 2 1 1 2
Paris    0 1 0 0

The A = e.g. Male in x,y,z and B the Male in a,b,c

Getting the idea ?

Nic;o)
0
 

Author Comment

by:smceneaney
ID: 10919752
Nico, (apologies for leaving the "o" out)

Thanks again for the help. I understand from the above that I can end up with something like

 A   B    C    D   E
10  1    89  55  12

A = e.g. Male in x,y,z and B the Male in a,b,c etc

I don't understand where you are getting the multiple rows. All my queries currently return 1 value e.g. count(x) therefore there is nothing to group by apart from A, B, C, D and E.

I guess if I can have something like you suggest the report can cherry pick values out of the results. Is that right?
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 125 total points
ID: 10920026
I would have to see your table design and the 1 value returning queries, but in general I would expect that you have something like an answer table with e.g.:
User Gender Status
1         M        A
2         F        B
3         F         X
4         M        A

Or a table with one row with Gender, Status, Shoesize, etc.

Nic;o)
0
 

Author Comment

by:smceneaney
ID: 10947367
Thanks Nico. Haven't had a chance to get back to this one. Thought I should award points rather than waiting around. I think I need to get a book on report development in Access. Last question do you do the grouping in the report or in the query? I understand a report uses a query and can perfom grouping outside of the query.

Many thanks,

Shane
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10947572
For a report you'll normally do the grouping (and sorting) by pressing the group button (looks like =] ).
Only when you need detaillines with aggregated data (sum/count/avg) you can start with a groupby query.

Clear ?

Nic;o)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question