Solved

Maximum number of sub reports in an Access report

Posted on 2004-04-26
12
393 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
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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now