Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Maximum number of sub reports in an Access report

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
smceneaney
Asked:
smceneaney
1 Solution
 
nico5038Commented:
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
 
Arthur_WoodCommented:
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
 
jjafferrCommented:
Hi smceneaney,

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

Hope this helps

Jaffer
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
smceneaneyAuthor Commented:
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
 
nico5038Commented:
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
 
nico5038Commented:
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
 
smceneaneyAuthor Commented:
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
 
nico5038Commented:
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
 
smceneaneyAuthor Commented:
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
 
nico5038Commented:
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
 
smceneaneyAuthor Commented:
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
 
nico5038Commented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now