By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Report that is totals only

prn
Expert 100+
P: 254
Hi folks,

I'm looking for a little advice here. I need to create a report that is totals only, with no detail records. I have a database with a lot of individuals (people) and the report has to have some overall totals, totals per state, totals by gender, race/ethnicity, and several other criteria. This report does not need to (and should not) report on each individual, just the totals.

I can create expressions for each of the items I need, but the question I'm looking for advice on is how to organize the overall report. I'm guessing that I should do it as multiple subreports included in a master report. Does that seem like the best procedure? Or should I just put them all into a single master report? Are there any good rules of thumb for when one is a better idea than the other?

Also, when I go to create the (sub)reports (e.g., in "Design View") Access gives me only sections for "Headers/footers" and "Details". If I put the total section in "Details", then the result repeats the totals several hundred times, i.e., once for each individual, which I do not want. How best to print these totals once for the entire report/subreport? Logically, I don't want to consider the entire report to fall into a "Report Header" section, but I suppose that's what I may need to do to get them only once. Does anyone have a better idea?

Thanks for any thoughts you might have for me.

Paul
Sep 10 '07 #1
Share this Question
Share on Google+
5 Replies


P: 35
Does it have to be a report? You could do something like
Expand|Select|Wrap|Line Numbers
  1. SELECT state, race, sum(vlaue to be totaled)
  2. FROM table
  3. GROUP BY state, race;
  4.  
Which would give you totals for each state and race but nothing else.
Sep 10 '07 #2

Expert 100+
P: 296
Hi folks,

I'm looking for a little advice here. I need to create a report that is totals only, with no detail records. I have a database with a lot of individuals (people) and the report has to have some overall totals, totals per state, totals by gender, race/ethnicity, and several other criteria. This report does not need to (and should not) report on each individual, just the totals.

I can create expressions for each of the items I need, but the question I'm looking for advice on is how to organize the overall report. I'm guessing that I should do it as multiple subreports included in a master report. Does that seem like the best procedure? Or should I just put them all into a single master report? Are there any good rules of thumb for when one is a better idea than the other?

Also, when I go to create the (sub)reports (e.g., in "Design View") Access gives me only sections for "Headers/footers" and "Details". If I put the total section in "Details", then the result repeats the totals several hundred times, i.e., once for each individual, which I do not want. How best to print these totals once for the entire report/subreport? Logically, I don't want to consider the entire report to fall into a "Report Header" section, but I suppose that's what I may need to do to get them only once. Does anyone have a better idea?

Thanks for any thoughts you might have for me.

Paul
In report design view, click on the sorting and grouping button on the Report Design toolbar at the top of your screen. Choose your headers/footers sections that you would like to see from the field list in the drop down box (i.e. State, Gender, Race, etc.) then where it says Group Footer, change it to 'Yes'. If you're wanting the header as well, change that to 'Yes' also. Put your totals expressions in the footers of your groupings. You do not need to have anything in the details section, so just shrink that section down all the way so you don't end up with unwanted white space on your report.
Sep 10 '07 #3

prn
Expert 100+
P: 254
prn
Does it have to be a report? You could do something like
Expand|Select|Wrap|Line Numbers
  1. SELECT state, race, sum(vlaue to be totaled)
  2. FROM table
  3. GROUP BY state, race;
  4.  
Which would give you totals for each state and race but nothing else.
Well, Yes, I think I do need a report, not just a dozen or so ad-hoc queries. I need something that I can (have my client) print giving a lot of different statistics. I don't think just a bunch of queries would be acceptable. I may be able to use this SQL anyway for some of the items, though, so thanks. I appreciate any hints.

Paul
Sep 10 '07 #4

prn
Expert 100+
P: 254
prn
In report design view, click on the sorting and grouping button on the Report Design toolbar at the top of your screen. Choose your headers/footers sections that you would like to see from the field list in the drop down box (i.e. State, Gender, Race, etc.) then where it says Group Footer, change it to 'Yes'. If you're wanting the header as well, change that to 'Yes' also. Put your totals expressions in the footers of your groupings. You do not need to have anything in the details section, so just shrink that section down all the way so you don't end up with unwanted white space on your report.
Thanks, I'll try this and see what I can do to organize it this way. I take it that you intend this to go essentially for each of the subreports?

Thanks,
Paul
Sep 10 '07 #5

Expert 100+
P: 296
Thanks, I'll try this and see what I can do to organize it this way. I take it that you intend this to go essentially for each of the subreports?

Thanks,
Paul
I tend to try to stay away from subreports, mainly cuz I'm fairly new to this and it just seems to get messy that way and I can usually accomplish what I'm trying to do with one report. If you are able to get all the info you are looking for in one query, try to use just one main report and set up the grouping that way. Depending on how the results output, you may need to turn to subreports to get the correct totals you are looking for.
Sep 10 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.